Decrease Font Size
Increase Font Size
   BLOG

How to change a IDENTITY column to NON-IDENTITY and vice versa

At one point we might come across situations where we have to disable the identity property from an existing column of a table for certain transaction. For instance, if we want to sync two tables from a different databases or server.

--drop identity
ALTER TABLE MyTable
DROP COLUMN myColumnName
GO
ALTER TABLE MyTable
ADD myColumnName int NULL
GO

--restore identity
ALTER TABLE MyTable
DROP COLUMN myColumnName
GO
ALTER TABLE MyTable
ADD myColumnName INT IDENTITY(1,1) NOT NULL