SQL Server Portal

In my earlier article, I wrote about how to generate a serial number by using the sequence object for a result set . However, sometimes you need to generate a negative serial number for any result set and you can easily achieve it by adjusting few properties in the sequence object.

Let me explain it step by step to demonstrate the solution.

Step 1 :
First of all, you need to create a sequence object along with some specific negative parameters as shown below.

USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
CREATE SEQUENCE dbo.Seq_Sample
AS int
START WITH -1
INCREMENT BY -1
MINVALUE -3000
MAXVALUE -1
CYCLE ;
GO
--OUTPUT

Step 2 :
In this step, you are ready to use the above sequence object to generate a negative serial number as shown below.

USE AdventureWorks2012
GO
SELECT
NEXT VALUE FOR Seq_Sample AS [Sno], Name
FROM sys.all_objects ;
GO
--OUTPUT

Step 3 :
In this step, you need to reset the sequence number in order to start the sequence number from -1 each time. Given below is the script.

USE AdventureWorks2012
GO
ALTER SEQUENCE dbo.Seq_Sample
RESTART WITH -1 ;
GO
--OUTPUT

Let me know if you came across this situation and how you fixed it.