Microsoft SQL server does
not support a method of identifying the row numbers for records stored on disk,
although there are a number of different techniques to associate a sequential
number with a row. You might want to display a set of records where each
record is listed with a generated number that identifies the records position
relative to the rest of the records in the set. The numbers might be
sequential that start at 1 and are incremented by 1 for each following record,
like 1,2,3,4, etc. In other cases you may want to sequentially number groupings
of records where each specific set of records is numbered starting at 1 and
incremented by 1 until the next set is reach where the sequence starts over. This
article will show a number of different methods of assigning a record sequence
number to records returned from a query.
Sequentially Numbering
Records by Having an Identity Column
Even though Microsoft SQL
Server does not physically have a row number stored with each record, you can
include one of your own. To have your own record number, all you need to do is
include an identity column in your table definition. When you define the
identity column, you can specify an initial seed value of 1, and a increment
value of 1. By doing this the identity column will sequentially number each
row inserted into the table. Let me show you a simple CREATE TABLE statement
that defines a ROW_NUMBER column, which will sequentially number records.
SET NOCOUNT ON
CREATE TABLE SEQ_NUMBER_EXAMPLE (
RECORD_NUMBER INT IDENTITY (1,1),
DESCRIPTION VARCHAR(40))
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES(‘FIRST RECORD’)
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES(‘SECOND RECORD’)
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES(‘THIRD RECORD’)
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES(‘FOURTH RECORD’)
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES(‘FIFTH RECORD’)
SELECT * FROM SEQ_NUMBER_EXAMPLE
DROP TABLE SEQ_NUMBER_EXAMPLE
When you run this code it
produces the following output:
RECORD_NUMBER DESCRIPTION
————- —————————————-
1 FIRST RECORD
2 SECOND RECORD
3 THIRD RECORD
4 FOURTH RECORD
5 FIFTH RECORD
Now as you can see, each
record has been automatically numbered using the identity column RECORD_NUMBER.
One thing to consider when using this method is that there is no guarantee that
these numbers are physically stored next to each other on disk, unless there is
a clustered index on the RECORD_NUMBER column. If you use this method either
create a clustered index, or have an ORDER BY RECORD_NUMBER clause to ensure
that the records are returned in sequential order. Also remember if you should
delete records, then your sequential number will have missing values for each
record deleted.
Sequentially Numbering Records by Using a
Temporary Table
Now you might not have
designed your table to have an identity column, or even want to place one on
your existing table, so another option is to insert the records you desired to
have a sequence number into a temporary table. Here is some code that takes
the Northwind.dbo.Employees table and copies only the Sales Representatives
into a temporary table. This example uses this temporary table with a rank
identity column to show a ranking of Sales Representatives by HireDate.
create table #HireDate (rank int identity,
HireDate datetime,
LastName nvarchar(20),
FirstName nvarchar(20)
)
insert into #HireDate (HireDate, LastName, FirstName)
select Hiredate, LastName, Firstname
from northwind.dbo.employees
where Title = ‘Sales Representative’
order by HireDate
Select cast(rank as char(4)) as Rank,
cast(hiredate as varchar(23)) as HireDate,
LastName,
FirstName from #HireDate
Drop table #HireDate
The output of this example
looks like this:
Rank HireDate LastName FirstName
—- ———————– ——————– —————
1 Apr 1 1992 12:00AM Leverling Janet
2 May 1 1992 12:00AM Davolio Nancy
3 May 3 1993 12:00AM Peacock Margaret
4 Oct 17 1993 12:00AM Suyama Michael
5 Jan 2 1994 12:00AM King Robert
6 Nov 15 1994 12:00AM Dodsworth Anne
Sequentially Numbering
Records by Altering Table
Ok, so you don’t want to
create a temporary table, but instead you want to use the existing table to
identify the row numbers for each record. You can still do this provided you
don’t have a problem with altering the table. To have row numbers, all you need
to do is alter the table to add an identity column with an initial seed value
of 1 and an increment of 1. This will number your rows from 1 to N where N is
the number of rows in the table. Let’s look at an example of this method using
the pub.dbo.titles table.
set nocount on
alter table pubs.dbo.titles
add rownum int identity(1,1)
go
select rownum, title from pubs.dbo.titles
where rownum < 6
order by rownum
go
alter table pubs.dbo.titles
drop column rownum
Note this example first
alters the table, then displays the first 5 rows, and lastly drops the identity
column. This way the row numbers are produced, displayed and finally removed,
so in effect the table is left as it was prior to running the script. The
output from the above script would look like this.
rownum title
———– —————————————————————-
1 But Is It User Friendly?
2 Computer Phobic AND Non-Phobic Individuals: Behavior Variations
3 Cooking with Computers: Surreptitious Balance Sheets
4 Emotional Security: A New Algorithm
5 Fifty Years in Buckingham Palace Kitchens
