Identity Column

Hi,

I need to turn off Identity of a column which already has records, i have tried SET IDENTITY_INSERT TableName OFF but it is not working. can any one please help me.

Do you mean turn it off permanently or what exactly? What are your exact, particular circumstances?

|||
To turn off identity temporarily and allow explicit inserts to the table, you have to issue SET IDENTITY_INSERT TableName ON. Note that you can have only one table with this property set to ON in a session. This will be in effect until you issue
SET IDENTITY_INSERT TableName OFF to come back to the identity.

If you wish to permanently delete the identity property then you may have to go for other solution, like adding a new column without identity to the table, insert the values for the existing data, delete the old column with identity property and rename the column back to the original name. Or you can create a new table with almost same structute except the idenity and export the data, drop the old table, rename the table name.

As a sidenote, why do you want to remove the identity property. As you can see, it can be done but its pain to do it.