-1

SQL server question. Although my tests say "yes", I'd rather hear it from someone with experience.

When I use SET IDENTITY_INSERT some_table ON, I'd like to know that I can safely close my session without being anxious of forgetting to set it to off and thus creating trouble on some other user.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • 1
    There's nothing to reset. It a session-level setting, not a setting attached to the table you're enabling identity inserts for. The documentation is not clear on this, and the requirement that you have `ALTER TABLE` permission suggest something about the table is changed, but this is not the case. In the case of pooled connections (where the session does not die but is reset), the question should rather be if resetting the pooled connection will also reset `IDENTITY_INSERT`. I'm too lazy to test this because I'd just be too surprised if the answer was "no". – Jeroen Mostert Feb 08 '18 at 14:27

2 Answers2

0

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a6ba2bb-5e82-47e8-a1a0-16fc044b951e/detect-current-identityinsert-settings?forum=transactsql

From Microsoft:

Since SET IDENTITY_INSERT is a session sensitive, it is managed in buffer level without storing somewhere. This means we do not need to check the IDENTITY_INSERT status as we never use this key word in current session.

If there is anything unclear, please feel free to ask.

Thanks,

Jin Chen - MSFT

Jacob H
  • 2,455
  • 1
  • 12
  • 29
0

It is. You can test it like this:

Create test table set identity insert on and insert into it.

create table Test (
  TestIdentity int identity,
  SomeValue char(1)
);

set identity_insert Test ON
insert into Test (
  TestIdentity,
  SomeValue
) values (
  1,
  'x'
)

Open new Query and try inserting into table without setting identity insert on. It will fail.

insert into Test (
  TestIdentity,
  SomeValue
) values (
  2,
  'y'
)
Daniel Suchy
  • 1
  • 1
  • 2