• sanjaydut26 (8/27/2016)


    Hi,

    I am new to table partitioning.

    I have a large table with millions of rows, There is no Date column in the table and i have the primary key column with Identity(1,1). Can i still paritition the table without date column.

    Thanks in advance,

    Sanjay

    Just to add a strong second to what Gail wrote...

    Partitioning isn't a panacea-of-performance solution. In fact, it will frequently slow queries down because of the extra B-TREEs that it has to slog through for each partition. A well written query (has SARGable predicates that can actually use an index properly) executed against a properly designed, properly indexed monolithic table will usually beat the same query executed against a partitioned table even in the face of so-called "partition elimination".

    The greatest irony is that the things that you must do to the table you want to partition (setting up the correct indexes being the most important) is the same thing you need to do to make the queries run faster without partitioning.

    To answer your original question, yes, you can partition without a date column but you need some other immutable column to do it with and an IDENTITY column could be used but, unless the older data is completely static so that the indexes don't change on the older partitions allowing you to greatly reduce index maintenance and, possibly, backups (older partitions could be set to READ_ONLY after a final backup), there's no performance advantage to partitioning and could actually cause things to run a bit slower.

    Performance is in the code. Concentrate on fixing the code.

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