Setting Identity to on or off in SQL server

Question

I want to set Is Identity property of a column to off and after inserting an explicit value setting it to on again.I've written this query :

SET IDENTITY_INSERT Tbl_Cartoons OFF

Although it executes successfully ,nothing changes in the table design. Please suggest a solution ,It's really crucial.

Answer 1

Set identity_insert on to be able to explicitly set the value of the id column. Set it off again to auto-assign.

Answer 2

You actually want to use SET IDENTITY_INSERT Tbl_Cartoons ON before attempting to insert explicit values.

You're saying that "I am going to be responsible for inserting values into the IDENTITY column".

SET IDENTITY_INSERT Tbl_Cartoons OFF says "I'll let the system take responsibility for inserting values into the IDENTITY column".

Answer 3

The session that sets SET IDENTITY_INSERT is allowed to enter explicit values.

But the column is still an identity column. It's just that your session can ignore the identity constraint.

Answer 4

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.

Answer 5

To insert explicit values into the identity column , do the following :

SET IDENTITY_INSERT Tbl_Cartoons  ON
GO

-- code to insert explicit ID values

SET IDENTITY_INSERT Tbl_Cartoons  OFF
GO

Answer 6

You can turn off the Identity property, but it involves editing system tables which is not considered a good practice. You are also unlikely to have the necessary rights, and will probably see You do not have permission to run the RECONFIGURE statement attempting the following code:

DECLARE @tableName nvarchar(128) = 'YourTable';

-- Get a list of identity columns (informational)
SELECT OBJECT_NAME(object_id) tableName, sc.name colName, st.name dataType
FROM sys.columns sc
JOIN sys.types st
    ON st.system_type_id = sc.system_type_id
WHERE sc.is_identity = 1
AND OBJECT_NAME(object_id) = @tableName

-- Allow ad-hoc changes to system catalogs
EXEC  sp_configure 'allow update', 1
GO
reconfigure with override
GO
-- Eliminate the identityness
UPDATE syscolumns SET colstat = colstat - 1
WHERE id = object_id(@tableName)
AND name = 'Id' -- Specify column if you like, though only one identity per table is currently supported
GO
-- Unallow ad-hoc changes to system catalogs
exec sp_configure 'allow update', 0
GO
reconfigure with override
GO