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.

SQL SERVER - Having Two Identity Columns for A Single Table twoidentitycolumns0-800x538

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.

SQL SERVER - Having Two Identity Columns for A Single Table twoidentitycolumns

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Killing DBCC SHRINKFILE Process – Is it Safe?
Next Post
SQL SERVER – Restore Database With Just Log File

Related Posts

3 Comments. Leave new

  • elaenabakman
    June 5, 2020 8:09 am

    You could also use a sequence:

    CREATE SEQUENCE [dbo].[sqs_test_1]
    AS [BIGINT]
    START WITH 9999
    INCREMENT BY-1
    MINVALUE 1
    MAXVALUE 9999
    CACHE;
    GO

    — use it like this and it’ll count down… just use it during insert:
    SELECT NEXT VALUE FOR [dbo].[sqs_test_1];

    Reply
  • 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.

    Reply
  • 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.

    Reply

Leave a Reply

Menu