You can drop IDENTITY columns just like any other column. For example, to drop the IDENTITY column created in the previous section:
alter table stores drop record_id
However, the following restrictions apply to dropping an identity column:
If sp_dboption “identity in nonunique index” is turned on in the database.
You must first drop all indexes, then drop the IDENTITY column, and then re-create the indexes.
If the IDENTITY column is hidden, you must first identify it using the syb_identity keyword. See “Referring to IDENTITY columns with syb_identity”.
To drop an IDENTITY column from a table that has set identity_insert turned on, first issue sp_helpdb to determine if set identity_insert is turned on.
You must turn off the set identity_insert option by issuing:
set identity_insert table_name off
Drop the IDENTITY column, then add the new IDENTITY column, and turn on the set identity_insert option by entering:
set identity_insert table_name on
