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