Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning key examples vs clustered index rules Expand / Collapse
Author
Message
Posted Friday, February 12, 2010 10:52 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:37 AM
Points: 97, Visits: 438
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.
Post #864783
Posted Saturday, February 13, 2010 4:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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/
Post #865066
Posted Sunday, January 9, 2011 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 30, 2011 6:08 AM
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.
Post #1045024
Posted Wednesday, January 12, 2011 4:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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/
Post #1046888
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse