Jeff Moden (11/27/2008)
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.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.
Hi Jeff,
You're right. When using SELECT INTO a temp table with the IDENTITY function (*), then there will not be any gaps and the range of A.ID-4 up to A.ID will always have 5 rows. But unless I overlooked something, this was not in the article. To me, the article appears to imply that any IDENTITY row can be used for this. And since many tables already have an IDENTITY column, often with gaps in the sequence due to deleted data or rolled back inserts, I thought it'd be better to point out this danger.
(*) There is another potential problem here. I know that there is only one situation where Microsoft guarantees that identity values are given out in the expected order when using ORDER BY in an INSERT statement, but I can never recall if this guarantee is for SELECT INTO with the IDENTITY() function, of for INSERT ... SELECT on a table with a predefined IDENTITY column. And I can never find this particular bit of documentation when I need it. I think that SELECT INTO with the IDENTITY() function is the supported scenario, but if you are going to use this in production you'd probably better doublecheck first, for my memory is known to .... aaahh, what was I going to say again? 🙂