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..)
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