ROW_NUMBER vs IDENTITY and ORDER BY

Question

Is there any difference (in terms of result set, performance or semantic meaning) between using ROW_NUMBER and using IDENTITY with an ORDER BY statement in MS SQL Server? For instance, given a table with a column "FirstName" is there any difference between

SELECT FirstName, ROW_NUMBER() OVER (ORDER BY FirstName) AS Position
INTO #MyTempTable
FROM MyTable

and

SELECT FirstName, IDENTITY(BIGINT) AS Position
INTO #MyTempTable
FROM MyTable
ORDER BY FirstName

Answer 1

The semantic meaning is different. The first example creates an integer column with a sequential value.

The second example, using identity() creates an identity column. That means that subsequent inserts will increment.

For instance, run this code:

select 'a' as x, identity(int, 1, 1) as id
into #t;

insert into #t(x) values('b');

select *
from #t;

As for processing, the two should be essentially the same in your case, because the firstname needs to be sorted. If the rows were wider, I wouldn't be surprised if the row_number() version edged out the other in performance. With row_number() only one column is sorted and then mapped back to the original data. With identity() the entire row needs to be sorted. This difference in performance is just informed speculation.