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
SELECT FirstName, IDENTITY(BIGINT) AS Position
ORDER BY FirstName
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.