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