October 20, 2009 at 7:55 am
Given the following sample code, will the identity column [rowseq] assign a number to the inserted rows in the sequence specified in the ORDER BY clause?
SELECT Count(1) as total, category, IDENTITY(int,1,1) as rowSeq
INTO #TEMP
FROM Metric m
INNER JOIN Manager mn ON mn.[Login] = m.createuserid
WHERE m.status = 'Pending' and type = 'updateTargetAccountPassword'
GROUP BY category
ORDER BY count(1) desc, category desc
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 8:27 am
Yep.
Very easy to try, indeed.
-- Gianluca Sartori
October 20, 2009 at 8:27 am
This is unreliable under SQL2000. See here.
The identity values that are generated depend on the position of the GetIdentity() function in the query tree (showplan), which may change due to optimizer changes, parallel query plans or the presence of TOP/SET ROWCOUNT.
... the identity value generation occurs before the rows are sorted based on the ORDER BY clause.
I find no authority that would make it workable under 2005-2008.
Alternatively, you can CREATE a table with an IDENTITY column and the order will be preserved. Quoting again from the above article:
If you want the IDENTITY values to be assigned in a sequential fashion that follows the ordering in the ORDER BY clause, create a table that contains a column with the IDENTITY property and then run an INSERT .. SELECT … ORDER BY query to populate this table.
The safe move when doing a SELECT... INTO is to use ROW_NUMBER() instead of IDENTITY to generate sequence numbers in a particular order. See here.
Thank you, Seth, for steering me clear of this before I tried it on a large table in a production environment.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 8:31 am
The article I does only cite 7.0 and 2000, but although many tests might show it works just fine, that by no means makes me confident that the issue was resolved in 2k5+ unless I saw somewhere that they said it was. As most of us are aware, order is one of those things that can work correctly 999 times and then fail the 1000th if all the rules aren't followed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply