Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

DELETE
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?

share|improve this question
1  
Can you afford to change the datatype to VARCHAR(MAX) or NVARCHAR(MAX)? –  ypercube Dec 28 '12 at 0:11
    
That's my last resort/backup plan. I'm hoping there's a way to avoid that. –  bd33 Dec 28 '12 at 0:15
add comment

migrated from stackoverflow.com Dec 28 '12 at 19:10

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

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
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY record_id, 
                                                from_add, 
                                                to_add, 
                                                subject, 
                                                CAST(body AS VARCHAR(MAX)) 
                                       ORDER BY email_id DESC) RN
         FROM   emails)
DELETE FROM cte
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

;WITH T
     AS (SELECT MAX(email_id) AS max_email_id
         FROM   emails
         GROUP  BY record_id,
                   from_add,
                   to_add,
                   subject,
                   CAST(body AS VARCHAR(max)))
DELETE e1
FROM   emails e1
WHERE  NOT EXISTS (SELECT *
                   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.

Plan

share|improve this answer
    
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 '12 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. –  Martin Smith Dec 28 '12 at 23:44
add comment

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.

share|improve this answer
    
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 '12 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 '12 at 19:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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