SQL Server Portal

Generating Serial number is something that is very usual for a SQL Server developer, either it is for report or any result set. I would not be wrong if I said that it is something that we need to use every now and then. I usually use ROW_NUMBER to generate the serial number in most of the places unless otherwise any compatibility issues. However, I wanted to generate it in new ways and recently, I found a new method to generate serial number while playing with Sequence Object.
Given below are scripts to generate a serial number using ROW_NUMBER & SEQUENCE Object.

• Using ROW_NUMBER

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
ROW_NUMBER() OVER(ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--OUTPUT

• Using SEQUENCE Object 

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
--Create Sequence Object
CREATE SEQUENCE dbo.Seq_Sample
AS int     START WITH 1
INCREMENT BY 1 ;
GO
--Generate serial number using Sequence object
SELECT
NEXT VALUE FOR dbo.Seq_Sample OVER (ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--Reset Sequence object to 1, So it will start from 1
--in the next result set.
ALTER SEQUENCE [dbo].Seq_Sample RESTART WITH 1
GO
--OUTPUT