Post reply

Table Paritioning

  • November 9, 2015 at 4:38 pm

    #322143

    I have written a Stored Procedure to Archive Tables.

    I have been asked to Partition the tables.

    Would I be better off to hold off on the archiving and just go with partitioning?

    If I archive then partition would that complicate things?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 9, 2015 at 4:42 pm

    #1838725

    Welsh Corgi (11/9/2015)

    I have written a Stored Procedure to Archive Tables.

    I have been asked to Partition the tables.

    Would I be better off to hold off on the archiving and just go with partitioning?

    If I archive then partition would that complicate things?

    Truly, "It Depends" on what kind of a table it is, how big it is, whether any FK's are pointing at the table, which Edition of SQL Server you have, and a dozen other things. Just not enough information in the post above to be able to answer the question correctly.

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

  • November 9, 2015 at 5:16 pm

    #1838734

    Jeff Moden (11/9/2015)

    Welsh Corgi (11/9/2015)

    I have written a Stored Procedure to Archive Tables.

    I have been asked to Partition the tables.

    Would I be better off to hold off on the archiving and just go with partitioning?

    If I archive then partition would that complicate things?

    Truly, "It Depends" on what kind of a table it is, how big it is, whether any FK's are pointing at the table, which Edition of SQL Server you have, and a dozen other things. Just not enough information in the post above to be able to answer the question correctly.

    Thanks for the reply.

    One table is 3.5 million records, a lot of FY constrains, to many indexes and a lot of columns.

    Another table has 32.5 million records, a few indexes and FK's and not so many columns.

    Does that help?

    Edit: Current tables have multiple file groups.

    My intention is to create the table from scratch and simplifythe filegoups

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 9, 2015 at 5:37 pm

    #1838738

    SQL Server 2012 Enterprise Edition SP2. Moving to 2014, probably soon.

    Please let me know what other information that need to make a determination.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 17, 2015 at 7:34 pm

    #1840515

    Welsh Corgi (11/9/2015)

    Jeff Moden (11/9/2015)

    Welsh Corgi (11/9/2015)

    I have written a Stored Procedure to Archive Tables.

    I have been asked to Partition the tables.

    Would I be better off to hold off on the archiving and just go with partitioning?

    If I archive then partition would that complicate things?

    Truly, "It Depends" on what kind of a table it is, how big it is, whether any FK's are pointing at the table, which Edition of SQL Server you have, and a dozen other things. Just not enough information in the post above to be able to answer the question correctly.

    Thanks for the reply.

    One table is 3.5 million records, a lot of FY constrains, to many indexes and a lot of columns.

    Another table has 32.5 million records, a few indexes and FK's and not so many columns.

    Does that help?

    Edit: Current tables have multiple file groups.

    My intention is to create the table from scratch and simplifythe filegoups

    Not much. Are there any other tables with FK's that point to the two tables in question? Also, are these two tables like audit tables or closed invoice tables or are all rows subject to the possibility of a modification?

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

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

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