How to create SQL sequence object with example

This article explains how to create SQL sequence object and how to generate unique value for column by using sequence object during insertion or updating of data.

SQL Server has SEQUENCE object and it is a user-defined schema bound object that generates a sequence of numeric values on below sequence object’s specification.

·        START WITH

·        INCREMENT BY

·        MINVALUE

     ·        MAXVALUE

Create a sequence object that generates sequence number increases by 1 and start with 1

    CREATE SEQUENCE Project.ProjectNumber 

    START WITH 1

    INCREMENT BY 1;

Run the below statement to get next sequence number.

SELECT NEXT VALUE FOR Project.ProjectNumber

Output:

1

You can drop the sequence object by using DROP Sequence statement

Drop SEQUENCE Project.ProjectNumber 

By default, sequence object is defined INT type and you can define in other datatype like tinyint, smallint, bigint, decimal and numeric

CREATE SEQUENCE Project.ProjectNumber  AS decimal(3,0) 

START WITH 1

INCREMENT BY 1;

Here is Table ProjectAssignement

Column	DataType
AssignmentNumber	int
ProjectID	int
UserID	int
AssignmentDate	DateTime

SQL Script:

Create Table Project.ProjectAssignement

     (

            AssignmentNumber int NOT NULL,

            ProjectID int NOT NULL,

            UserID int NOT NULL,

            AssignmentDate Datetime DEFAULT getdate()

            PRIMARY KEY (AssignmentNumber)

     )

For above Table, we can use Project.ProjectNumber Sequeunce object to generate unique number for AssignmentNumber Column

INSERT INTO Project.ProjectAssignement (AssignmentNumber,ProjectID,UserID)

VALUES (NEXT VALUE FOR Project.ProjectNumber, 1,1001);

INSERT INTO Project.ProjectAssignement (AssignmentNumber,ProjectID,UserID)

VALUES (NEXT VALUE FOR Project.ProjectNumber, 1,1002);

INSERT INTO blocking.ProjectAssignement (AssignmentNumber,ProjectID,UserID)

VALUES (NEXT VALUE FOR Project.ProjectNumber, 1,1003);

GO

Select * from Project.ProjectAssignement

Output:         

   

In other way, you can use sequence object to generate default value for AssignementNumber column

Create Table Project.ProjectAssignement

     (

            AssignmentNumber int NOT NULL DEFAULT NEXT VALUE FOR Project.ProjectNumber,

            ProjectID int NOT NULL,

            UserID int NOT NULL,

            AssignmentDate Datetime DEFAULT getdate()

            PRIMARY KEY (AssignmentNumber)

     )

Now run the below insert script

INSERT INTO Project.ProjectAssignement (ProjectID,UserID)

VALUES (6,1001);

INSERT INTO Project.ProjectAssignement (ProjectID,UserID)

VALUES (6,1002);

INSERT INTO Project.ProjectAssignement (ProjectID,UserID)

VALUES (6,1003);

GO

Select * from Project.ProjectAssignement

Output: