在 SQL Server 中将 Identity 设置为打开或关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15065253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting Identity to on or off in SQL server
提问by Vadim Tychonoff
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 :
我想将列的 Is Identity 属性设置为关闭，并在插入显式值后再次将其设置为打开。我写了这个查询：
SET IDENTITY_INSERT Tbl_Cartoons OFF
Although it executes successfully ,nothing changes in the table design. Please suggest a solution ,It's really crucial.
尽管它成功执行，但表设计没有任何变化。请提出一个解决方案，这真的很重要。
采纳答案by Jon Egerton
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.
从列中完全删除标识更难。问题涵盖了它，但基本思想是您必须创建一个新列，复制数据，然后删除标识列。
回答by Damien_The_Unbeliever
You actually want to use SET IDENTITY_INSERT Tbl_Cartoons ONbefore attempting to insert explicit values.
您实际上想SET IDENTITY_INSERT Tbl_Cartoons ON在尝试插入显式值之前使用。
You're saying that "Iam going to be responsible for inserting values into the IDENTITYcolumn".
您是说“我将负责将值插入IDENTITY列中”。
SET IDENTITY_INSERT Tbl_Cartoons OFFsays "I'll let the system take responsibility for inserting values into the IDENTITYcolumn".
SET IDENTITY_INSERT Tbl_Cartoons OFF说“我会让系统负责将值插入IDENTITY列中”。
回答by Vadim Tychonoff
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
回答by Andomar
The session that sets SET IDENTITY_INSERTis allowed to enter explicit values.
SET IDENTITY_INSERT允许设置的会话输入显式值。
But the column is still an identity column. It's just that your session can ignore the identity constraint.
但该列仍然是一个标识列。只是您的会话可以忽略身份约束。
回答by cf_en
Set identity_insert on to be able to explicitly set the value of the id column. Set it off again to auto-assign.
将 identity_insert 设置为能够显式设置 id 列的值。再次将其设置为自动分配。
回答by Charles Burns
You canturn 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 statementattempting the following code:
您可以关闭 Identity 属性，但它涉及编辑系统表，这被认为是一种不好的做法。您也不太可能拥有必要的权限，并且可能会看到You do not have permission to run the RECONFIGURE statement尝试以下代码：
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
