Making a normal Column, an Identity column in SQL Server

Do you know that you cannot just change a normal column into an identity column in SQL Server? This is because that identity columns can only be created when creating new tables.

Now some people will say that how cannot that be? Because they are used to change using the SQL Server Management Studio (SQLSMS). What management studio will do is, it will create a temporary table with the Identity column and copy all your existing data there. Later it will delete the original table and will rename the temporary table to the original.

For example we will take Sales.Individual table in the AdventureWorks database.



Think that we need to add a column named Id as an identity column. What we will do is just type after ModifiedDate column and make the new column an identity column.

In such a case when we save the change SQLSMS will first create a temporary table which matches the new schema with the identity column.

Then it will set IDENTITY INSERT OFF and will copy the data from the original table to the newly created temporary table.

After this it will delete the original table and will rename the temporary table as the original table.

Did any one thought that the SQLSMS is doing this much of work for us without our knowledge?