Why can a SQL Server table not have more than one IDENTITY column?

Question

Why can a SQL Server table not have more than one identity column?

CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY)

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

I understand we can resolve it using a computed column.

As per the product documentation, a table can not have more than one identity column. But, why? What is the real reason behind it?

Answer 1

I don't think there's any real "internals" reason. The metadata is stored at column level not table level. It would need a rethink though of scalar functions such as scope_identity() and pseudo column syntax such as $identity as there would now be ambiguities.

Philosophically if the purpose of identity is to produce something that uniquely identifies an entity why would you need two different arbitrary calculated values acting in that role?

And where is the benefit anyway? This is a cross site dupe so I'll repeat my example from SO.

An Identity Column in SQL Server has a seed and an auto increment. We could always calculate what the 2nd hypothetical id value should be if we knew the value of the first id column anyway.

e.g. If this was legal syntax

create table #foo
(
bar int identity(1,10),
baz int identity(1000,1)
)

We wouldn't need to store baz as it could be calculated from bar as follows.

baz = 1000 + (bar-1)/10

Since SQL Server 2012 you can knock yourself out and add as many columns using sequence defaults as you want to a table though. For example:

CREATE SEQUENCE dbo.Sequence1 
    AS integer 
    START WITH 1 
    INCREMENT BY 1 
    MAXVALUE 1000 
    CYCLE 
    CACHE 50;

CREATE SEQUENCE dbo.Sequence2 
    AS decimal(5,0) 
    START WITH 5
    INCREMENT BY 10
    MAXVALUE 250 
    CYCLE 
    CACHE 50;

CREATE TABLE dbo.T
(
    id integer NOT NULL DEFAULT NEXT VALUE FOR dbo.Sequence1, 
    id2 decimal(5,0) NOT NULL DEFAULT NEXT VALUE FOR dbo.Sequence2, 
);

INSERT dbo.T DEFAULT VALUES;
INSERT dbo.T DEFAULT VALUES;
INSERT dbo.T DEFAULT VALUES;

SELECT * FROM dbo.T;

DROP TABLE dbo.T;

DROP SEQUENCE 
    dbo.Sequence1, 
    dbo.Sequence2;

Answer 2

You could use a computed column instead, starting at some value plus the id value:

CREATE TABLE dbo.TwoIdentity (id INT IDENTITY (1,1) NOT NULL, SecondID AS 100+id, TEXT VARCHAR(50) NULL)