SQL Server Portal

Sequence object is one of the new objects shipped with SQL Server 2012. I wrote multiple articles on its features / solutions in earlier articles. One of the major facts that Sequence object is not a table dependent like IDENTITY columns enables you to inset its value manually in any table. I have been asked in a forum whether we can make Sequence as a default value for a column in a table ? The answer is YES & NO.  YES because we have a work around to make it happen and NO because it is not implemented by default, like identity in a table.

So what is the work around to make SEQUENCE as a default for a column ?

Lets do it step by step.

Step 1 :
In this step, we need to create a sample table and a sequence object to demonstrate it.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE TABLE
USE tempdb
GO
CREATE TABLE dbo.tbl_sample
( [ID] VARCHAR(8) ,
[Product Name] varchar(50)
)
GO

--CREATE SEQUENCE
USE tempdb
GO
CREATE SEQUENCE dbo.Sample_Seq AS
INT START WITH 1
INCREMENT BY 1 ;
GO

Step 2 :
In this step, we need to create a default constraint on the above created table on [ID] column to make SEQUENCE as DEFAULT value for [ID] column.
Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT (NEXT VALUE FOR dbo.Sample_Seq) FOR [ID];
GO

Step 3 :
Lets insert few records into the table.

-- Insert records into the table
USE tempdb
GO
INSERT INTO dbo.tbl_sample ([Product Name])
VALUES ('SQL SERVER 2005'),('SQL SERVER 2008')
,('SQL SERVER 2012'),('SQL SERVER 2014')
GO

Step 4 :
Once the data has been inserted, you can browse the table and view the [ID] column data to check whether SEQUENCE became the DEFAULT value of this column or not.

--Browse Table
USE tempdb
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Read Full Post »