sql - 查找二进制重复记录 SQL Server 2008(数据类型图像)

Question

我继承了一个数据库，其中有一个充满 SQL 数据类型 Image 的表 (300GB)。我了解此数据类型已贬值。

作为例行清理，我想从满足某些条件的表中删除所有重复的Image。

如何使用 SQL 有效比较二进制数据？ = 相等运算符是否足够？

这是一个场景:

Table 'Paperwork'
  int ID
  int EmployeeID
  int AnotherID
  int AnotherFKID
  image Attachment

我想查找 Attachment、EmployeeID、AnotherID 和 AnotherFKID 相同的所有行。由于有超过 1,116,313 行，因此需要在对数据库影响最小的情况下完成。

编辑

SQL Server Image 数据类型不支持 LIKE 或常用的比较运算符。

编辑

感谢 @Martin，他建议将 Image 转换为 varbinary。我已添加到此以使用 Hashbytes 获取 MD5 校验和

HASHBYTES('MD5',CAST(cast([附件] as varbinary(max))as varbinary)) AS AttachmentMD5

Answer 1

杰里米，

任何多合一脚本在读取 300g 数据时都会杀死缓冲区高速缓存。将工作分解为几个任务。

任务1

• 使用 ID 创建一个表格，并进行分组以显示三个 int 列的重复项

表格示例

    TableID  PaperWorkID GroupID
       1        14          1
       2        15          1
       3        21          2
       4        55          2

现在我们知道 PaperWorkID 14 和 15 共享相同的三个 int 列，因为它们位于同一组中。

任务2

• 向表中添加一列 (bigint)，并根据表 Paperwork 中的 Image 列的 DATALENGTH 填充该列表中的 PaperWorkID
• 根据数据长度和GroupID删除所有非重复项

任务3

• 向表中添加一列 varbinary(max)。
• 根据表中的 PaperWorkID 使用图像列的 MD5 哈希值填充该列
• 根据 MD5 哈希值和 GroupID 从表中删除所有非重复项

任务4

• 对 PaperWork 表进行 2 次备份
• 根据表中剩余的项目删除 Paperwork 中的重复记录。

如果图像列的数据是从纸张上扫描的，则两次扫描产生完全相同图像的可能性很小。如果数据上传了两次那么你很幸运。