Sequence object is one of the new additions in SQL Server 2012. It has lot of functionality plus identity column functionality as well. However, there are certain limitations and one of the limitations is, you can only create sequence object in number datatype. Given below is the screen image.
So, how can we create a sequence object with varchar datatype ?
Lets do it step by step.
Step 1 :
In this step, we need to create a sample table and a sequence 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) , [Name] varchar(50) CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY CLUSTERED ([ID] ASC) ) 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 value for the [ID] column of the above table, and the default value of the [ID] column should be SEQUENCE and add a custom script to make it varchar.
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 FORMAT((NEXT VALUE FOR dbo.Sample_Seq),'CUS0000#') FOR [ID]; GO
Step 3 :
Lets insert few records into the table.
-- This script is compatibile with SQL Server 2012 and above. -- Insert records into the table USE tempdb GO INSERT INTO dbo.tbl_sample ([Name]) VALUES ('Imran'),('Bob'),('Sandra') GO
Step 4 :
Once the data has been inserted, you can browse the table and view the [ID] column data that it is either a number or varchar only.
--Browse Table USE tempdb GO SELECT * FROM tbl_sample GO --OUTPUT
Conclusion :
As you can see, the [ID] column has varchar data instead of number. Remember, whenever you need to do generate sequence in varchar format, you must format the sequence object.
Reblogged this on Sutoprise Avenue, A SutoCom Source.