Home » News » RowNumber on SQL Server

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:

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

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

Deixe um comentário