If you want to have a sequential number against a row in SQL Server record. You can use the number function called ROW_NUMBER. This function is usually used when you want to perform a page paging for a large number of records.

Let says we have a product table that we want to include the sequential number against the row.

Here is the TSQL Query to use the ROW_NUMBER in SQL Server.

SELECT	ROW_NUMBER() OVER(
			PARTITION BY CategoryId
			ORDER BY Name
		) AS Row_Index, 
		CategoryId, 
		Name, 
		Price
FROM  Products

This is the result if we run the above TSQL query.

If you want to divide the result based on the CategoryId, we can also use the PARTITION keyword. See below query example.

SELECT	ROW_NUMBER() OVER(
			PARTITION BY CategoryId
			ORDER BY Name
		) AS Row_Index, 
		CategoryId, 
		Name, 
		Price
FROM  Products

Here is the result after we apply the PARTITION keyword which will categorize the result based on the CategoryId column.