skip to Main Content

SEQUENCE vs. IDENTITY

SQL Server Denali contains a new database object called SEQUENCE that delivers sequential numbers. It complements the existing IDENTITY construct that has a similar objective.

IDENTITY has been around as long as I can remember and comes from SQL Servers Sybase heritage. It is simple to use because all that is needed is an IDENTITY clause on the column:

CREATE TABLE Table1 (
Column1 int NOT NULL IDENTITY(1,1),
Column2 varchar(50) NULL)

After this INSERTs into the table will be automatically assigned the next number in the sequence.

IDENTITY is safe and easy to use. So why introduce a new and similar construct now?

The sceptical amongst us may be thinking that the reason SQL Server is getting SEQUENCE objects is because Oracle has them and Microsoft don’t want to be seen as second best. Another reason could be compliance with the ANSI/ISO SQL standards. Another is that IDENTITY does have some disadvantages that SEQUENCE objects do not have. Some examples of these are:

  • A column that uses IDENTITY cannot be changed with an UPDATE statement (except when the SET IDENTITY_INSERT statement is used)
  • IDENTITY cannot be added to an existing column using ALTER TABLE
  • IDENTITY allocates numbers for use in a single table and numbers cannot be shared across tables
  • IDENTITY specifically relates to new records and INSERT and cannot be used in other circumstances

An example SEQUENCE object is a defined as follows:

CREATE SEQUENCE dbo.MySequence

AS int

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 100000

CYCLE

CACHE 3

It is used to obtain values with the following clause:

NEXT VALUE FOR dbo.MySequence

This can be used in all kinds of circumstances and some of these are as follows:

  • Assigning the next value to a variable:

DECLARE @i int
SELECT @i = NEXT VALUE FOR dbo.MySequence

  • Inserting a value into two tables from the same sequence:

INSERT INTO Table2 (Column1, Column2)
VALUES (NEXT VALUE FOR dbo.MySequence, ‘Data’)

INSERT INTO Table3 (Column1, Column2)
VALUES (NEXT VALUE FOR dbo.MySequence, ‘Data’)

  • Defining a sequence as a default for a column, so that new values are assigned automatically on each INSERT:

CREATE TABLE Table4 (
Column1 int NOT NULL DEFAULT NEXT VALUE FOR dbo.MySequence,
Column2 varchar(50) NULL)

  • Using a sequence in a SELECT clause to number the rows returned:

SELECT NEXT VALUE FOR dbo.MySequence OVER (ORDER BY Name), Name
FROM sys.objects

Pretty powerful stuff!

Certainly SEQUENCE offers more scope than the IDENTITY alone. But it’s worth remembering that IDENTITY is not going to go away any time soon and so for simplicity and backwards compatibility we can keep on using it. When we need a little more, then we have SEQUENCE as well.

The best of both worlds.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).

 

Previous Post
Next Post
Back To Top
Contact us for a chat