Sequential Numbering and Counting of Records
(Page 1 of 6 )
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. In other cases, you may want to sequentially number groupings of records where each specific set of records are numbered starting at 1 and incremented by 1 until the next set is reached, 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 an 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.
Next: Sequentially Numbering Records by Using a Temporary Table >>
More MS SQL Server Articles
More By Gregory A. Larsen
/ 19 
