Creating a Sequential Record Number field

By Bill Graziano on 25 September 2000 | 13 Comments | Tags: UPDATE


Ok, this has to be one of the coolest things I've discoverd about SQL Server in quite a while. It is a way to create a sequential record number field on a table using a single update statement. Until I discovered this, I would have said this was impossible. Read on for the solution.

While digging around in SQL Server recently I discovered something very interesting. There is a quote from Books Online for the UPDATE statement that says:

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
This led me to believe I might be able to use a single update statement to increment a value in a table. Before this trick, if you wanted to create a sequential record number you had two bad choices. You could create a cursor and move through the records, setting the values one at a time to counter value that you incrememented. That was slow and not a set-oriented operation like SQL Server likes.

The second option was to create a temp table with an identity column and a column for the primary key of the table you wanted to number. Insert all the records into the temp table and the temp table identity column becomes the sequential record counter. You can join back to the original table and update the sequential record number. The only reason you'd still choose this option is if you want a custom sort order. This little trick won't give you that.

This little piece of code will run through a table and sequentially number the field you specify. The only drawback is that it will determine the order based on the physical order of the table.

declare @intCounter int
set @intCounter = 0
update Yaks
SET @intCounter = YakSequenceNumber = @intCounter + 1


After running this statement, the field YakSequenceNumber will hold nicely incremented values.

Discuss this article: 13 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using REPLACE in an UPDATE statement (31 March 2010)

Other Recent Forum Posts

which is small and which is large data (1 Reply)

Join 2 select Statements (2 Replies)

add new columns to create script of a database (1 Reply)

Change Row Value as Column Header (1 Reply)

What are First Steps for dba in new Office? (4 Replies)

table type (3 Replies)

Using COVERT function to convert DATE to DATETIME (3 Replies)

Import Excel workbook/form into SQL Server (5 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -