July 6, 2007 at 12:01 pm
Is thier a script available to disable the identity property of a column? Can I disable more than one identity column at once?
Thanks
July 6, 2007 at 12:31 pm
Fastest way is through EM. Because all constraints and indexes must be destroyed / rebuilt in that process.
July 6, 2007 at 12:33 pm
And be *very* aware that it can cause downtime!
* Noel
July 6, 2007 at 12:43 pm
can WILL cause down time.
July 6, 2007 at 12:46 pm
Just throwing this out...
It might even be faster to create a brand new clean DB (with the modifications), then reload all the data. Might be about as fast, but the 2nd version can more easily be scripted. I never actually did that, but i'd try it if I had a lot of tables to change the identity option from.
July 6, 2007 at 12:47 pm
A table can only have one column with an identity property. You can remove the identity property or turn it off for a batch with SET IDENTITY_INSERT OFF
July 6, 2007 at 12:50 pm
Thanks for your replies.
I will take it all under advisement.
July 6, 2007 at 12:56 pm
That's what happens when you read too fast. I thaught you wanted to DELETE the property, and not disable it for a while.
If you only need to temporarely disable, then just do as Steve told you.
July 6, 2007 at 1:01 pm
Can the identitity property be disabled on multiple tables at the same time? Use this command: SET IDENTITY_INSERT OFF
July 6, 2007 at 1:37 pm
SET INDENTITY_INSERT ON does *NOT* disables the identity property. It merely let the batch exceuting it specify a value for the identity column but that is different from "disabling"
The command to set that ON or OFF can be applied to only one table at a time!
* Noel
July 6, 2007 at 1:40 pm
No, only one table at a time can have INDENTITY_INSERT ON.
What exactly are you trying to do?
July 9, 2007 at 10:30 am
If by "disabling" one means "turn off the automatic assignment of sequentially-generated numbers upon insert into this table, and instead allow me to supply the value I wish to use for that column," then SET IDENTITY_INSERT ON does disable the identity property.
What do you mean by disable and how does it differ from this definition?
July 9, 2007 at 3:27 pm
"Disable" in my opinion is not to use Identity ever again at will. Because there are more than one table, according to the poster requirements, such "disable" is not possible.
When you disable a Foreign key for example the "definition" remains and SQL Server simply won't check for it. The same happens for Check constraints, triggers, etc. That is why it is not called DISABLE_IDENTITY and instead it is called "SET_IDENTITY_INSERT"
Just my $0.02
* Noel
July 9, 2007 at 3:53 pm
The definition of the behavior of an Identity property on a column is:
SQL Server assigns each new row inserted in a table a value that is some increment greater than the previous highest value.
SET_IDENTITY_INSERT negates the behavior described in this definition, which to me seems to fit the term "disabled." The same wording you used for triggers and constraints makes sense to me for identity:
When you disable SET IDENTITY_INSERT an Foreign key identity property for example the "definition" remains and SQL Server simply won't check for it automatically increment the identity column's value and prevent explicit value insertion.
The definition of the identity does remain intact when you set IDENTITY_INSERT ON, so it seems that SET IDENTITY_INSERT does temporarily disable an identity.
Are you objecting only to the terminology "disabled?" It sounds like you are taking exception to the the limitation of having only a single table with SET IDENTITY_INSERT ON at any given time, but I don't see that the ability to turn off a behavior, setting, or property on multiple objects at one time is integral to the definition of "disabling."
July 9, 2007 at 4:09 pm
We can argue about "wording" of these things for hours if you want.
The reason M$ used IDENTITY_INSERT ON instead of DISABLE_IDENTITY "hopefully" can justify *my* way of thinking about the "disable" concept you seem to be so concerned about.
Cheers,
* Noel
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply