ROW_NUMBER() Function in MS SQL Server with Examples

Sometimes you may need to add row number or serial number with your desired outcomes in MS SQL Server. There is a function called ROW_NUMBER() in MS SQL Server by which you can do this. ROW_NUMBER() Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

Let’s see the function syntax:

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )  

In this syntax:

• The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.
• The PARTITION BY clause divides the result set into partitions (another term for groups of rows). The ROW_NUMBER() function is applied to each partition separately and reinitialized the row number for each partition.
• The PARTITION BY clause is optional. If you skip it, the ROW_NUMBER() function will treat the whole result set as a single partition.
• The ORDER BY clause defines the logical order of the rows within each partition of the result set. The ORDER BY clause is mandatory because the ROW_NUMBER() function is order sensitive.

Now, let’s see our sample table where we will apply ROW_NUMBER().

SELECT name, score 
FROM DataAnalytics.dbo.tbl_students 
WHERE name IS NO NULL;

Here is the result set of the above query:

SELECT ROW_NUMBER() serial, name, score

FROM DataAnalytics.dbo.tbl_students 
WHERE name IS NO NULL
ORDERBY 1;

In the above example, we have seen how to use ROW_NUMBER() Function in MS SQL Server.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to add space character by using ROW_NUMBER() function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like the facebook page http://www.facebook.com/LearningBigDataAnalytics and stay connected.