Frank-461314 (4/5/2010)
Hello,I have a 6 billion row table that I want to partition. My question lies with the clustered index and partition key. I want to partition on date, but cluster index on client, date. This would cause the indexes to be aligned, but storage would not be. Partitioning on day makes alot of sense for maintenance reasons, but our reporting relies more on Client, Date.
Does anyone see any consequences with this set up? My worries is the maintenance of the clustered index. Would the clustered index need to be maintained on the 6 billion rows, or would the partition swapping help maintain it?
Thanks...
I know this is old, but, if the reporting is mainly on client and date, isn't that the same as reporting on date and client? That is, the queries will include both the date (or a date range) and the client ID. So partitioning and aligning on the date should be fine, I would think.
That is, I would cluster on date and then client. Also, this makes adding new data faster since tables are not split so data can be inserted in client order.
If this is not right, someone please let me know. Thanks.
David Walker