sql-servert-sqlidentity-insert

Is identity_insert reset to OFF at session close?

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.

Solution

  • 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