Fix Error 2744 “Multiple identity columns specified for table” in SQL Server

If you’re getting SQL Server error 2744 that reads “Multiple identity columns specified for table…“, it looks like you’re trying to define a table to have more than one identity column.

SQL Server restricts identity columns to just one per table. If you try to add another one, you’ll get the above error.

The easiest way to address this issue is to leave the table with one identity table and be done with it. But that might not always be practical. Maybe you need a column that increments a different value than the identity column. Fortunately, there are ways of doing that.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE MyTable (
    RealIdentity INT IDENTITY(1,1),
    FakeIdentity INT IDENTITY(100,10),
    RandomCol NVARCHAR(100)
);

Output:

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'MyTable'. Only one identity column per table is allowed.

I tried to create two identity columns, but SQL Server threw an error.

We’ll get the same error if we try to add an identity column to a table that already has an identity column:

CREATE TABLE MyTable (
    RealIdentity INT IDENTITY(1,1),
    RandomCol NVARCHAR(100)
);

ALTER TABLE MyTable
ADD FakeIdentity INT IDENTITY(100,10);

Output:

Msg 2744, Level 16, State 2, Line 6
Multiple identity columns specified for table 'MyTable'. Only one identity column per table is allowed.

Solutions

Here are a few ways of dealing with this issue.

Option 1: Create a Sequence

In many cases the best option will be to create a sequence for the second column (in place of the IDENTITY() property).

Here’s an example:

CREATE SEQUENCE MySequence START WITH 100 INCREMENT BY 10;

CREATE TABLE MyTable (
    RealIdentity INT IDENTITY(1,1),
    FakeIdentity INT DEFAULT NEXT VALUE FOR MySequence,
    RandomCol NVARCHAR(100)
);

That creates a sequence, then applies that sequence to the FakeIdentity column.

We can verify that it works by inserting data into the RandomCol column and checking the table’s data:

INSERT INTO MyTable (RandomCol) VALUES 
    ('Hog'),
    ('Wart'),
    ('Ant');
    
SELECT * FROM MyTable;

Result:

RealIdentity  FakeIdentity  RandomCol
------------  ------------  ---------
1             100           Hog      
2             110           Wart     
3             120           Ant      

The sequence provided us with the same incrementing functionality that we would’ve got from an identity column.

Option 2: Use UNIQUEIDENTIFIER with NEWID() or NEWSEQUENTIALID()

If a numeric sequence isn’t strictly necessary, you can use GUIDs (Globally Unique Identifiers) for other unique columns.

To do this, define the column as type UNIQUEIDENTIFIER and set its DEFAULT value to NEWID() or NEWSEQUENTIALID():

ALTER TABLE MyTable ADD OtherIdentity UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID();

It’s worth noting that NEWSEQUENTIALID() generates values in sequential order, which can improve database performance compared to the random values generated by NEWID().

Option 3: Use a Computed Column

If the second “identity” can be mathematically derived from the primary IDENTITY column (e.g., an offset value), you can use a computed column. For example:

ALTER TABLE MyTable ADD OtherIdentity AS RealIdentity + 100;

Option 4: Manage the Sequence via Application Code or Triggers

You can also create a standard INT or BIGINT column and manage its value generation through application logic or a database trigger. But be careful with this approach. It will require careful planning and implementation to ensure uniqueness and management of potential concurrency issues.

Option 5: Re-evaluate your Schema Design

Often, the need for multiple independent auto-incrementing columns suggests a potential issue with database normalization. Consider if the data belongs in separate, related tables, using foreign keys to link them back to a single primary IDENTITY column.