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 ?

Let us Try ..

CREATE TABLE #tbl_Identity(Id SMALLINT IDENTITY(1,1),Name VARCHAR(10)
)INSERT INTO #tbl_Identity (Name) SELECT 'India' UNION ALL SELECT 'UK' UNION ALL SELECT 'USA' UNION ALL SELECT 'Canada'SELECT * FROM #tbl_Identity 



As we can see Id column values got Auto filled in.

Now, let us try to update it..

UPDATE #tbl_Identity SET Id = 5
WHERE Id = 4 
   

Boom...........We got an Error

This tells us that Identity column value cannot be updated.  But what if you really wanna do it.. Is there any way ? If it is @ the top of your head then let me know or stay tuned for next blog entry to find out the answer