Home Forums SQL Server 2008 SQL Server 2008 - General deleting huge records From Table RE: deleting huge records From Table

  • November 22, 2010 at 6:10 am

    #1253686

    Eralper (11/22/2010)

    Thanks Jeff,

    I checked the BOL and I found :

    The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT… INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement.

    So I think populating a new table and then truncating the other might be more efficient when log size is considered.

    I agree. Just to be clear for folks that may think of doing it, don't ever shift from FULL recovery to SIMPLE recovery for the sake of archiving data because it will break the log chain for backups. It's ok to shift from FULL recovery to the BULK-LOGGED recovery, though.

    --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)