从表中的列中删除标识

Question

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

Answer 1

IDENTITY一旦设置，您就无法删除规范。

要删除整个列：

ALTER TABLE yourTableDROP COLUMN yourCOlumn;

有关ALTER TABLE的信息

如果您需要保留数据，但删除IDENTITY列，则需要：

• 创建一个新列

• 将数据从现有IDENTITY列传输到新列

• 删除现有IDENTITY列。

• 将新列重命名为原始列名称

Answer 2

如果您想在不添加和填充新列的情况下执行此操作，而无需重新排序列，并且几乎没有停机时间，因为表上没有数据发生变化，让我们使用分区功能做一些魔术（但由于没有使用分区，所以不要需要企业版）：

1. 删除所有指向此表的外键

2. 脚本要创建的表; 重命名所有内容，例如'MyTable2'，'MyIndex2'等。删除IDENTITY规范。

3. 你现在应该有两个“相同的” - 表，一个是完整的，另一个是空的，没有IDENTITY。

4. 跑 ALTER TABLE [Original] SWITCH TO [Original2]

5. 现在您的原始表将为空，新表将具有数据。您已切换两个表的元数据（即时）。

6. 删除原始（现在为空的表），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 Original

SELECT 'abcd'

UNION ALL 

SELECT 'defg';

您可以执行以下操作：

--create new table with no IDENTITY

CREATE TABLE Original2

(

  Id INT PRIMARY KEY

, Value NVARCHAR(300)

);

CREATE NONCLUSTERED INDEX IX_Original_Value2 ON Original2 (Value);

--data before switch

SELECT 'Original', *

FROM Original

UNION ALL

SELECT 'Original2', *

FROM Original2;

ALTER TABLE Original SWITCH TO Original2;

--data after switch

SELECT 'Original', *

FROM Original

UNION ALL

SELECT 'Original2', *

FROM Original2;

--clean up

DROP TABLE Original;

EXEC sys.sp_rename 'Original2.IX_Original_Value2', 'IX_Original_Value', 'INDEX';

EXEC sys.sp_rename 'Original2', 'Original', 'OBJECT';

UPDATE Original

SET 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！