Home Forums Article Discussions Article Discussions by Author Discuss content posted by Stefan Krzywicki Removing Duplicates RE: Removing Duplicates

  • October 15, 2013 at 5:48 pm

    #1658758

    prennings (10/15/2013)

    I tried this solution on table with over 7 million results. (Around a 100.000 duplicates each) however the query did run for around 57 minutes then my server crashed and the database I was performing it on crashed and went into recovery mode... (needed to reboot the server to made it available again :-)).

    So the function is great for smaller databases but not a real solution in my case for a database with 7+ million results. (In the end I had a total of 1654 rows left, so yeah ALLOT of duplicates). In my case I had the luck I could run:

    SELECT

    DISTINCT CreateStatement,

    dbo.split(dbo.split(CreateStatement, '[', 2), ']') AS Tablename

    FROM

    CB_I_Drop_Create_Indexes

    Above query took me arround 4 minutes to complete the same job. 🙂 (In the createStatement column the tablename for the index was in that column row). Hope someone also has some use for it.

    I would love to see the split function. Any chance of you posting it, please? Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)