SQL Server – SEQUENCE objects

SQL Server 2012 has introduced SEQUENCE objects, which are an alternative to the identity column property.

A sequence is an idependent object in SQL Server that is used to generate numeric values in ascending or descending order.

SEQUENCE Vs IDENTITY

Sequence Identity
User-defined database object Column property
Independent object Tied to a specific column in a table
We can generate the value before using it We need to insert the row to get the next identity value
We can cache a sequence to improve performance Doesn´t provide cache option
Provides an option to generate a range of sequence numbers using the sp_sequence_get_range stored procedure Not possible to generate a range of values

SEQUENCE PROPERTIES

  • START WITH: The start value. The default value is MINVALUE for an ascending sequence and MAXVALUE value for a descending sequence.
  • INCREMENT BY: The increment value. The default value is 1. If we use a negative value, the sequence will be descending.
  • MINVALUE: The minimum value of the sequence. The default is the minimum value of the data type of the sequence. For example, it will be 0 for a TINYINT data type and -2147483648 for a INT data type.
  • MAXVALUE: The maximum value of the sequence. The default is the maximum value of the data type of the sequence. For example, it will be 255 for a TINYINT data type and 2147483647 for a INT data type.
  • CYCLE | NO CYCLE: Defines whether the sequence should restart once it reaches to the maximum value (for ascending sequence objects) or minimum value (for descending sequence objects). The default is NO CYCLE.
  • CACHE | NO CACHE: Caches a sequence to improve performance reducing the number of disk IOs. The default is CACHE.

EXAMPLES

Creating a Sequence that starts with 1 and increases by 1

CREATE SEQUENCE dbo.SeqCustomerID AS INT
START WITH 1
INCREMENT BY 1;
GO

Using the dbo.SeqCustomerID sequence in an Insert

CREATE TABLE dbo.Customers
(
customerid INT NOT NULL PRIMARY KEY,
customername VARCHAR(50) NOT NULL
);
 
INSERT INTO dbo.Customers VALUES
(NEXT VALUE FOR dbo.SeqCustomerID,'Roger'),
(NEXT VALUE FOR dbo.SeqCustomerID,'Michael'),
(NEXT VALUE FOR dbo.SeqCustomerID,'Brian');
 
SELECT * FROM dbo.Customers;
SEQUENCE_01

dbo.Customers Result Set

Creating a Sequence that starts with 0 and decreases by 1

CREATE SEQUENCE dbo.SeqCustomerIDDesc AS INT
START WITH 0
INCREMENT BY -1;
GO

Using the dbo.SeqCustomerIDDesc sequence in an Insert

CREATE TABLE dbo.CustomersDesc
(
customerid INT NOT NULL PRIMARY KEY,
customername VARCHAR(50) NOT NULL
);
 
INSERT INTO dbo.CustomersDesc VALUES
(NEXT VALUE FOR dbo.SeqCustomerIDDesc,'Roger'),
(NEXT VALUE FOR dbo.SeqCustomerIDDesc,'Michael'),
(NEXT VALUE FOR dbo.SeqCustomerIDDesc,'Brian');
 
SELECT * FROM dbo.CustomersDesc;
dbo.CustomersDesc Result Set

dbo.CustomersDesc Result Set

Creating a Sequence using all properties

CREATE SEQUENCE dbo.SeqAllProperties
AS INT
START WITH 20
INCREMENT BY 5
MINVALUE 15
MAXVALUE 30
CYCLE
CACHE 10;

Execute the following commands and check the results

SELECT NEXT VALUE FOR dbo.SeqAllProperties AS 'First sequence number';
SELECT NEXT VALUE FOR dbo.SeqAllProperties AS 'Second sequence number';
SELECT NEXT VALUE FOR dbo.SeqAllProperties AS 'Third sequence number';
SELECT NEXT VALUE FOR dbo.SeqAllProperties AS 'Fourth sequence number';

SEQUENCE_03

  • The fisrt value is 20 (START WITH 20).
  • The second value is 25 (INCREMENT BY 5).
  • The third value is 30. The sequence reaches its maximum value (MAXVALUE 30).
  • The fourth value is 15. The sequence restarts with 15 because it is configured to cycle and the sequence minimum value is 15 (MINVALUE 15).

Dropping SEQUENCE objects

DROP SEQUENCE dbo.SeqCustomerID;
DROP SEQUENCE dbo.SeqCustomerIDDesc;
DROP SEQUENCE dbo.SeqAllProperties;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s