I created the partition function as follows:
create partition function pf_month1 (varchar (8))
As range left for values (' 20120131 ', ' 20120229 ', ' 20120331 ', ' 20120430 ', ' 20120531 ', ' 20120630 ', ' 20120731 ', ' 20120831 ', ' 20120930 ', ' 20121031 ', ' 20121130 ')
But there is a problem, how to deal with the cross-year?
If 2013 years, all the data will be placed in the last partition, how to deal with it? Is there a better partition function that is partitioned by year-by-month?
------Solution--------------------
With Alter PARTITION Function/scheme
Reference
Http://msdn.microsoft.com/zh-cn/library/ms186307.aspx
Http://msdn.microsoft.com/zh-cn/library/ms190347.aspx
------Solution--------------------
1. It is better to partition the border with the number 1th per month, so as not to judge the number of months.
2. Partitions are used to speed up queries, but each partition is preferably a separate physical disk that is placed on the same physical disc and does not add much performance.
3. If the monthly partition indicates a very large amount of data per month, then modify the partition function after one months, instead of dividing all the partitions at once.
4. If the amount of data used in the previous months is not large after one months, you can merge it so as long as you re-divide the partition.
------Solution--------------------
Assume that the current partition boundary is 100 200 300 400
That is
<100 200~300 300~400 >400
Add a partition where the boundary is 500
SQL Code
--Specify the next partition to apply the filegroup. (eg: PRIMARY) Alter PARTITION SCHEME [Customer_scheme] --partition architecture Next used [PRIMARY]--Specify partition demarcation point for alter PARTITION FUNCTION Customer_ Partfunc () --partition function split RANGE (500)
How SQL Server creates partition functions on a yearly monthly basis