How to drop the identity column in sql server 2012?

Question

How to Drop the existing table IDENTITY Field by using sql query. I have already tried the query like

SET IDENTITY_INSERT Database.tableName OF

Answer 1

SET IDENTITY_INSERT allows you to explicitly insert values into an identity column, instead of having it auto-assign (see http://msdn.microsoft.com/en-us/library/ms188059.aspx).

As far as I am aware, you cannot drop the identity feature of an identity column; the only solution I can think of is to drop the column entirely:

ALTER TABLE myTable
DROP COLUMN identityColumn

If for some reason you just want to "turn off" the identity feature for a while, and your table is not too large, you can just copy the identity column into a new column:

ALTER TABLE myTable
ADD identityCopy int

UPDATE myTable
SET identityCopy = identityColumn

...and then drop the identity column. You can, of course, re-add a new identity column and copy over the values with IDENTITY_INSERT if/when you want to add it back.