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.
SQL Server Portal 