Just a note... The IDENTITY function can only be used when the INTO clause (as Figaro did) is also present or you will get the following error...

Server: Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

There is another technique. Gert Drapers did a straw poll of the audience at one of his PASS presentations this year to see who knew about this technique and very few did...hence its probably worth passing on here.

Stick the following into QA and run:

--I have a table:
USE tempdb
GO

CREATE TABLE MyTable (
   MyTableID int NULL,
   [Name] varchar(35) NOT NULL
)

--I populate some data
INSERT MyTable ([Name]) VALUES ('Jamie')
INSERT MyTable ([Name]) VALUES ('David')
INSERT MyTable ([Name]) VALUES ('Harold')
GO

--I then run the following to populate MyTableID
DECLARE @vKeyCounter int
SET @vKeyCounter = (SELECT ISNULL(MAX(MyTableID), 0) FROM MyTable)
UPDATE MyTable
SET @vKeyCounter = MyTableID= @vKeyCounter + 1
WHERE MyTableID IS NULL
OPTION (MAXDOP 1)

--View the results
SELECT *
FROM MyTable

The clever bit is the SET part of the UPDATE statement which also changes the variable @vKeyCounter.

Have a go with this and let me know what you think. I'd appreciate feedback.

I've just realised that this thread is over a year old. I hope someone reads it!!!

There was a link to it in the 2004-12-06 newsletter which is why I happen to be replying to it now!

Jamie --

   I like the approach because of its simplicity; however, if the records are numbered in the order that they appear in the table. This may be good approach if coupled with a temporary table built on some select statement (in a SP for example) so that the records are ordered before the sequential number is applied for some display purpose.

Art

DOH! I just noticed that your comment was two years old.