SQL - How to create SQL sequence object with example

This blog explains how to generate unique value for a column by using SQL sequence object during insertion or updation of record

What is SQL SEQUENCE Object :

SEQUENCE object is a user-defined schema bound object that generates a sequence of numeric values on the below sequence object’s specification.

• START WITH

• INCREMENT BY

• MINVALUE

• MAXVALUE

Create a Sequence Object :

Below is a sql statement which 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 Sequence Object :

Run the below sql statement to run the SQL sequence object to get next sequence unique number  

SELECT NEXT VALUE FOR Project.ProjectNumber

Output:

1

Drop the Sequence Object :

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

Here is an example how to use Sequence object to generate the unique number for column assignmentNumber

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

START WITH 1

INCREMENT BY 1;

Here is a 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: