我在 SQL Server 2019 的一个表中有一个数据库,其中包含大量 varbinary(MAX) 重复记录。我们需要删除重复项,因此我运行了删除查询来删除重复项。数据库大小在删除语句之前没有保持不变,而是在查询完成后增加。我即将应用 DBCC SHRINKFILE(N'myDatabase', 0),但我真的很好奇为什么数据库大小会增加。附件是我的删除查询。
DELETE from Document WHERE DocumentId in
(
SELECT t.DocumentId
FROM (
SELECT s.*,
ROW_NUMBER() OVER (PARTITION BY s.DocumentName, s.SubmitDateTime, s.DocumentType ORDER BY s.DocumentName, s.SubmitDateTime, s.DocumentType) AS [RowCount]
FROM Document s
LEFT OUTER JOIN Staging b ON s.DocumentId = b.DocumentId
LEFT OUTER JOIN RejectTable r ON s.DocumentId = r.DocID
WHERE b.DocumentId IS NULL
AND r.DocID IS NULL
AND s.SubmitDateTime IS NOT NULL
AND s.InsertDateTime IS NOT NULL
AND s.DocumentName IS NOT NULL
AND s.DocumentContent IS NOT NULL
AND s.Description ='EmployeesDocument'
) t
WHERE t.[RowCount] > 1
)