Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning key examples vs clustered index rules


Partitioning key examples vs clustered index rules

Author
Message
christian_t
christian_t
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 525
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.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
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.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Kasper Bengtsen
Kasper Bengtsen
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 10
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.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
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.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search