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'

[@houstonvanhoy7767]

Nice. Thank you, Brad.

[@darrelltrainor1930]

I love your videos. The only suggestion I have is that you could increase the size of the text so that it is easier to see.

[@hassanOnYoutube]

Hit that Thumbs Up button ! :)) Love from Pakistan !

[@Iamyourboo2]

Good video, but consider that your point about inserts messing up the select of the latest ID column is addressed by using the "output" command in sql server to return the identity ID as part of the insert query. Then it is part of the same transaction and this risk is gone.

[@superfreiheit1]

You do not have to create a loop. Use "GO 100"

[@BeardedDevData]

I know.

[@fathallahilyes529]

thanks nice tuto but y should do some sports bro your breath like dragon !!

[@BeardedDevData]

Fair play 😂

[@donaldthelemaque4008]

Lol you have the same numbers of views as the number of your subscribers