sql – What is ROW_NUMBER?

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 .

Scroll to Top