ROW_NUMBER vs. IDENTITY and ORDER BY

Question

Is there a difference (in terms of a result set, performance, or semantic value) between using ROW_NUMBER and using IDENTITY with the ORDER BY clause in MS SQL Server? For example, for a table with a column of "FirstName" there is some 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 identifier column. This means that subsequent inserts will increase.

For example, 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, they should be essentially the same in your case, since firstname needs to be sorted. If the rows were wider, I would not be surprised if the row_number() version left another in performance. With row_number() only one column is sorted and then mapped back to the original data. With identity() you need to sort the entire row. This performance difference is just a predicted assumption.