使用 SQL Express 2017 删除重复项

Question

我有一个包含 1.2 亿行的表。其中大约 800 万行是重复的，具体取决于我用于确定重复项的值/列。为了论证起见，我正在测试电子邮件列与多列，以查看我的数据会发生什么。

该文件大约为 10GB，因此由于 SQL Express 的大小限制，我不能简单地向数据库添加另一个表。相反，我想我会尝试使用临时表提取、截断、插入，因为我一直在尝试这种方法。

我知道我可以使用 CTE 来删除重复项，但每次我尝试这样做都需要很长时间并且我的系统锁定。我的解决方案是执行以下操作。

1.Extract all rows to tempdb
2.Sort by Min(id)
3.Truncate original table
4.Transfer new unique data from tempdb back to main table
5.Take the extra duplicates and trim to uniques using Delimit
6.Import the leftover rows back into the database. 

我的表如下所示。

Name    Gender   Age   Email              ID
Jolly   Female   28    [e-mail]    1
Jolly   Female   28    [e-mail]    2
Jolly   Female   28    [e-mail]    3
Kate    Female   36    [e-mail]      4
Kate    Female   36    [e-mail]      5
Kate    Female   36    [e-mail]      6
Jack    Male     46    [e-mail]      7
Jack    Male     46    [e-mail]      8
Jack    Male     46    [e-mail]      9

我的代码

SET IDENTITY_INSERT test.dbo.contacts ON
GO
select name, gender, age, email, id into ##contacts
from test.dbo.contacts
WHERE id IN
(SELECT MIN(id) FROM test.dbo.contacts GROUP BY name)
TRUNCATE TABLE test.dbo.contacts
INSERT INTO test.dbo.contacts
SELECT name, gender, age, total_score, id
from ##students
SET IDENTITY_INSERT test.dbo.contactsOFF
GO

这段代码几乎可以工作，除了我看到的以下错误。 "只有在使用列列表且IDENTITY_INSERT为 ON 时，才能指定表'test.dbo.contacts'中标识列的显式值。

我完全不知道为什么自从我打开和关闭identity_insert以来我一直看到该消息。

有人可以告诉我代码中缺少什么吗？如果有人有另一种解决方案来保留独特的行，我很想听听。

Answer 1

你说你最初的问题是"这需要很长时间，我的系统锁定了"。 问题是操作和锁升级到表锁所需的时间量。 我的建议是分解操作，以便您一次删除少于 5000 行。 我假设每个名称的重复项少于 5000 个。 您可以在此处阅读有关锁定升级的更多信息： https://www.sqlpassion.at/archive/2014/02/25/lock-escalations/关于你的问题(身份插入)，你的脚本至少包含两个错误，所以我想它不是原始错误，所以很难说为什么原始错误失败。

use test;
if object_ID('dbo.contacts') is not null drop table dbo.contacts;
CREATE TABLE dbo.contacts
(
id int identity(1,1) primary key clustered, 
name    nvarchar(50),
gender  varchar(15),
age     tinyint,
email   nvarchar(50),
TS      Timestamp
)
INSERT INTO [dbo].[contacts]([name],[gender],[age],[email])
VALUES
('Jolly','Female',28,'[e-mail]'),
('Jolly','Female',28,'[e-mail]'),
('Jolly','Female',28,'[e-mail]'),
('Kate','Female',36,'[e-mail]'),
('Kate','Female',36,'[e-mail]'),
('Kate','Female',36,'[e-mail]'),
('Jack','Male',46,'[e-mail]'),
('Jack','Male',46,'[e-mail]'),
('Jack','Male',46,'[e-mail]');
--for the purpose of the lock escalation, I assume you have less then 5.000 duplicates for each single name.
if object_ID('tempdb..#KillList') is not null drop table #KillList;
SELECT KL.*, C.TS
into #KillList
from 
(
SELECT [name], min(ID) GoodID       
from dbo.contacts
group by name 
having count(*) > 1
) KL inner join 
dbo.contacts C
ON KL.GoodID = C.id
--This has the purpose of testing concurrent updates on relevant rows
--UPDATE [dbo].[contacts] SET Age = 47 where ID=7;
--DELETE [dbo].[contacts] where ID=7;
while EXISTS (SELECT top 1 1 from #KillList)
BEGIN 
DECLARE @id int; 
DECLARE @name nvarchar(50);
DECLARE @TS binary(8);
SELECT top 1 @id=GoodID, @name=Name, @TS=TS from #KillList;
BEGIN TRAN
if exists (SELECT * from [dbo].[contacts] where id=@id and TS=@TS)
BEGIN 
DELETE FROM C
from [dbo].[contacts] C
where id <> @id and Name = @name;
DELETE FROM #KillList where Name = @name;
END
ELSE 
BEGIN
ROLLBACK TRAN;
RAISERROR('Concurrency error while deleting %s', 16, 1, @name);
RETURN;
END 
commit TRAN;
END 
SELECT * from [dbo].[contacts];

Answer 2

我是这样写的，你可以看到每个查询的子结果。 内部 sql 不应该有 *，而是使用 id。

delete from [contacts] where id in 
(
select id from
(
select *, ROW_NUMBER() over (partition by name, gender, age, email order by id) as rowid from [contacts]
) rowstobedeleted where rowid>1
)

如果这需要太长时间/产生大量负载，您可以使用SET ROWCOUNT提供较小的块，但随后您需要运行它，直到不再删除任何内容。

Answer 3

我认为你需要这样的东西：

INSERT INTO test.dbo.contacts (idcol1,col2) 
VALUES (value1,value2)