Making a normal Column, an Identity column in SQL Server CE

In SQL CE making a normal column an identity column is not possible. This is because the process involved in the making of identity columns. If you would like to know more about the Identity creation behaviour please read my earlier article.

http://arjunachith.blogspot.com/2007/08/making-normal-column-identity-column-in.html

In SQL CE the process mentioned in the article is not possible because that SQL CE does not support enabling and disabling IDENTITY INSERT.

If you did try it out, should have noted that even SQL CE table editor is not supporting this.

So if you really want to change a column in to an identity column (which already having unique values) then what you can do is, create a new table and copy the data your self. But the problem is that you will not get the old values in the identity column because there is no way to copy them without turning the IDENTITY INSERT off. So identity column data will be new from 1 to n, But all other information will be there in the table with the identity column in place. If you even wants to keep the old identity values you can keep that old identity column and create a new column for the Identity column.

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?