Post reply

Which is better Truncate by Partition or Delete with where clause?

  • August 5, 2014 at 7:35 am

    #291682

    Hi All,

    I am working on designing a project. I will have about 10 table all with 3/5 partition (if the partition is the best way to go) the data per partition will range from 10k rows to 130mil rows.

    So my question is would it be worth setting up partitions on these tables and truncating by partition or just do a delete? I will be doing this using SSIS. I just want to make sure the gain is

    worth the effort of developing using Partitions.

    Thanks for any help.

  • August 5, 2014 at 7:44 am

    #1735193

    Truncate is a table wide operation. If you have your table partitioned, truncating will truncate all partitions.

    If you want to delete the data in a single partition, the quickest way is to switch the partition out to a "stage" table and then truncate that table. Otherwise use delete.

    Another alternative is if you are using SQL 2000 style partitions where it was really a bunch of tables brought together by a view.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 5, 2014 at 7:48 am

    #1735194

    Ok Thanks for the quick respones, that answered my question.

  • August 5, 2014 at 7:50 am

    #1735195

    You are welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 4 (of 4 total)

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