Most often I’ve come across scenarios where we need to generate a unique valued column on the fly. From SQL 2005 onwards, we have the window functions like ROW_NUMBER() for doing this however for earlier versions one typical solution I’ve seen is use SELECT …INTO with IDENTITY() function to generate a temporary table with unique valued column.
eg. SELECT IDENTITY(int,1,1) AS ID,…. INTO #Temp FROM Table…ORDER BY datefield
We expect above statement to create a temporary table with ID column having consecutive unique valued based on order of datefield value.
But the fact is that this cant be guaranteed always!
Unless we create a temporary table with IDENTITY column and use like
INSERT #Temp (columns..)
SELECT columns..
FROM Table
..
ORDER BY datefield
we cant guarantee that identity value will be generated in order of specified field
This is clearly described in below KB article
