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.
Just Right click on highlighted part and click on “New Sequence…” which will open below popup.
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.) –
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
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' );
2). With Update Statement
UPDATE ProductMaster SET ProductID = NEXT VALUE FOR dbo.MyFirstSequence WHERE ProductName = 'HTC Desire 816g';
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
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.
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.