CODEWITHSUNDEEP
sql-server-2005sql-scripts
Mark Carpenter
Mark Carpenter

Reputation: 17775

Should I explicitly "SET IDENTITY_INSERT [Table] OFF" after turning "ON"?

Does SET IDENTITY_INSERT [Table] ON persist beyond the scope of a SQL Script? I'm wondering if I need to explicitly set it to "OFF" or if SQL Server knows that it should only use that setting for the current script.

Thanks!

Upvotes: 3

Views: 1439

Answers (1)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

Yes, it does persist beyond the current batch.

It doesn't, however, persist beyond the current session, so if you disconnect immediately after running it, you don't need to change it.

As an aside, it may only be ON for one table at a time, per session, and as pointed out by Aaron in a comment below, will throw an error if you try setting it for more than one table per session.

Upvotes: 10

Related Questions