SQL Server，如何在创建表后设置自动增量而不会丢失数据? [英] SQL Server, How to set auto increment after creating a table without data loss?

问题描述

我在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 value 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年开始)，解决方法发布在

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

这篇关于SQL Server，如何在创建表后设置自动增量而不会丢失数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！