从表中的列中删除标识

Question

从表中的列中删除标识

我们有一个5GB的表（接近5亿行），我们希望删除其中一个列上的标识属性，但是当我们尝试通过SSMS执行此操作时 - 它会超时。

这可以通过T-SQL完成吗？

Answer 1

IDENTITY一旦设置，您就无法删除规范。要删除整个列：ALTER TABLE yourTableDROP COLUMN yourCOlumn;有关ALTER TABLE的信息如果您需要保留数据，但删除IDENTITY列，则需要：创建一个新列将数据从现有IDENTITY列传输到新列删除现有IDENTITY列。将新列重命名为原始列名称

Answer 2

如果您想在不添加和填充新列的情况下执行此操作，而无需重新排序列，并且几乎没有停机时间，因为表上没有数据发生变化，让我们使用分区功能做一些魔术（但由于没有使用分区，所以不要需要企业版）：删除所有指向此表的外键脚本要创建的表; 重命名所有内容，例如'MyTable2'，'MyIndex2'等。删除IDENTITY规范。你现在应该有两个“相同的” - 表，一个是完整的，另一个是空的，没有IDENTITY。跑 ALTER TABLE [Original] SWITCH TO [Original2]现在您的原始表将为空，新表将具有数据。您已切换两个表的元数据（即时）。删除原始（现在为空的表），exec sys.sp_rename将各种模式对象重命名为原始名称，然后可以重新创建外键。例如，给定：CREATE TABLE Original(  Id INT IDENTITY PRIMARY KEY, Value NVARCHAR(300));CREATE NONCLUSTERED INDEX IX_Original_Value ON Original (Value);INSERT INTO OriginalSELECT 'abcd'UNION ALL SELECT 'defg';您可以执行以下操作：--create new table with no IDENTITYCREATE TABLE Original2(  Id INT PRIMARY KEY, Value NVARCHAR(300));CREATE NONCLUSTERED INDEX IX_Original_Value2 ON Original2 (Value);--data before switchSELECT 'Original', *FROM OriginalUNION ALLSELECT 'Original2', *FROM Original2;ALTER TABLE Original SWITCH TO Original2;--data after switchSELECT 'Original', *FROM OriginalUNION ALLSELECT 'Original2', *FROM Original2;--clean upDROP TABLE Original;EXEC sys.sp_rename 'Original2.IX_Original_Value2', 'IX_Original_Value', 'INDEX';EXEC sys.sp_rename 'Original2', 'Original', 'OBJECT';UPDATE OriginalSET Id = Id + 1;SELECT *FROM Original;

Answer 3

这会受到外键和主键约束的影响，所以这里有一些脚本可以帮助你：首先，创建一个具有临时名称的重复列：alter table yourTable add tempId int NOT NULL default -1;update yourTable set tempId = id;接下来，获取主键约束的名称：SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'yourTable';现在尝试删除列的主键约束：ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;如果你有外键，它将失败，所以如果这样，删除外键约束。 保持记录表明你已经开始运行，因此你可以再添加限制因素！SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'otherTable';alter table otherTable drop constraint fk_otherTable_yourTable;commit;..删除所有外键约束后，您将能够删除PK约束，删除该列，重命名临时列，并将PK约束添加到该列：ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;alter table yourTable drop column id;EXEC sp_rename 'yourTable.tempId', 'id', 'COLUMN';ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id) commit;最后，添加FK约束：alter table otherTable add constraint fk_otherTable_yourTable foreign key (yourTable_id) references yourTable(id);..El Fin！