SQL Server，如何在不丢失数据的情况下创建表后设置自动增量?

2021-11-30 00:00:00 identity sql-server-2008 sql-server auto-increment

我在 SQL Server 2008 中有一个表 table1，其中有记录.

I have a table table1 in SQL server 2008 and it has records in it.

我希望主键 table1_Sno 列是一个自动递增的列.这可以在不进行任何数据传输或表克隆的情况下完成吗?

I want the primary key table1_Sno column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table?

我知道我可以使用 ALTER TABLE 添加自动增量列，但是我可以简单地将 AUTO_INCREMENT 选项添加到作为主键的现有列吗?

I know that I can use ALTER TABLE to add an auto-increment column, but can I simply add the AUTO_INCREMENT option to an existing column that is the primary key?

推荐答案

更改 IDENTITY 属性实际上只是元数据更改.但是直接更新元数据需要在单用户模式下启动实例并处理 sys.syscolpars 中的一些列，并且没有记录/不受支持，我不会推荐或将提供任何其他详细信息.

Changing the IDENTITY property is really a metadata only change. But to update the metadata directly requires starting the instance in single user mode and messing around with some columns in sys.syscolpars and is undocumented/unsupported and not something I would recommend or will give any additional details about.

对于在 SQL Server 2012+ 上遇到这个答案的人，目前实现自动递增列结果的最简单方法是创建一个 SEQUENCE 对象并设置 next 值for seq 作为列默认值.

For people coming across this answer on SQL Server 2012+ by far the easiest way of achieving this result of an auto incrementing column would be to create a SEQUENCE object and set the next value for seq as the column default.

或者，对于以前的版本(从 2005 年开始)，解决方法发布在 此连接项 显示了一种完全受支持的方法，无需使用 ALTER TABLE...SWITCH<进行数据操作的大小/代码>.还在 MSDN 上写了博客 此处.虽然实现这个的代码不是很简单，并且有一些限制——比如被改变的表不能成为外键约束的目标.

Alternatively, or for previous versions (from 2005 onwards), the workaround posted on this connect item shows a completely supported way of doing this without any need for size of data operations using ALTER TABLE...SWITCH. Also blogged about on MSDN here. Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can't be the target of a foreign key constraint.

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

相关文章