So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we should rethink aspects of the design are likely correct but unhelpful :)
I have a very large table, approx 150 fields wide and about 600m rows, that drives a large number of processes. This is in a data warehouse situation so we don't have ANY updates/inserts outside the scheduled load process, so it is heavily indexed.
A decision has been made to try partitioning this table, and I have some concerns about indexing a partitioned table. I don't have any experience with partitioning, so any input or links are appreciated. I couldn't locate specifically what I am after on BOL or msdn.
Currently we cluster on a field that we'll call
IncidentKey which is a
varchar(50) and not unique - we could have between 1-100 records with the same
IK (no comments please). We do often get new data on old
IncidentKey records so it's not sequential either.
I understand I need to include my partition field,
IncidentDate, in my clustered index key for the partition to work correctly. I'm thinking it would be
The question is, how will the mechanics of a clustered index work on a 2 part key in a partitioned table, if a record in a "new" partition should be before a record in an "old" partition in the clustered index?
For example, I have 5 records:
IncidentKey Date ABC123 1/1/2010 ABC123 7/1/2010 ABC123 1/1/2011 XYZ999 1/1/2010 XYZ999 7/1/2010
If I get a new record for
ABC123, 2/1/2011 it will need to be in the clustered index BEFORE
XYZ999, 1/1/2010. How does this work?
I'm assuming fragmentation and pointers, but I can't find any info on the physical storage and configuration of non-partitioned clustered indexes on partitioned tables with dual-part keys.