SQL Server，如何设置建表后自增而不丢失数据？

我有一张桌子table1在 SQL Server 2008 中，它有记录。

我想要主键table1_Sno列是自动递增列。可以在不进行任何数据传输或表克隆的情况下完成此操作吗？

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

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

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

或者，对于以前的版本（从 2005 年起），解决方法发布在这个连接项目 http://connect.microsoft.com/SQLServer/feedback/details/252226/allow-enabling-and-disabling-of-a-columns-identity-property显示了一种完全受支持的方法，无需使用数据操作的大小ALTER TABLE...SWITCH。还在 MSDN 上发表了博客here http://blogs.msdn.com/b/dfurman/archive/2010/04/20/adding-the-identity-property-to-a-column-of-an-existing-table.aspx。尽管实现此目的的代码不是很简单并且存在一些限制 - 例如正在更改的表不能成为外键约束的目标。

示例代码。

设置测试表，无identity column.

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')

Gives

bar         filler    filler2
----------- --------- ---------
10001       foo       bar      

Clean up

DROP TABLE dbo.tblFoo