Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER 2012 – How to generate a varchar Sequence Number – Using Sequence Object’

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.

varcharsequence1.1

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

varcharsequence1.2

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.

Read Full Post »