row count: 20
-- Cross Join Query --
Table 'Accounts'. Scan count 17, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
-- Cross Join Query --
-- Update Query --
Table 'Accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 44 ms.
-- Update Join Query --
-- Select After Update Query --
Table 'Accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
-- Select After Update Query --


Row Count: 3280
-- Cross Join Query --
Table 'Accounts'. Scan count 3278, logical reads 6663, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 246 ms.
-- Cross Join Query --
-- Update Query --
Table 'Accounts'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 35 ms.
-- Update Join Query --
-- Select After Update Query --
Table 'Accounts'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 38, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 201 ms.
-- Select After Update Query --

Hugo Kornelis (11/27/2008)

---

What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.

Lynn Pettis (11/26/2008)

---

Good article, but let's take another approach and see what we see.

Jeff Moden (11/27/2008)

---

Heh... you beat me to it, again. ;)

Lynn Pettis (11/27/2008)

---

Since the purpose of the identity field was order, if there was a gap it wouldn't have caused an issue.

Jeff Moden (11/27/2008)

---

Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.