Interview Question - Can we have 2 Identity columns in a single table ? If No then can we simulate such situation ?

I came across this question few days back and ever since wanted to blog about it. Although it was asked to me in one of internal project training session, I though it might be worth asking in an interview process as well.

So the question was - Can we have 2 Identity columns in the same table ?

Let us try ans see if we can..

DECLARE @TEST TABLE
(
ID SMALLINT IDENTITY(1,1),
NEW_ID SMALLINT IDENTITY(1,1)
)

Thus the answer is NO we can't have tw identity columns defined in a single table. 

Then came the followup question - Can we simulate the situation of having multiple identity columns by some other way ?

Let us try that as well ...

DECLARE @TEST2 TABLE
(
ID SMALLINT IDENTITY(1,1),
NEW_ID AS ID
)

INSERT INTO @TEST2 DEFAULT VALUES

SELECT * FROM @TEST2

What did we do here ? We have created the computed column  in the table which simulates the situation of having multiple identity columns per table. 










Now, what if you want to start NEW_ID column (second identity column) value from 101. Just change the code as follows and you would be through 

DECLARE @TEST2 TABLE
(
ID SMALLINT IDENTITY(1,1),
NEW_ID AS ID + 100
)

INSERT INTO @TEST2 DEFAULT VALUES

SELECT * FROM @TEST2

Noticed New_ID value in the output is starting from 101.