Jacob Horbulyk Jacob Horbulyk - 1 year ago 209
SQL Question

ROW_NUMBER vs IDENTITY and ORDER BY

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download