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)
)

Interview Question - How to manually insert a value into Identity column ?

In the last blog entry we saw that how Identity column values cannot be updated. And at the end I had asked you a question what if we still want to do it .. can we ?

The answer is - No we can't. But we can simulate the behavior though. What does that mean? I mean we can do certain operation which would suggest as if we have updated the Identity value..

• Copy the record details for identity value that you want to update ( I am assuming here that you don't have duplicates in the identity column. In case you do then you know the record for which you want to preserve the details. )
• Delete the record for identity value that you want to update
• Insert new record into table with the identity value that you wanted to set for the deleted record

Interview Question - What is Identity column and Can we update it ?

This question was fielded in the recent job interview that my colleague was part of. He is moving on and I am glad that he is .. not that I hate him but his skills are underutilized here...

Back to the question  .. What is the Identity column in SQL server and Can we update the Identity column value ?

We all know what Identity column is ... in case you don't then 

According to Wikipedia - 

In short -  Identity column is an auto increment column. 

Now back to practical part of the question - Can we update the Identity column value ?