如何使用T-SQL修改表并在PK列上设置标识？
感谢您的帮助。
6个回答
3
大多数解决此问题的方法要么需要向表中添加新列，要么完全重建表。这两种方法都可能需要大量的锁定和日志记录活动，我一直觉得这很令人烦恼，因为这只是元数据更改，不应该必须接触数据页（实际上，可以通过在单用户模式下启动实例并在sys.syscolpars中 messing around一些列来直接更新元数据，但这是未记录/不支持的）。然而，在this connect item上发布的解决方法显示了一种完全支持的方法，使用ALTER TABLE...SWITCH将其转换为元数据更改（credit SQLKiwi）
示例代码。
设置没有identity列的测试表。
将其修改为具有
示例代码。
设置没有identity列的测试表。
CREATE TABLE dbo.tblFoo
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2
将其修改为具有identity列（几乎即时）。
BEGIN TRY;
BEGIN TRANSACTION;
/*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
set the correct seed in the table definition instead*/
DECLARE @TableScript nvarchar(max)
SELECT @TableScript =
'
CREATE TABLE dbo.Destination(
bar INT IDENTITY(' +
CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1) PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
'
FROM dbo.tblFoo
WITH (TABLOCKX,HOLDLOCK)
EXEC(@TableScript)
DROP TABLE dbo.tblFoo;
EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
测试结果。
INSERT INTO dbo.tblFoo (filler,filler2)
OUTPUT inserted.*
VALUES ('foo','bar')
给予
bar filler filler2
----------- --------- ---------
10001 foo bar
清理
DROP TABLE dbo.tblFoo
- Martin Smith
3
您无法修改现有列以具有IDENTITY“属性”-您必须执行以下操作：
- 创建一个具有相同结构（但设置了IDENTITY）的新表，
- 为此新表打开IDENTITY_INSERT，
- 将旧表中的行插入新表中，
- 删除旧表，并且，
- 将新表重命名为旧表名称。
- Damien_The_Unbeliever
9
@Martin - 很有趣，但你会推荐别人这样做吗？ - Damien_The_Unbeliever
@Damien - 绝对不，除非他们只是在测试数据库上出于好奇而这样做。 - Martin Smith
@Martin - 我是不是太蠢了 - 你链接的不就是上面项目符号的实际编码扩展吗？还是没有办法告诉 SQL Server “请在此列上启用
IDENTITY()”，对吧？ - Damien_The_Unbeliever@Martin - 啊，原来是
ALTER TABLE ... SWITCH是新的神奇部分。太好了。我会记住这个方法的。 - Damien_The_Unbeliever现在我已经摆脱了
DBCC CHECKIDENT，它几乎是瞬间完成的。 - Martin Smith显示剩余4条评论
1
这是你正在寻找的答案吗？
DBCC CHECKIDENT(
'DBName.dbo.TableName'
,RESEED --[, new_reseed_value ]
)
使用示例：
DBCC CHECKIDENT(
'DBName.dbo.TableName'
)
检查身份信息：当前身份值为'1'，当前列值为'1211031236'。 DBCC 执行完成。如果 DBCC 打印错误信息，请联系系统管理员。
DBCC CHECKIDENT(
'DBName.dbo.TableName'
,RESEED --[, new_reseed_value ]
)
检查身份信息：当前身份值为'1211031236'，当前列值为'1211031236'。
DBCC执行完成。如果DBCC打印错误消息，请联系系统管理员。- Tomasz Kapcia
0
由于在 insert 中只能忽略标识列，而不能忽略更新列，因此您需要中间表。以下是一个示例：
create table TestTable (pk int constraint PK_TestTable primary key,
name varchar(30))
create table TestTable2 (pk int constraint PK_TestTable identity primary key,
name varchar(30))
set identity_insert TestTable2 on
insert TestTable2 (pk, name) select pk, name from TestTable
set identity_insert TestTable2 off
drop table TestTable
exec sp_rename 'TestTable2', 'TestTable'
- Andomar
0
实际上，您可以修改列上的IDENTITY。
请阅读本文http://www.sqlmag.com/article/tsql3/adding-the-identity-property-to-an-existing-column.aspx。
但是，这将需要比
但是，这将需要比
ALTER TABLE tab ALTER COLUMN col SET IDENTITY更多的代码。- Steven Ryssaert
5
是的，你可以通过企业管理器或SSMS来完成这个操作 - 但它在“幕后”实际执行的操作正是我上面所描述的。 - Damien_The_Unbeliever
可能引用一篇10年前的文章，并提到比OP所询问的软件版本低2个版本也不是最好的做法... - JNK
我以为SQL Server的新版本是向后兼容的，也许我错了。 - Steven Ryssaert
此外，SQL语法比SQL Server 2000还要早得多。然而，它仍然被用于新的查询中。这是否意味着它因为老旧而不够准确？ - Steven Ryssaert
1我不确定为什么人们对你的建议如此愤怒。SSMS中的工具使得这些操作比大多数自制工具更加容易和安全。到目前为止，所有展示的方法都没有考虑重建PK、FK、触发器、相关视图等等。在我看来，你的回答实际上是迄今为止最好的答案。 - Jeff Moden
0
你需要使用 ALTER TABLE 命令 - 总是先在开发或预生产环境中进行测试！
示例 G 最接近您的要求：
CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ; GO ALTER TABLE dbo.doc_exe ADD
-- 添加一个 PRIMARY KEY identity 列。 column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,
- Peter Schofield
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接