SQL Tutorial - Sequence Objects
This video will teach you how to create sequence objects with working examples, within SQL Server, we also look at the benefits of sequence objects and some of the differences between a sequence object and identity.
CREATE SEQUENCE 'name'
AS 'data type'
START WITH 'value'
INCREMENT BY 'value'
MINVALUE 'value' (optional – will default to data type range)
MAXVALUE 'value' (optional – will default to data type range)
CYCLE? (optional – will default to NO CYCLE)
How to find details of sequence objects within the database:
sys.sequences – will show all information regarding sequence objects including current value
How to restart the value of a sequence object:
ALTER SEQUENCE 'name' RESTART WITH 'value'
How to drop a sequence:
DROP SEQUENCE 'name' (cannot drop a sequence if it is in use!)
How to generate the next value for a sequence:
SELECT NEXT VALUE FOR 'name'
Sequences are covered in the Microsoft Exam – Querying SQL Server (70-461) 2012 / 2014.
SQL Statements used below:
CREATE SEQUENCE seq_test
AS TINYINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
CREATE TABLE Food_Orders
(
Id TINYINT DEFAULT NEXT VALUE FOR seq_test
, [Date] DATETIME2
, Summary VARCHAR(100)
)
CREATE TABLE Food_Orders_2
(
Id TINYINT DEFAULT NEXT VALUE FOR seq_test
, [Date] DATETIME2
, Summary VARCHAR(100)
)
-- loop to insert data to test sequence
DECLARE @N TINYINT = 1
WHILE @N 'less than' 110
BEGIN
INSERT INTO Food_Orders([Date], Summary)
VALUES (CURRENT_TIMESTAMP, 'Burger and Chips')
INSERT INTO Food_Orders_2([Date], Summary)
VALUES (CURRENT_TIMESTAMP, 'Burger and Chips')
SET @N = @N + 1
END
SELECT NEXT VALUE FOR seq_test
ALTER SEQUENCE seq_test RESTART WITH 1
DROP SEQUENCE seq_test
CREATE SEQUENCE seq_new
AS INT
START WITH 1
INCREMENT BY -1
SELECT NEXT VALUE FOR seq_new
SQL errors covered:
The sequence object 'name' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated
Cannot DROP SEQUENCE 'name' because it is being referenced by object 'name'
