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'
Sur cette page du site, vous pouvez voir la vidéo en ligne SQL Tutorial - Sequence Objects durée heure minute seconde en bonne qualité , qui a été Téléchargé par l'utilisateur BeardedDev 16 avril 2017, Partagez le lien avec vos amis et connaissances, sur youtube cette vidéo a déjà été regardée 4,748 fois et il a aimé 58 téléspectateurs. Bon visionnage!
