SQL Tutorial - Sequence Objects

Pubblicato il: 16 aprile 2017
sul canale di: BeardedDev

4,748

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'

In questa pagina del sito puoi guardare il video online SQL Tutorial - Sequence Objects della durata di ore minuti seconda in buona qualità , che l'utente ha caricato BeardedDev 16 aprile 2017, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 4,748 volte e gli è piaciuto 58 spettatori. Buona visione!

158