February 12, 2010 at 10:52 am
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.
February 13, 2010 at 4:25 am
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/
January 9, 2011 at 11:55 am
This bug is probably also a reason why partitioning is not that commonly used:
... it would be a nice feature however, if it worked.
January 12, 2011 at 4:11 pm
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