SELECT .. INTO table with IDENTITY function

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

http://support.microsoft.com/kb/273586