Delete Duplicates

  • Any thoughts on performance?

    If I recall correctly I came across this syntax with help from Erland Sommarskog. I was looking for a way to remove duplicates from a table with over 100 million rows. As it turned out, this was the only method I could use that would perform well enough given the amount of cpu and ram, that would even do the job.


  • Excellent questions..............

  • Of note, I learned something today as well regarding the semi-colons as my background is vb where semi-colons make our life more difficult. I am focussing on getting those semi-colons into my queries. Thanks for all the nice comments... appreciated!


  • Yes, this is useful. In fact I ran into a case yesterday where I was able to employ this method to remove duplicates from a table. And as performance goes it was much more efficient then the method I would commonly have used which was copy all the data out into a temp table, truncate and then select distinct back into the table.

  • If you have code like this or need to use code like this, you have other greater issues going on and need to put the pipe down.

  • I agree up to a point. But if you inherit an environment where someone has created a number of tables with no primary keys, then this can be useful in scripting out a fix to resolve those greater issues. I have worked on a lot of projects large and small and someone always manages to create such a table which I then have to fix, usually after it gets loaded with duplicates and ends up in production.

  • Excellent question...


  • excellent question with great discussion by gr8 ppl 🙂

    To get quick answer follow this link:

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply