从表中的列中删除标识

我们有一个5GB 的表(将近5亿行) ，我们希望删除其中一列上的标识属性，但是当我们尝试通过 SSMS 这样做时，它会超时。

这可以通过 T-SQL 实现吗？

371852 次浏览
小开
最佳答案

一旦设置了 IDENTITY规范，就不能删除它。

删除整个列:

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

有关 更改桌子的资料请浏览此处

如果需要保留数据，但删除 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;

如果您有外键，它将失败，所以如果是这样，删除外键约束

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. 现在应该有两个“相同”的表，一个是满的，另一个是空的，没有标识。
  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
IF NOT EXISTS (SELECT * FROM Original) 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;

郑重声明，随着这个方法越来越流行，我一直想找到最初的来源(我自己没有想到) ，但是当然我早就忘记了我是在哪里找到最初的想法的。它可能在这里; 这是我能找到的唯一一个早于这个答案(时间飞逝，男孩和女孩) : https://social.technet.microsoft.com/wiki/contents/articles/17738.sql-server-quick-way-to-remove-the-identity-property.aspx

小开

我也有同样的问题。4个语句，而不是使用 GUI，这是非常快的。

  • 写个新专栏

    alter table users add newusernum int;

  • 将值复制完毕

    update users set newusernum=usernum;

  • 放下旧纵队

    alter table users drop column usernum;

  • 将新列重命名为旧列名

    EXEC sp_RENAME 'users.newusernum' , 'usernum', 'COLUMN';

小开

下面的脚本移除名为“ Id”的列的 Identity 字段

希望能有帮助。

BEGIN TRAN
BEGIN TRY
EXEC sp_rename '[SomeTable].[Id]', 'OldId';


ALTER TABLE [SomeTable] ADD Id int NULL


EXEC ('UPDATE [SomeTable] SET Id = OldId')


ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL


ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName];
ALTER TABLE [SomeTable] DROP COLUMN OldId
ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL
ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id)


ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL


COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT ERROR_MESSAGE ()
END CATCH
小开

以下代码工作正常，当我们不知道身份列名时。

需要将数据复制到新的临时表中，如 Invoice_DELETED。 下次我们用:

insert into Invoice_DELETED select * from Invoice where ...




SELECT t1.*
INTO Invoice_DELETED
FROM Invoice t1
LEFT JOIN Invoice ON 1 = 0
--WHERE t1.InvoiceID = @InvoiceID

有关详细解释，请参阅: Https://dba.stackexchange.com/a/138345/101038

小开

只是为了和我有同样问题的人。 如果你只是想做一些插入只是一次，你可以这样做。

假设您有一个包含两列的表

ID Identity (1,1) | Name Varchar

并希望插入 ID = 4的行。重新播种到3下一个就是4

DBCC CHECKIDENT([YourTable], RESEED, 3)

插入

INSERT  INTO [YourTable]
( Name )
VALUES  ( 'Client' )

让你的种子回到最高的 ID，让我们假设是15

DBCC CHECKIDENT([YourTable], RESEED, 15)

成交！

小开

我也有同样的要求，你可以试试这个方法，我个人推荐你，请手动设计你的表并生成脚本，我在下面所做的就是重命名旧的表以及它的备份约束。

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION


SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SI_Provider_Profile
DROP CONSTRAINT DF_SI_Provider_Profile_SIdtDateTimeStamp
GO
ALTER TABLE dbo.SI_Provider_Profile
DROP CONSTRAINT DF_SI_Provider_Profile_SIbHotelPreLoaded
GO
CREATE TABLE dbo.Tmp_SI_Provider_Profile
(
SI_lProvider_Profile_ID int NOT NULL,
SI_lSerko_Integrator_Token_ID int NOT NULL,
SI_sSerko_Integrator_Provider varchar(50) NOT NULL,
SI_sSerko_Integrator_Profile varchar(50) NOT NULL,
SI_dtDate_Time_Stamp datetime NOT NULL,
SI_lProvider_ID int NULL,
SI_sDisplay_Name varchar(10) NULL,
SI_lPurchased_From int NULL,
SI_sProvider_UniqueID varchar(255) NULL,
SI_bHotel_Pre_Loaded bit NOT NULL,
SI_sSiteName varchar(255) NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
DF_SI_Provider_Profile_SIdtDateTimeStamp DEFAULT (getdate()) FOR SI_dtDate_Time_Stamp
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
DF_SI_Provider_Profile_SIbHotelPreLoaded DEFAULT ((0)) FOR SI_bHotel_Pre_Loaded
GO
IF EXISTS(SELECT * FROM dbo.SI_Provider_Profile)
EXEC('INSERT INTO dbo.Tmp_SI_Provider_Profile (SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName)
SELECT SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName FROM dbo.SI_Provider_Profile WITH (HOLDLOCK TABLOCKX)')
GO


-- Rename the primary key constraint or unique key In SQL Server constraints such as primary keys or foreign keys are objects in their own right, even though they are dependent upon the "containing" table.
EXEC sp_rename 'dbo.SI_Provider_Profile.PK_SI_Provider_Profile', 'PK_SI_Provider_Profile_Old';
GO
-- backup old table in case of
EXECUTE sp_rename N'dbo.SI_Provider_Profile', N'SI_Provider_Profile_Old', 'OBJECT'
GO


EXECUTE sp_rename N'dbo.Tmp_SI_Provider_Profile', N'SI_Provider_Profile', 'OBJECT'
GO


ALTER TABLE dbo.SI_Provider_Profile ADD CONSTRAINT
PK_SI_Provider_Profile PRIMARY KEY NONCLUSTERED
(
SI_lProvider_Profile_ID
) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


GO
COMMIT TRANSACTION
小开 
ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'

但是上面的代码只有在没有主-外键关系的情况下才能工作

小开

在 SQLServer 中，您可以像下面这样打开和关闭标识插入:

设置 IDENTITY _ INSERT table _ name ON

请在这里查询

设置 IDENTITY _ INSERT table _ name OFF

小开 
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME DROP IDENTITY);

提交答案