SQL Server，如何创建一个没有数据丢失的表后自动增量？

我有一个SQL Server 2008中的表table1 ，它有记录。

我希望主键table1_Sno列是一个自动递增列。 这可以在没有任何数据传输或克隆表的情况下完成吗？

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

---

更改IDENTITY属性实际上是一个仅用于元数据的更改。 但要直接更新元数据，需要在单用户模式下启动实例，并在sys.syscolpars混淆某些列，并且没有文档/不支持，并且我不推荐或将提供任何其他详细信息。

对于在SQL Server 2012+中遇到此答案的人来说，实现自动递增列的最简单方法是创建一个SEQUENCE对象，并将next value for seq的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 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：如何在其中包含行的表上设置自动增量：

如果您正在复制的表格非常大，此策略会将这些行实际上复制两次，这会花费更长的时间。

您可以保存数据，使用自动增量和主键删除并重建表，然后重新加载数据。

我会用一个例子来引导你：

第1步，创建表foobar（没有主键或自动增量）：

CREATE TABLE foobar(
    id int NOT NULL,
    name nchar(100) NOT NULL,
)

第2步，插入一些行

insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');

第3步，将foobar数据复制到临时表中：

select * into temp_foobar from foobar

第4步，删除表格foobar：

drop table foobar;

第5步，用主键和自动递增属性重新创建您的表格：

CREATE TABLE foobar(
    id int primary key IDENTITY(1, 1) NOT NULL,
    name nchar(100) NOT NULL,
)

第6步，将您的数据从临时表中重新插入foobar

SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;

第7步，放下你的临时表，并检查它是否工作：

drop table temp_foobar;
select * from foobar;

你应该得到这个，当你检查foobar表时，id列是自动递增1，id是主键：

1    one
2    two
3    three

---

如果您想通过设计人员进行此操作，则可以按照此处的说明进行操作，如果将现有列更改为可以为空时，则不允许保存更改

链接地址: http://www.djcxy.com/p/95651.html

上一篇: SQL Server, How to set auto increment after creating a table without data loss?

下一篇: Query performance difference between SQL Server 2000 and SQL Server 2005