Switching Identity Off

[Gin_bee]

I want to know is it possible to swich off an Identlty property on a column through T-SQL? I want to set up a job to turn off this property whist making some other changes to the table.

I know I can alter the properties through the database diagram
or the table designer. There is an idex on this field but the numbers are remining unique.


Thanks in advance

[RSINGH]

Yup sure is. Use the following to allow explicit entry of a value in an identity column.

SET IDENTITY_INSERT tblTableName ON

To set it back:

SET IDENTITY_INSERT tblTableName OFF

[Gin_bee]

Thanks Rsingh

The problem I have is that the Identity property is being switched off permently and not just to allow explicit insertions. If I use the Set_Identity Insert and trying to insert explicit values into another table at the same time (which could happen) then SQL is going to return an error.

I need to completly drop the identity property not just turn if off.

Any Ideas?

[RSINGH]

OK - I haven't tried this, but have you considered the ALTER table command for changing the column definition? Its of the form

ALTER TABLE tblTable ALTER COLUMN colColumnName INTEGER(scale,precision)

[Gin_bee]

yes I have!!!!

but to date I've had no luck.

[RSINGH]

O arse!

BOL states:

'If the altered column is an identity column, new_data_type must be a data type that supports the identity property. '

What is your highest identity value? You might need to convert to a larger datatype. I'll try it on one of my db's and see if I can get something that works.

[RSINGH]

It doesn't look like it can be done. The quote above implies that although you can change the datatype of the column, it wil still remain an identity. It also stated that the changed column cannot be one that is 'Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

It might be possible to do it by working against the system tables directly if you're confident with them - but I'm not sure

I couldn't find any sp's or xp's either so you'll probably have to use Enterprise Manager.

[Gin_bee]

Thanks for the help anyway!