SQL SEQUENCE

SQL SEQUENCE

This tutorial will help you to get familiar with SEQUENCE objects. The latest version of SQL server 2012, code named "Denali", introduces SEQUENCE objects, as a new feature. A SEQUENCE object is used as T-SQL built-in type which automatically generates or creates sequential numbers in certain conditions or scenarios and that sequence number can be used in multiple tables as Key of those tables. The condition or scenario can be the inserting/updating of multiple tables (reference tables) at a single time (like in stored procedure).

In this tutorial, you will learn about:

a) Creation of SEQUENCE object and retrieval of incremental value.
b) Dropping SEQUENCE object.
c) Usage i.e. How to use it with multiple tables.

a) Creation & Increment of SEQUENCE object

Lets start with Creation & Increment of SEQUENCE object:

CREATE SEQUENCE Sequence1;
-- Created a SEQUENCE object.
GO

SELECT * FROM sys.sequences WHERE name = 'Sequence1';
-- Checking whether Sequence1 is created.

In this section, a Sequence object is created with name "Sequence1" of default type Int and default starting value i.e. –2147483648 & auto-increment by 1. Sequence can be one of the below type:

tinyint - Range 0 to 255
smallint - Range -32,768 to 32,767
int - Range -2,147,483,648 to 2,147,483,647
bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
decimal and numeric with a scale of 0

Any user-defined data type (alias type) that is based on one of the allowed types.

Retrieval of incremented value can be done with the below query:

SELECT NEXT VALUE FOR Sequence1;

Example for creating a Sequence object with Start Value and Increment value:

CREATE SEQUENCE Sequence1 AS tinyint
-- Created a SEQUENCE object with start value as 0 and increment by 5
 START WITH 0
 INCREMENT BY 5;
GO
 
SELECT * FROM sys.sequences WHERE name = 'Sequence1';
-- Checking whether Sequence1 is created.
 
-- Get next value from Sequence1.
SELECT NEXT VALUE FOR Sequence1;
SELECT NEXT VALUE FOR Sequence1;

When we execute SELECT NEXT VALUE FOR Sequence1, it will return 0 for first query result and 5 as next query result (incremented by 5).

b) Dropping
Now lets see the dropping part:

IF EXISTS (SELECT * FROM sys.sequences WHERE name = 'Sequence1')
    DROP SEQUENCE Sequence1;

If the given sequence exists (i.e. Sequence1 in our case) then drop it.

c) Usage

Lets see the usage of Sequence objects:

SEQUENCE object scope is database wide. It means, it can be used by multiple tables in a database.

Example to use in multiple tables:

-- Create Customer table.
CREATE Customer TABLE
(
 CID INT NOT NULL PRIMARY KEY,
 CNAME VARCHAR(50) NOT NULL,
 CAGE INT NOT NULL
);
 
-- Insert values into the table.
INSERT Customer (CID, CNAME, CAGE)
VALUES (NEXT VALUE FOR Sequence1, 'Amit', 22),
(NEXT VALUE FOR Sequence1, 'Dheeraj', 28),
    (NEXT VALUE FOR Sequence1, 'Kabir', 25);
 
-- Update the table.
UPDATE Customer
SET CID = NEXT VALUE FOR Sequence1
WHERE Name = 'Amit';
 
-- Get next value from SEQUENCE Sequence1.
DECLARE @incr_val INT = NEXT VALUE FOR Sequence1;
-- Use Sequence1 in different table viz. Order.
UPDATE Order
SET OID = NEXT VALUE FOR Sequence1
FROM Customer AS c
WHERE c.CID = @incr_val;

More features of Sequence Objects:

I) Min & Max Value in Sequence object

CREATE SEQUENCE Sequence1 AS TINYINT
 START WITH 0
 INCREMENT BY 5
 MINVALUE 0
 MAXVALUE 255

We can set minimum and maximum value in a SEQUENCE Object.

II) Cycle or NoCycle

CREATE SEQUENCE Sequence1 AS TINYINT
 START WITH 0
 INCREMENT BY 5
 MINVALUE 0
 MAXVALUE 255
 CYCLE;

If developer turns this(Cycle) option ON by specifying CYCLE inside the query, and when the NEXT value exceeds by the maximum value (given in the query with MAXIMUM directive) or the default maximum value (depending on Type mentioned), then the NEXT value again starts from minimum value which is stated with MINVALUE directive.

If we specify NOCYCLE, then the cycle option will be turned OFF and it will throw an exception once the NEXT value exceeds the MAXIMUM value (stated in MAXIMUM Directive).