Generat Serial Number from Select Query T-SQL

There are times when you would want to generate your own serial numbers for a query. For eg: If we take the Customers table in Northwind database, the CustomerID is not numeric Eg: 'ALFKI'.

If you want to generate your own serial number for the Customers table while displaying the records, use the ROW_NUMBER(). As mentioned in BOL "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"

Here is a query that will help you generate your own serial numbers for the CustomerID

SELECT (ROW_NUMBER() OVER ( ORDER BY CustomerID)) AS ID ,
CustomerID, ContactName, CompanyName, ContactTitle
FROM Customers

ROW_Number() is a magical function. It can generate serials but it requires the column based on which it generate the Order. Also it can repeat the sequence on every occurrence of the given condition.
ROW_Number() require Minimum 1 and maximum 2 data to do all the magic.

1. Column to decide the order. Multiple column can be used in csv format
2. Column to decide Partition on which Row_Number() will regenerate serial from 1.

Lets see this in action

We have tables State(stid,stname) and city(ctid, ctname)

;WITH CTE(STID,STNAME, CTID, CTNAME,STIID)AS(
SELECT *  FROM TBSTATES S JOIN TBCITIES C ON S.ID=C.STATEID
)
 
SELECT STNAME, (ROW_NUMBER() OVER( PARTITION BY STNAME ORDER BY CTNAME))CITI_SNO, CTNAME FROM CTE

This will give a column citi_sno with repetition when state will change.

Fill up the data in table and try yourself.