Serial number or row number

Sometimes there would be a requirement to add serial number (or) row number to the result set.

Example: The requirement would be to select some columns from the database tables along with Serial Number like below

SNo	Customer_Name	Customer_Phone_Number
1	aaa	xxx-xxx-xxxx
2	bbb	xxx-xxx-xxxx

So, in this post we will see how we can select that using T-SQL on Microsoft SQL Server 2008

We can achieve this in several ways:

1) Using Row_Number()
 
Row_Number() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. 

T-SQL Script:

        SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS [S.No]
                        ,CUSTOMER_NAME
                        ,CUSTOMER_PHONE_NUMBER
        FROM database.dbo.tblTableName

2) Using Identity(Property)
 
Identity() property creates an identity column in the table. So, first we need to create a temporary table with [S.No] column on it and then we will select the required columns from the temporary table.

T-SQL Script:
 
        SELECT IDENTITY(INT,1,1) AS [S.No]
                       ,CUSTOMER_NAME
                       ,CUSTOMER_PHONE_NUMBER
        INTO #tempTable
        FROM database.dbo.tblTableName

        --Now, the result set 

        SELECT [S.No]
                       ,CUSTOMER_NAME
                       ,CUSTOMER_PHONE_NUMBER
        FROM #tempTable