Partition on Value

Question

I can see lot of documentation on Range Partitioning. Is there any other type of partition supported in SQL Server 2005?

For example, I have a Fact table having Billion rows. It has a column called BATCH_ID. A BATCH_ID corresponds to 10-20 Million rows and it is a running sequence number like 1,2,3 etc. (not an identity column). Is there anyway I can specify a partition function with BATCH_ID column as an int value? Will the SQL Server automatically does the partition on each int value in that case? If not, what is the best way to do it?

Thanks in advance for all help in this

 

 

 

Answer 1

There is no automatic way to perform the partitioning. You will have to define the valid ranges yourself in the partition function. You can modify say the code that generates the batch id to add the new partitions and then perform the insert into the table. Another option is to use a hash based partition approach (using CHECKSUM and modulo) to split the table into fixed number of buckets. This might be a feasible option for you if you do not want to change the code that generates the batch id. It will however require changes to the schema, addition of computed column in the table and so on.

Answer 2

Is it possible to create ranges in the partition function that specify values that do not yet exist in the partitioning column, if you know that you will be adding those values later?  E.g., if I am adding 10 million rows each month, and I want each month's data to go into a new partition, and I have a column which increments by 1 each month, can I go ahead and specify the ranges for future months in the partitioning function, and will they work properly once the new data is added, so that the new data for each month will be added to a new partition?

Thanks for your help.