RowNumber on SQL Server

People who had contact with Oracle and already used the function that enumerates the rows of a query (RowNumber) perceive a certain ease to use this functionality.
However on SQL Server you are faced with a need to enumerate rows and the famous question comes to your head: How to do it?

This post aims to present two solutions for this situation:

  1. Using a temporary table with IDENTITY
  2. Using the function ROW_NUMBER () OVER (ORDER BY COLUMN_NAME)

An important observation to be made about the second approach is that it works from SQL Server 2005.

Initially we will create a temporary table:

Now let’s add a few records to test and query them:

The result so far is:

SQL RowNumber Result

At this point we have our scenario to work on so let’s apply the first method.
Note that a temporary table is created and then we run a query against this temporary table. Also note that the order of line numbering is defined the ORDER BY.

The result is:

SQL RowNumber Result Identity

Using the second method will not be necessary to create the temporary table and the row number is defined by is the ORDER BY.

The result will be the same as in the first method.

 

To know more about Identity visit https://msdn.microsoft.com/en-gb/library/ms186775.aspx
To know more about Row_Number visit https://msdn.microsoft.com/en-gb/library/ms186734.aspx

 

Thanks for reading.
Like and Share if you found it may be useful to someone you know!

Would you like to check the Portuguese version?
Click on the Brazilian flag -> Brazil

Leave a Reply

Your email address will not be published. Required fields are marked *

 
Translate »