Sequential Numbering and Counting of Records - Sequentially Numbering Records by Altering Table

(Page 3 of 6 )

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

Next: Sequentially Numbering Records by Using a Self Join >>
 

More MS SQL Server Articles
More By Gregory A. Larsen

Comments On: Sequential Numbering and Counting of Records

>>> Be the FIRST to comment on this article!