我需要从一个相当大的SQL Server表(即300,000+行)中删除重复的行。
当然,由于RowID标识字段的存在,这些行不会完全重复。
MyTable
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
我该怎么做呢?
我需要从一个相当大的SQL Server表(即300,000+行)中删除重复的行。
当然,由于RowID标识字段的存在,这些行不会完全重复。
MyTable
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
我该怎么做呢?
当前回答
从应用程序级别(不幸的是)。我同意防止重复的正确方法是在数据库级别上使用唯一的索引,但是在SQL Server 2005中,一个索引只能是900字节,而我的varchar(2048)字段打破了这一规定。
我不知道它的性能如何,但我认为您可以编写一个触发器来强制执行这一点,即使您不能直接使用索引。喜欢的东西:
-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism
ON stories
after INSERT, UPDATE
AS
DECLARE @cnt AS INT
SELECT @cnt = Count(*)
FROM stories
INNER JOIN inserted
ON ( stories.story = inserted.story
AND stories.story_id != inserted.story_id )
IF @cnt > 0
BEGIN
RAISERROR('plagiarism detected',16,1)
ROLLBACK TRANSACTION
END
另外,varchar(2048)对我来说听起来很可疑(生活中有些东西是2048字节,但这很少见);它真的应该不是varchar(max)吗?
其他回答
有时使用软删除机制,其中记录日期以指示删除的日期。在这种情况下,可以使用UPDATE语句根据重复的条目更新该字段。
UPDATE MY_TABLE
SET DELETED = getDate()
WHERE TABLE_ID IN (
SELECT x.TABLE_ID
FROM MY_TABLE x
JOIN (SELECT min(TABLE_ID) id, COL_1, COL_2, COL_3
FROM MY_TABLE d
GROUP BY d.COL_1, d.COL_2, d.COL_3
HAVING count(*) > 1) AS d ON d.COL_1 = x.COL_1
AND d.COL_2 = x.COL_2
AND d.COL_3 = x.COL_3
AND d.TABLE_ID <> x.TABLE_ID
/*WHERE x.COL_4 <> 'D' -- Additional filter*/)
对于包含大约3000万行、重复量有高有低的中等数据表,这种方法非常适用。
我想提一下这种方法,它可能是有帮助的,并且适用于所有SQL服务器: 通常只有一到两个副本,副本的id和数量是已知的。在这种情况下:
SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
这个查询为我展示了非常好的性能:
DELETE tbl
FROM
MyTable tbl
WHERE
EXISTS (
SELECT
*
FROM
MyTable tbl2
WHERE
tbl2.SameValue = tbl.SameValue
AND tbl.IdUniqueValue < tbl2.IdUniqueValue
)
它在30秒多一点的时间内从2M的表中删除了1M行(50%重复)
使用这个
WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1
如果您想预览要删除的行,并控制要保留的重复行。参见http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/
with MYCTE as (
SELECT ROW_NUMBER() OVER (
PARTITION BY DuplicateKey1
,DuplicateKey2 -- optional
ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed
) RN
FROM MyTable
)
DELETE FROM MYCTE
WHERE RN > 1