How to Check If Identity_Insert Is Set to on or Off in SQL Server

How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?

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.

Sorry, no help for this.

Great question though :)

Source: Here

Update
There are ways maybe to do this, also seen in the site I linked, IMO, it is too much effort to be useful.

if

(select max(id) from MyTable) < (select max(id) from inserted)

--Then you may be inserting a record normally

BEGIN
    set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE
END

ELSE --You definitely have IDENTITY_INSERT on.  Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway

BEGIN
.... Code that shouldn't run with IDENTITY_INSERT on
END

Is identity_insert reset to OFF at session close?

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

SET IDENTITY_INSERT ON List In Database SQL Server

I think you need this:

SELECT   'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  ON' 
FROM SYS.IDENTITY_COLUMNS 
WHERE OBJECT_NAME(OBJECT_ID) not like 'sqlagent_%'
AND OBJECT_NAME(OBJECT_ID) not like 'queue_messages%'

EDIT:

SELECT 'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  ON;'+'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  OFF;' 
FROM SYS.IDENTITY_COLUMNS 
WHERE OBJECT_NAME(OBJECT_ID) not like 'sqlagent_%'
AND OBJECT_NAME(OBJECT_ID) not like 'queue_messages%'

IDENTITY_INSERT is set to OFF - How to turn it ON?

Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

How to enable Identity_insert on SQL Server Level

You don't. The documentation confirms this:

SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table.

...

Remarks

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Emphasis mine.

If you need to enable it on multiple tables, you must enable it on one table, disable it, then en able it on the next, etc, etc.

Setting Identity to on or off in SQL server

All the line you've given does is to disable the identity so that you can insert specific values into your identity column - usually this is needed for one-offs such as moving data around. The identity is still there on the column, its just not being acted upon. Conceptually this is similar to the difference between disabling and removing triggers.

To remove the identity from the column entirely is harder. The question covers it, but the basic idea is that you have to create a new column, copy the data over, then remove the identity column.

When should I use SET IDENTITY_INSERT tablename OFF function?

From the documentation:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

So, if you are in a batch where you want to override the auto-generated identity values on two different tables, you would set the first one to OFF before setting the second one to ON.

(Also, like a lot of things, if you change something from the default, it's never a bad idea to change it back.)

---

Related Topics