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:

--CREATE TEMP TABLE   
CREATE TABLE #TAB_CLIENT(   
             Code INT,   
             Name VARCHAR(200))   
GO

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

--Insert data for test  
INSERT INTO #TAB_CLIENT (Code, Name) VALUES (1, 'Maria')   
INSERT INTO #TAB_CLIENT (Code, Name) VALUES (2, 'Alex')   
INSERT INTO #TAB_CLIENT (Code, Name) VALUES (3, 'Bruna')   
INSERT INTO #TAB_CLIENT (Code, Name) VALUES (4, 'Thiago')   
--Verifying the content   
SELECT *   
  FROM #TAB_CLIENT

The result so far is:

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.

--Method 1 (temporary table with IDENTITY)
SELECT IDENTITY(INT, 1, 1) AS RowNumber, Code, Name   
  INTO #TMP   
  FROM #TAB_CLIENT ORDER BY Name   
SELECT * FROM #TMP

The result is:

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

--Method 2   (function ROW_NUMBER () OVER (ORDER BY COLUMN_NAME))
SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber, *   
  FROM #TAB_CLIENT   
 ORDER BY Name

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 ->