I know everybody want that and I wanted to write these queries long time ago. So, it is the time now.
At first, we will establish Partitioning Case.
First, will create test database with additional partitions and files.
By that code we create new Database "TestPartitions" with two files in PRIMARY file group. Besides of "Primary" we created two groups with one file each and third group with two files.
Here is how result of that creation looks in SSMS:
To automate the process will write a query:
Here we can see not only how File Groups and Files were setup, but also some operational information
like: File_id, File Physical Name, File State, Average Read/Write File statistics.
Table PartitioningFor Table Partitioning we have to create Partitioning Functions and Partitioning Schemas.
In this example I will create Functions' Partitioning by DateTime field.
I've created two functions with "Left" and "Right" side boundaries and with three ranges:
Range 1: Before 1/1/2014
Range 2: Between 1/1/2014 and 1/1/2016
Range 3: After 1/1/2016
As you can see, in the second partitioning schema, just for sake of experiment, I've purposely mixed File Groups in reverse order.
Here is what we can see in SSMS for Functions and Schemas we've just created:
As the result we can see following:
1. Function to Schema relationships;
2. Function Range Type;
3. Bordering values;
4. Associated File Groups and Partitions;
5. Space usage statistics per File/Partition.
Create Partitioned TablesWe will create two tables. One in each schema and populate them with the similar data:
Following insertion was performed:
1. 15 records with before 1/1/2014 - are supposed to go to Range 1;
2. 20+10 records between 1/1/2014 and 1/1/2016 - are supposed to go to Range 2;
3. 5 records with after 1/1/2016 - are supposed to go to Range 3;
In order to see records' allocations we can run following query:
Unfortunately, because we use more than one file per File Group, our Row and Page Counts are aggregated by a Group and it might bring some inconvenience because of the duplication.
Left and Right RangesI did not stop on that before because I want demonstrate it with an example. Jut do couple of more inserts and then run the previous query.
That is their difference.
Splitting the RangeUsually new Partition Ranges are added to the end of a Function/Schema, but in my example I'll demonstrate how to split middle Partition.
At first we create new File Group. Then make it next within a schema and then split a Function:
I hope you liked this post and if you know any other interesting way to play with partitions please let me know.