SQL服务器 - 从列在一个表中删除标识

我们有一个5GB表(近500万行),我们要删除在列的一个标识属性,但是当我们试图通过SSMS要做到这一点 - 它超时。

可以通过这个T-SQL来完成​​?

我们使用的是SQL Server 2005企业版

--------------解决方案-------------

你不能删除IDENTITY一旦设置规范。

要删除整列:

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

有关ALTER TABLE的信息在这里

如果你需要保留的数据,但删除IDENTITY列,你将需要:

  • 创建一个新列
  • 从现有的传输数据IDENTITY列到新列
  • 删除现有IDENTITY列。

这就会变得混乱与外国和主键约束,所以这里的一些脚本来帮助你的方式:

首先,创建一个临时名称重复的列:

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;

如果你有外键,它会失败,所以如果是这样删除外键约束。 保留哪个表的轨道运行了这么你可以添加约束BACK在后面!

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);
..

萨尔瓦多鳍!

魔术分区功能(但由于没有分区使用你不需要企业版):

  1. 删除指向该表的外键
  2. 脚本要创建的表; 一切都重新命名如“MyTable2','MyIndex2”等删除IDENTITY规范。
  3. 您现在应该有两个“相同的”-ish表,一个完整的,其他的空与没有标识。
  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;

分类:SQL 时间:2015-03-15 人气:3
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 1.790 (s). 10 q(s)