SQL SERVER – Having Two Identity Columns for A Single Table

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)