August 21, 2007 at 10:00 am
Hello,
I like to create a partitioned table that has its partitions based on States. All the partition functions examples I've seen do some type of numeric range (like salesdate). Is there are a way to set it up by State?
Thanks a bunch
August 21, 2007 at 12:10 pm
Just for curiosity, I checked Books Online and found this
The following partition function partitions a table or index into four partitions.
CREATE PARTITION FUNCTION myRangePF3 (char(20))AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR'); | |
The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.
| Partition | 1 | 2 | 3 | 4 |
|---|---|---|---|---|
Values | col1 < | col1 >= | col1 >= | col1 >= |
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/9dfe8b76-721e-42fd-81ae-14e22258c4f2.htm
N 56°04'39.16"
E 12°55'05.25"
August 21, 2007 at 2:06 pm
Thanks for your time Peter. I believe though that this still represents a range col1 would be A? to EX not just EX.
I would like to classify the States in the proper partition. Maybe I am not fully comprehending the concepts here.
August 21, 2007 at 2:28 pm
Blimey!
Since there are only 50 states and 1 district in the USA, it can't be that hard to set the ranges accordingly...
CREATE PARTITION FUNCTION States50_1
(VARCHAR(20)) AS RANGE LEFT FOR VALUES ('Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'Delaware', 'District of Columbia', 'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah,' 'Vermont', 'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming');
N 56°04'39.16"
E 12°55'05.25"
August 21, 2007 at 10:40 pm
Your Partition Function must map the entire domain of values. If it is a two character field, you can setup boundaries to separate the partitions. You will need to add additional constraints to limit your states to real "abbreviations".
With that said, now comes the tricky part. Your CLustered Index is the partitioning Key. Why is this tricky? Because if you want to have a unique constraint (perhaps customer number), you will need to protect it with an index. By deafult, the index inherits the same partiioning scheme as the base table to make it aligned (this is normally a good thing). BUT, you may not have a unique index that is "aligned" unless it is a subset of the partitioning key. I have a script that I have used for a presentation that is available at http://www.ricksql.com that may help you understand this limitation.
Hope this helps!
Rick...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply