Using Microsoft SQL 2012 Sequence – Auto Increment ID
One of the new feature available in the SQL 2012 is Sequence
.We can use this feature for various type of matters.We can use this to generate ID or serial numbers (Eg;Company
ID,Customer ID..etc). One of advantage is we can have auto increment column
which is varchar type.
We can find this new feature under programmability section.
I’m going to explain how to create company ID sequence.
1.
Creating Sequences
Execute the above query .In my sequence I’m starting with
10000 and increment by 1.
2.
Create Table associated with sequence.
CREATE TABLE dbo.Company
(
CompanyID
varchar(10) NOT NULL CONSTRAINT DEF_CompanyID DEFAULT
'CM' + CAST((NEXT VALUE FOR dbo.seq_Company) AS varchar),
CAddress varchar(30),
Trade varchar(30) ,
CONSTRAINT PK_Company
PRIMARY KEY CLUSTERED (CompanyID)
)
GO
Then you can insert some
values to table. Company ID will be auto increment column even though it’s type
varchar.
Once you pass through above two steps you will be able to
get auto increment company ID as below.
Optionally we can restart the sequence by using below query.
Alter SEQUENCE
dbo.seq_Company
RESTART WITH 10000
GO
We can get current value
of sequence using below query
SELECT Current_Value
FROM SYS.Sequences
WHERE name='seq_Company'
GO
