SQLServer，如何在创建表后设置自动增量而不丢失数据？

Question

SQLServer，如何在创建表后设置自动增量而不丢失数据？我有张桌子table1在Sqlserver 2008中，其中有记录。我要主键table1_Sno列为自动递增列。在不进行任何数据传输或表克隆的情况下，能做到这一点吗？我知道我可以使用ALTERTABLE来添加一个自动增量列，但是我可以简单地将AUTO_CREATION选项添加到作为主键的现有列中吗？

Answer 1

更改IDENTITY属性实际上是元数据只进行更改。但是，要直接更新元数据，需要在单用户模式下启动实例，并在sys.syscolpars而且是没有文件记录的/没有支持的，不是我会推荐的东西，也不是我会给出的任何其他细节。

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

或者，对于以前的版本(从2005年起)，这个解决方案发布在此连接项显示了一种完全支持的方法来完成此操作，而不需要使用ALTER TABLE...SWITCH..还在MSDN上发表博客这里..尽管实现这一目标的代码并不简单，而且也存在一些限制-例如要更改的表不能成为外键约束的目标。

示例代码。

设置测试表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 TRYBEGIN 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