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:
- Using a temporary table with IDENTITY
- 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!