• Fredy James (4/7/2014)


    I know this is an old post, but completeness of this thread I would like to add the below comments.

    DBA as well as Jeff.Mason was partially correct in the sense that normally by default primary keys are clustered. Eventhough it is not a requirement for a partition column NOT to be in the primary key, but best practice is for a partition column to be part of the clustered index. Without this, i.e. partition key not in the clustered index, and since in SQL Server, all indexes and data sitting on top of clustered index, SQL Engine will always perform a table-scan and not partition scan and hence creating a partition is not affective at all.

    Cheers,

    Fredy

    Just a couple of additional notes there...

    If the index supports a query in such a fashion as to do an index seek (and a possible range scan for multi-row queries), it won't do a table scan any more than it would in a monolithic table. And, IIRC, the partition scans are only possible if you include the partitioning column in the criteria of the query. Since partition scans are really like smaller table scans, they'll still be a lot less effective than good code that does index seeks. Performance isn't necessarily the primary reason that you'd want to partition a table because such performance has a pretty narrow scope of queries.

    The other thing is that you may have to consider whether or not the PK is going to be a target of an FK. If it is, you can't add the partitioning column to it. You'll have to live with a non-aligned unique index.

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