You can drop IDENTITY columns just like any other column, for example:
alter table stores drop record_id
These are the restrictions for 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.
Next, turn off the set identity_insert option:
set identity_insert table_name off
Drop the IDENTITY column, then add the new IDENTITY column, and turn on the set identity_insert option:
set identity_insert table_name on
