将标识添加到现有列

 

我需要将表的主键更改为标识列，并且表中已经有很多行。

我有一个脚本来清理ID，以确保它们从1开始连续，在我的测试数据库上正常工作。

什么是SQL命令来改变列有一个身份属性？

您无法更改现有的标识列。

你有2个选项，

  • 用标识创建一个新表并删除现有表

  • 用标识创建一个新列并删除现有列

    • 方法1（新表格）您可以在这里保留新创建的标识列上的现有数据值。 

    CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

    方法2（新列）您不能保留新创建的标识列上的现有数据值。标识列将保存编号的序列。 

    Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

    有关更多详细信息，请参阅以下Microsoft SQL Server论坛帖子：

    如何将列更改为标识（1,1）

    在SQL 2005及以上版本中，有一个技巧可以在不改变表的数据页的情况下解决这个问题。 这对于触摸每个数据页面的大型表格可能需要数分钟或数小时很重要。 即使身份列是主键，属于聚集索引或非聚集索引的一部分，或者其他陷入可能导致更简单的“添加/删除/重命名列”解决方案的技巧，该技巧也可以工作。

    这里有个技巧：可以使用SQL Server的ALTER TABLE ... SWITCH语句来更改表的模式而不更改数据，这意味着您可以用具有相同表模式的IDENTITY替换表，但不使用IDENTITY列。 同样的技巧可以将IDENTITY添加到现有列。

    通常情况下，ALTER TABLE ... SWITCH用于有效地用新的空分区替换分区表中的完整分区。 但是它也可以在非分区表中使用。

    我已经使用这个技巧在5秒内将IDENTITY中25亿行表中的一列转换为非IDENTITY（为了运行一个多小时的查询，其查询计划对非IDENTITY更好列），然后在不到5秒内再次恢复IDENTITY设置。

    这是一个如何工作的代码示例。 

     CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test;

    这显然比其他答案中的解决方案涉及更多，但如果你的桌子很大，这可能是一个真正的救命。 有一些注意事项：

  • 您需要先删除foriegn密钥，然后再进行切换并恢复。
  • WITH SCHEMABINDING功能，视图等相同
  • 新表的索引需要完全匹配（相同的列，相同的顺序等）
  • 旧表和新表需要位于同一个文件组上。
  • 只适用于SQL Server 2005或更高版本
  • 我以前认为这个技巧只适用于SQL Server的企业版或开发版（因为分区仅在企业版和开发版中受支持），但Mason G. Zhwiti在他的评论中表示它也可以在SQL标准版中使用。 我认为这意味着企业或开发人员的限制不适用于ALTER TABLE ... SWITCH。

    • TechNet上有一篇关于上述要求的文章。

    您无法将列更改为IDENTITY列。 你需要做的是创建一个新的列，该列从开始就定义为IDENTITY，然后删除旧列，并将新列重命名为旧名称。 

    ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

    渣子

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

    上一篇: Adding an identity to an existing column

    下一篇: Drop table, then cannot recreate table with the same name