Question:
What is ROW_NUMBER used in sql server ?
How and where should we use it?
Do you have a simple usage example?
Answer:
ROW_NUMBER is a non-deterministic function that returns a sequential – and potentially volatile – number calculated while (and whenever) the query is executed and serves to enumerate the results of that query's execution.
Whenever using the ROW_NUMBER function it is necessary to use the OVER() ORDER BY with an ORDER BY . This ORDER BY does not necessarily need to be the ordering of the data, it just determines how the numbers will be generated by the ROW_NUMBER function (and that's why the clause is mandatory).
In the OVER clause it is also possible to use a PARTITION BY clause, it will define when the "count" of ROW_NUMBER should restart.
For example, here's a normal query sorted by the name field.
SELECT
name, recovery_model_desc
FROM sys.databases
WHERE database_id < 5
ORDER BY name ASC;
The result would be something like:
name recovery_model_desc
----------------------------
master SIMPLE
model FULL
msdb SIMPLE
tempdb SIMPLE
And here the same query using ROW_NUMBER() . Note that the ordering has been moved into OVER() .
The result would be:
Row# name recovery_model_desc
1 master SIMPLE
2 model FULL
3 msdb SIMPLE
4 tempdb SIMPLE
An example using a different ordering for the data and generating the values of ROW_NUMBER .
SELECT
ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
name, recovery_model_desc
FROM sys.databases
WHERE database_id < 5
Order By Name Desc;
Exit:
Row# name recovery_model_desc
4 tempdb SIMPLE
3 msdb SIMPLE
2 model FULL
1 master SIMPLE
And an example using PARTITION BY recovery_model_desc .
SELECT
ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC)
AS Row#,
name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;
The output would be like this:
Row# name recovery_model_desc
1 model FULL
1 master SIMPLE
2 msdb SIMPLE
3 tempdb SIMPLE
The examples were copied from the documentation .
See more in the official SQL Server documentation .