I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id.

Normally I would use the following statement to remove duplicates:

FROM emails
WHERE email_id NOT IN (
SELECT MAX(email_id) FROM emails
GROUP BY record_id, from_add, to_add, subject, body)

However, body is datatype text, so it cannot be compared/grouped. It gives the following error message on the select query:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

What is the best way to identify duplicates with the text datatype? Do I have to convert the column to varchar first?

  • 1
    Can you afford to change the datatype to VARCHAR(MAX) or NVARCHAR(MAX)? Dec 28, 2012 at 0:11
  • That's my last resort/backup plan. I'm hoping there's a way to avoid that.
    – bd33
    Dec 28, 2012 at 0:15

2 Answers 2


You don't have to convert the actual column. Just its usage in the SELECT query.

For a one off clean up task I'd probably just go

;WITH cte
                                                CAST(body AS VARCHAR(MAX)) 
                                       ORDER BY email_id DESC) RN
         FROM   emails)
WHERE  RN > 1 

Unless dealing with huge amounts of data that might make spending time on a more efficient solution (that doesn't have to sort huge strings) worthwhile.

Or alternatively you could try

     AS (SELECT MAX(email_id) AS max_email_id
         FROM   emails
         GROUP  BY record_id,
                   CAST(body AS VARCHAR(max)))
FROM   emails e1
                   FROM   T
                   WHERE  max_email_id = e1.email_id) 

This is likely to give you a better plan than NOT IN as SQL Server treats MAX(not_nullable_column) as being potentially nullable and hence you end up with a potentially more expensive plan.


  • Perfect - this is what I was looking for. Thank you! Out of curiosity - you used a cte and changed the format of the query rather than adding CAST(body AS VARCHAR(MAX) to my original query... why is that?
    – bd33
    Dec 28, 2012 at 21:21
  • @bd33 - Often this is cheaper although maybe not in this case as a hash aggregate on the long strings could well turn out better than sorting them. Check the execution plans for both versions. Dec 28, 2012 at 23:44

Try hashing thusly:

select master.sys.fn_repl_hash_binary( Cast( Cast( body as VarChar(max) ) as VarBinary(max) ) )
  from emails

If that works you should be able to group by the hash value of the body to match messages.

Thanks to John Huang.

  • It returns the error: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "master" or the user-defined function or aggregate "master.sys.fn_repl_hash_binary", or the name is ambiguous. So I looked in the functions for both the master system database and the database in question and did not find that function. Any advice? What am I missing? I'm not really a DBA, apologies for the novice question. :)
    – bd33
    Dec 28, 2012 at 16:14
  • @bd33 - My bad. I tested it on SQL Server 2008, though I thought that I has seen others refer to it being available in 2005. I just checked my elder 2005 system and it isn't there. Please have a look at the function defined in the accepted answer here. It survived a quick test on my 2005 system. (Use 'MD5' for @Algo.)
    – HABO
    Dec 28, 2012 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.