ROW_NUMBER used in
sql server ?
How and where should we use it?
Do you have a simple usage example?
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
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).
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
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
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
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;
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 .