The last section of my Comprehensive Database Performance Health Check is always questions and answers. In yesterday’s session, a DBA asked an interesting question that if it is possible to have two identity columns for a single table. The answer to this question – Yes and No.
Technically a table can have only one identity column and if you try to add another identity column to it, it will give you an error.
Here is an example
USE TempDB GO CREATE TABLE dbo.TwoIdentity (ID INT IDENTITY (1,1) NOT NULL, ID2 INT IDENTITY (1,1) NOT NULL)
When you run the above syntax, it will give you the following error:
Msg 2744, Level 16, State 2, Line 3
Multiple identity columns specified for table ‘TwoIdentity’. Only one identity column per table is allowed.
However, there is a workaround for the same. You can run using the computed column to create another identity.
Let us see a unique question which I had received from my client:
“We want two identity columns in a single table. One identity column starting from 1 and increasing by an interval of 1 and another one starting from 9999 and decreasing by 1, can you help us to do that?”
The answer is yes, it is possible to do that. Let us see the code for the same.
First, let us create a table with a computed column which actually uses the original identity column and decreases it by 1.
CREATE TABLE dbo.TwoIdentity (ID INT IDENTITY (1,1) NOT NULL, SecondID AS 10000-ID, TextDesc VARCHAR(100)) GO
Next, let us populate the table with some sample data.
INSERT INTO TwoIdentity (TextDesc) VALUES ('SQLAuthority.com') GO 100
Now let us select the data from the table.
SELECT * FROM dbo.TwoIdentity GO
Here is the screenshot of the data.
In the screenshot, you can clearly see how the SecondID column acts as a second identity column which is decreasing from an initial number of 9999.
Here is how you can drop the table which you had created earlier.
DROP TABLE dbo.TwoIdentity GO
I hope you find this blog post helpful. Here are a few additional blog posts on this subject, which you may find interesting.
- SQL SERVER – Finding Out Identity Column Without Using Column Name
- SQL SERVER – How to an Add Identity Column to Table in SQL Server
- SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
- SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
- SQL SERVER – Find Current Identity of Table
- SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity
- SQL SERVER – Reset the Identity SEED After ROLLBACK or ERROR
- SQL SERVER – DELETE, TRUNCATE and RESEED Identity
- SQL SERVER – 2005 – List All Column With Identity Key In Specific Database
Reference: Pinal Dave (https://blog.sqlauthority.com)
You could also use a sequence:
CREATE SEQUENCE [dbo].[sqs_test_1]
START WITH 9999
— use it like this and it’ll count down… just use it during insert:
SELECT NEXT VALUE FOR [dbo].[sqs_test_1];
One thing to note about IDENTITY is that it can have gaps. This can be caused by DELETEs, but it can also be caused by how SQL handles IDENTITY in the back end. It can cache some of the numbers up and in some situations (dirty shutdown for example), you can end up having gaps. If you NEED it to be sequential, using a sequence is a better option.
Knowing WHY you need 2 identity columns may help you design the table better. For example, if it is for serial numbers for a system, a sequence may be a better option as gaps may be problematic. If it is simply because you want an ever increasing key for the table, gaps don’t (usually) matter.
I love this article. It is very nice to explain and keep it up to good work. Thanks to that post it has plenty of information to consider. Thoughtful, well-organized article and it is explained in step by step.
It is a very nice trick, Thank You