Auto increment a non-identity column in SQL Server

I’m afraid to say that I’m just one of those people that unless I am doing something, I am getting bored. Even in my office in the free time I always used to active on stackoverflow.com site. In-fact this blog post inspired by one of the question asked in stackoverflow and after answering the question I thought it’ll be a good idea to make a blog post for my readers. So I have decided to dedicate myself to writing this blog post.

So the question was – Auto Increment a non-identity Column in sql-server. Well, if you are good at sql then it’ll strike you many ways like – create a trigger for insert which executes at insertion time. Yes that’s one of the correct answers. Another alternative is – get max value from the table and increment it by 1 at the time of insertion. That’s also correct (But awkward). But apart from all of these there is a very nice feature introduced in SQL server 2012 called – SEQUENCE. It gives a sequence of numeric values either in ascending or descending order according to the criteria specified when the sequence was created.

By not going into extensive detail of sequence, Let’s create our first sequence.

CREATE SEQUENCE dbo.MyFirstSequence
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
NO CYCLE
CACHE 50

And yes you can also create sequence by using SSMS as shown in below image.

SSMS1

Just Right click on highlighted part and click on “New Sequence…” which will open below popup.

CreateSeq

Now let us try to understand each line of creating sequence. Here we create a sequence of datatype int (It can be of TINYINT, SMALLINT, BIGINT and user-defined data type derived from the specified datatype here.) –

Start from –  1,

Increment is  – 1 (it can also be nagative),

Minvalue – Minimum value for a sequence object is the min value of the data type i.e. -2,147,483,648 (For int),

MaxValue – If no value is specified then max value for a sequence object is the max value of the data type i.e. 2,147,483,647 (For int),

NO CYCLE – Throws an exception after max value,

CACHE – I’m not explaining this in detail(Refer MSDN). If you specified cache then it can help you to increase performance by minimize some IO at generation time.

NOTE: The cache values can be lost in case of any power failure or server crash.

Alright, now let us generate sequence number by using the “next value for” syntax. Below query gives you your first sequence number.

SELECT SeqNum = NEXT VALUE FOR dbo.MyFirstSequence

Practical Usage:

Okay, now let’s see the practical use of sequence.

1). With Insert Statement

You can use sequence in insert statement as shown below.

INSERT INTO ProductMaster ( ProductID, ProductName )
VALUES ( NEXT VALUE FOR dbo.MyFirstSequence, 'iPhone 6s' ),
( NEXT VALUE FOR dbo.MyFirstSequence, 'HTC Desire 816g' );

SeqInsert

2). With Update Statement

UPDATE ProductMaster SET ProductID = NEXT VALUE FOR dbo.MyFirstSequence
WHERE ProductName = 'HTC Desire 816g';

SeqUpdate

3). With Default Column value

You can say this is new way to generate unique numbers in the table, like below –

-- Add sequence constraint
ALTER TABLE [dbo].[ProductMaster]
ADD CONSTRAINT [DF_ProductMaster_ProdID] DEFAULT NEXT VALUE FOR dbo.MyFirstSequence FOR ProductID
GO

INSERT INTO ProductMaster (ProductName)
VALUES ('iPhone 5s')
GO

SeqConstraint

So now I’m sure you are going to tell me that IDENTITY does the same thing then what is the difference between the two. Yes, nice question. Here is the difference.

Difference Between a Sequence and Identity

  • The major difference is that an Identity is associated with the table, on other hand sequence is created independently of the tables.
  • Identity generated values when rows are inserted. While on the other hand sequence can obtain the next sequence number by calling “NEXT VALUE FOR” function i.e. Sequence generate numbers across database.

There might be some more difference, If you came across it then you can add it in comment section. Thank you.

Now what happen if you reach at the max value and you request for next sequence number. As you know that we specify NO CYCLE in sequence creation. That means you will get the error as shown in below.

SeqMaxValue

So What you need to do in this situation? Well, You need to alter sequence and restart it by using below syntax.

ALTER SEQUENCE dbo.MyFirstSequence
RESTART WITH 1
INCREMENT BY 1
--MAXVALUE 5 

That’s it for now. Hope you enjoyed this post. I would love to hear from you.

 

 

 

Advertisements

One thought on “Auto increment a non-identity column in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s