在列上设置标识 [英] set identity on the column

问题描述

如何使用 T-SQL 修改表并在 PK 列上设置标识?

How can I modify table and set identity on PK column using T-SQL?

感谢帮助

推荐答案

这个问题的大多数解决方案的问题在于，它们需要向表中添加新列或完全重建表.

The problem with most solutions to this question is that they require either adding a new column to the table or completely rebuilding the table.

两者都可能需要大量的锁定和记录活动，我一直觉得这很烦人，因为这只是元数据更改，根本不需要触及数据页面(确实可以通过启动直接更新元数据单用户模式下的实例，并在 sys.syscolpars 中处理一些列，但这是未记录/不支持的.)

Both can require large amounts of locking and logging activity which I have always found annoying as this is a metadata only change and shouldn't necessitate touching the data pages at all (Indeed it is possible to update the metadata directly by starting the instance in single user mode and messing around with some columns in sys.syscolpars but this is undocumented/unsupported.)

但是，在 此连接项 展示了一种完全受支持的方式，可以使用 ALTER TABLE...SWITCH(信用 SQLKiwi)

However the workaround posted on this connect item shows a completely supported way of making this into a metadata only change using ALTER TABLE...SWITCH (credit SQLKiwi)

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 列(或多或少是即时的).

Alter it to have an identity column (more or less instant).

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

这篇关于在列上设置标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！