Partitioning key examples vs clustered index rules

  • Hi there,

    I got a question that seems to be simple but yet not answered in any blogs/forums so far.

    I read the article about partitioning (http://www.sqlservercentral.com/articles/partition/64740/) here (and many more) articles about partitioning using simple keys like 10,20,30 or date-values when refering to the sales table of adventureworks.

    However I have second piece of knowledge about clustered indexes (e.g. http://www.sqlservercentral.com/articles/Indexing/68439/)

    Which is basiclly saying a clustered key must be Narrow, Unique, Unchanging, Ever increasing.

    So at first I builded a simple key asigning the customers to there portals. Let's say e.g. 1,2,3,4.

    This would be in conflict with the statements for clustered indexes (unique).

    So again I added to my key the customerid so it could be unique again. But there were historising tables, too, so I had to add the valid_from to make the key unique again.

    As I found out, the partition advantange is only used if the server can rule out that some partitions needn't to be used.

    Quering one table is easy if you put the key into the where-clause.

    However joining with another table would not transfer the partition-usage information so I had to add the partion-key in the join too

    Example

    SELECT user_id

    FROM table1

    WHERE key = 2

    Uses the partionkey-index for table1 and the key infos to rule partitions out.

    SELECT user_id

    FROM table1

    INNER JOIN table2 ON table1.user_id = table2.user_id

    WHERE key = 2

    Uses the partionkey-index for table1 but not for table2

    SELECT user_id

    FROM table1

    INNER JOIN table2 ON table1.user_id = table2.user_id AND table1.key = table2.key

    WHERE key = 2

    Uses the partionkey-index for both tables.

    So my question here:

    Has the partition-key to be unique to have the same performance as usual clustered index

    If it has to be unique, is a partition-key no good for a customer-DWH?

    Thanks for your help.

    Mitch.

  • Partitioning sounds really good and should offer all sorts of performance improvements but the reality is that the key choice(s) for native partitioning is often so restrictive as to make it unusable. You may find yourself having to use triggers or indexed views to maintain the primary key because your choice of partitioning contains more than one column.

    In some work I did I found that partitioned views were a much better solution, and we're talking sql 2008 here. I agree that in the main working examples for partitioning are few and far between and most seem to rely upon partitioning by year held in a nice isodate format in an integer - I've never yet actually wanted to partition by year, some types of partioning I've done include the first letter of a string, ranges of words, ranges of leading characters to a string. Days of the week, days of a month, numeric ranges.

    So to answer your question - it all depends what you're trying to do and you need to test.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • This bug is probably also a reason why partitioning is not that commonly used:

    http://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance

    ... it would be a nice feature however, if it worked.

  • I do make use of partitioning despite the restrictions, there's a link on my blog to a a large document i produced for a presentation I did which talked about partitioning data in general - not just native partitioning. There are maybe not so obvious reasons for partitioning which can be worth the effort - I just find the inability to get unique constraints on a partitioned table where the partitioning key is actually a foreign key a bit of a pain.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

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

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