Here is part 3 of my sequence of blogs on SEQUENCE object 🙂
In part 1, I blogged on how SEQUENCE objects in DENALI behave within transactions. You can see that here;
In part 2, I blogged on some basics on how to get started with SEQUENCE object and how to use it in table definitions: you can find that blog here;
In this blog, I want to show you what else you can do with a SEQUENCE object.
Creating a SEQUENCE Object:
USE tempdb GO CREATE SEQUENCE dbo.TestID AS INT MINVALUE 1 NO MAXVALUE START WITH 1;
Generating NEXT value for a SEQUENCE object:
SELECT NEXT VALUE FOR dbo.TestID
Re-seeding a SEQUENCE object; note that SEQUENCE is a first class SQL Server object:
ALTER SEQUENCE dbo.TestID RESTART WITH 20;
Altering a SEQUENCE object to change the increment value by using the INCREMENT clause:
ALTER SEQUENCE dbo.TestID RESTART WITH 1 INCREMENT BY 10;
Dropping a SEQUENCE object:
DROP SEQUENCE dbo.TestID
Suppose the minimum or maximum value of a SEQUENCE object is reached and you want to re-start it automatically (minimum in case the SEQUENCE object is descending in nature), you can use the CYCLE clause. For example;
CREATE SEQUENCE dbo.KolDevCon2 AS tinyint START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CYCLE ; GO
2 Comments on “SQL Server 2012: DENALI series: SEQUENCE feature Part 3 – Re-seeding, Restarting, incrementing & Cycling”
Is there a way to function to capture sequence value generated for inset record like Ident_current?
sorry for the late reply; try this..
select * from sys.sequences