3

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!

Shimmy Weitzhandler
  • 101,809
  • 122
  • 424
  • 632
Mark Carpenter
  • 17,445
  • 22
  • 96
  • 149

1 Answers1

10

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.

Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
  • Jeremy: what do you mean by "current batch" compared to current session? – shahkalpesh Jun 29 '09 at 16:45
  • upvoted for session vs. batch diff and how it transitions to the next table. – Raj More Jun 29 '09 at 16:46
  • 1
    @Jeremy - your last statement is incorrect. If you try setting IDENTITY_INSERT ON for a second table, you will receive an error: Msg 8107, Level 16, State 1, Line 5 IDENTITY_INSERT is already ON for table 'mydb.dbo.MyTable'. Cannot perform SET operation for table 'MyOtherTable'. – Aaron Alton Jun 29 '09 at 18:02
  • Thanks for your answer(s) and comments! I ended up not having to use this for the script I was writing (the database that we inherited from another company didn't have any identity columns set...). It's good to know for the future though! – Mark Carpenter Jun 29 '09 at 18:48