Cluster on partitioned table

Question

Post reply

Cluster on partitioned table

as_1234

SSCrazy

Points: 2842
More actions
July 17, 2018 at 8:35 am

#354510

All,
My table configuration is as follows:
Field 1 is a uniqueidentifier, has rowguid set to yes and a default of 'newid'
Field 2 is a date field
There are other fields but I don't think they are relevant to the question.
On a none partitioned table I would have field1 as my primary key and the table would be clustered based on this index.
This table has a partition on field2
At the moment I have the primary key based on field1 but it's not clustered.
if I want to use switch then I have to base any keys on the partition key. My primary key therefore has to include the partition key?
In terms of clustering I can see three options:
Leave it un-clustered - Heaps are normally a bad design?
Cluster on field2 partition key - That kind of makes sense as it's a partition column but using field1, which is unique, would be more logical for a primary key? Also a lot of updates are based on field1
Cluster on field1 and the partition key - This kind of makes sense as field1 is unique but, as mentioned above, is including more fields than are necessary to make it unique
I would appreciate advice on whether my understanding above is correct and whether I've missed a clustering option? Which is the best clustering option will vary so I'll only know by testing?
Thanks

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

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Are there any other tables that point to this table using "field 1" as a Foreign Key?

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

as_1234 SSCrazy Points: 2842 More actions · Answer 2

Hello,

Thanks for your help. Yes there is. I got so caught up in the partitioning I forgot about the more simple issue of joins :angry:

Thanks

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

My understanding of partitioned tables may leave something to be desired but my understanding of it all is that any UNIQUE index that is also ALIGNED with the partitioned will automatically have the partitioning key added to it. That blows FKs out of the water for any partitioning that is based on temporal columns because it's going to require two columns and the 2nd column (the date column) is indeterminate.

In order to to be able to use the likes of SWITCH, all of the indexes on the table must be aligned. The work around is to drop non-aligned indexes, do your SWITCH, then re-establish the non-aligned indexes. Since we're talking about a PK that would be the target of many FKs, that would blow a whole lot of FKs out of the water, making it all seem not worth it to me.

Bottom line is, I don't know how to help you on this. Hopefully, the fellow I consider to be one of the "I actually use partitions on huge tables" expert,s on the subject of partitioning, Mr. Eddie Wuerch , will see this and possibly provide an answer.