Setting an identifier to enable or disable on the SQL server

Question

I want to set the Is Identity property of a column and after inserting an explicit value so that it turns on again. I wrote this query:

SET IDENTITY_INSERT Tbl_Cartoons OFF 

Although it succeeds, nothing changes in the table design. Please suggest a solution, this is really important.

Answer 1

The whole line that you specified is to disable the identifier so that you can insert certain values ​​into your identity information column - this is usually necessary for one-time operations such as moving data. Identity is still present in the column; it simply does not work. Conceptually, this is similar to the difference between disabling and deleting triggers.

Removing an identifier from a column is more difficult. The question covers this , but the basic idea is that you need to create a new column, copy the data and delete the identifier column.

Answer 2

In fact, you want to use SET IDENTITY_INSERT Tbl_Cartoons ON before trying to insert explicit values.

You say that " I will be responsible for inserting values ​​into the IDENTITY column.

SET IDENTITY_INSERT Tbl_Cartoons OFF says: "I will let the system take responsibility for inserting the values ​​into the IDENTITY column."

Answer 3

To insert explicit values ​​in the identifier column, follow these steps:

 SET IDENTITY_INSERT Tbl_Cartoons ON GO -- code to insert explicit ID values SET IDENTITY_INSERT Tbl_Cartoons OFF GO 

Answer 4

In the session that sets SET IDENTITY_INSERT , explicit values ​​are allowed.

But the column is still the identity column. It is just that your session can ignore the restriction of personality.

Answer 5

Set identity_insert in order to explicitly set the value of the id column. Reinstall it for automatic assignment.

Answer 6

You can disable the Identity property, but it involves editing system tables, which are not considered good practice. You are also unlikely to have the necessary rights and are likely to see You do not have permission to run the RECONFIGURE statement by running 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