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'
Auf dieser Seite können Sie das Online-Video SQL Tutorial - Sequence Objects mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer BeardedDev 16 April 2017 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 4,748 Mal angesehen und es wurde von 58 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
