Delete Duplicates

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    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.

    Jamie

  • Anipaul

    SSC-Insane

    Points: 24681

    Excellent questions..............

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    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!

    Jamie

  • Cliff Jones

    SSChampion

    Points: 10517

    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.

  • DB_Andrew

    SSC Eights!

    Points: 979

    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.

  • Cliff Jones

    SSChampion

    Points: 10517

    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.

  • abiel.andrew

    Old Hand

    Points: 383

    Excellent question...

    🙂

  • kapil_kk

    SSC-Insane

    Points: 21316

    excellent question with great discussion by gr8 ppl 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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