Mehedi's Earth

A quick question, are you ever in a position to have more than one IDENTITY columns in a single table? Technically, SQL Server does not support multiple IDENTITY Columns in a single table. Question is, can we still achieve this? Answer is "yes" in a tricky way. First, try to create the table with two IDENTITY column:

CREATE TABLE Test1(
    pk_id    int not null identity(1,1),
    second_pk_id    int not null identity(1,1),
    name    varchar(10) default ('Mehedi')
)
GO

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

We will ride on Computed Column for the work around. We know that a computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs. In computed column, we are going to assign the identity value + 100000. Let's try:

-- Drop table if it already exists.

IF OBJECT_ID('Test1', 'U') IS NOT NULL
    DROP TABLE Test1;
GO

-- Create the table
CREATE TABLE Test1(
    pk_id    int not null identity(1,1),
    second_pk_id    as pk_id+100000, // assign the identity value + 100000
    name    varchar(10) default ('Mehedi')
)
GO

-- Populate with 100 sample data
INSERT INTO Test1 default values
GO 100

-- Examine the data
SELECT * FROM Test1
GO



Output