Saturday, January 19, 2013 5:36 PMCan someone give me some high-level direction on how to approach this?
I'm given a task to do these 2 things:
- 1. Separate customers data across multiple filegroups using table partition (based on CustomerID)
- 2. Archive customers data (using table partition if possible) (based on CreatedDate)
I have a very clear idea on how to do either 1 or 2, but not both. Is that even possible to partition on top of partition? If not, what's the best way to approach this?
Saturday, January 19, 2013 5:47 PM
So you want to partition the same table on two different criterias?
Since you can only have a single partitioning column, you would need to define a computed column that holds both piece of information. It goes without saying that this is not very practical.
Then again, say that you want four different filegroups, and you need to active partitions for time, so that is only 8 active partitions. When you want to archive data, you switch out four partitions and add four new ones. Could be doable.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
Sunday, January 20, 2013 12:56 AMModerator
Is this homework?
For 2, consider:
Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
Sunday, January 20, 2013 6:20 PMYou can switch partitions if your idea allows this or TRANSFER the partition to other. Please follow the following URL for more details.
- Marked As Answer by Iric WenModerator Monday, January 28, 2013 6:05 AM