title: Find binary duplicate records SQL Server 2008 (Data type image)
body: <p>I have inherited a database that has a table (300gb) full of SQL datatype <code>Image</code>. I understand this datatype is depreciated. </p>
  
  <p>As a routine cleanup I want to delete all duplicate <code>Image</code>'s from the table where certain conditions are met.</p>
  
  <p>How do I compare binary data using SQL efficiently? Is the = equality operator sufficient?</p>
  
  <p>Here is a scenario:</p>
  
  <pre><code>Table 'Paperwork'
    int ID
    int EmployeeID
    int AnotherID
    int AnotherFKID
    image Attachment
  </code></pre>
  
  <p>I want to find all rows where the <code>Attachment</code>, <code>EmployeeID</code>, <code>AnotherID</code> and <code>AnotherFKID</code> are the same. It needs to be done with minimal impact on the database as there are over 1,116,313 rows.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>The SQL Server <code>Image</code> data type does not support <code>LIKE</code> or the usual comparison operators.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>Thanks to @Martin who suggested the <code>Image</code> be casted to varbinary. I have added to this to get the MD5 checksum using Hashbytes</p>
  
  <p><code>HASHBYTES('MD5',CAST(cast([Attachment] as varbinary(max))as varbinary)) AS AttachmentMD5</code></p>