October 30, 2007 at 10:53 am
I have had a 2005 up and running box placed in my lap, and not having played with partitions before I am a little confused about a couple of things, possibly someone can shed some insight...
/****** Object: PartitionFunction [CDRDateRangePFN] ******/
CREATE PARTITION FUNCTION [CDRDateRangePFN](smalldatetime) AS RANGE RIGHT FOR VALUES (N'2007-08-01T00:00:00', N'2007-09-01T00:00:00', N'2007-10-01T00:00:00')
/****** Object: PartitionScheme [CDRDatePScheme] ******/
CREATE PARTITION SCHEME [CDRDatePScheme] AS PARTITION [CDRDateRangePFN] TO ([NationalLD_GeneralGroup], [NationalLD_GeneralGroup], [NationalLD_GeneralGroup], [NationalLD_GeneralGroup])
There are four datafiles in the NationalLD_GeneralGroup.
The way that I see it everything for this table will be put in the partition, starting with data newer than 08-01 going in the first file, newer than 09-01 in the second, 10-01 in the third, and newer than that in the fourth.
Is my belief in this accurate?
Given that I am reaching a new month, want to be rid of the old stuff from before 09-01, would I update the partition function, and that will get rid of the data automatically, or do I need to perform a delete?
One the data is gone, will the partitions automatically roll around or is that something I need to manually do with the datafiles?
Thanks.
October 30, 2007 at 12:08 pm
First, partitions are open-ended. When using RANGE RIGHT, the first partition holds all data less than the first value listed. In this case, the first partition is for all data prior to midnight, August 1. August data goes in partition #2, September data to partition #3, October 1 forward to partition #4.
As to the actual data storage, the partitions are, behind the scenes, effectively separate tables.
The files in a filegroup are proportionately filled with table data based on the free space in them. You do not direct storage at a specific file, only a filegroup.
Because every partition is directed to the same filegroup in your partition scheme, all data from every partition will be spread evenly among the four files. Partition schemes direct data to filegroups, not files, just like all other SQL Server storage. If you want the data to go to physically different files, then you need to create separate filegroups for those files, and direct the slices of data to the specific filegroups.
As for maintaining the data and partitions as time moves forward, you alter the partition function itself using MERGE RANGE and SPLIT RANGE. Data is not 'dropped' from a partition by modifying it (again, partitions are open-ended). You'll have to clean it out yourself. I could type it up here, but there's plenty of info in BOL: look for "sliding window partitioning scenario".
It also covers how to easily move all the data in a partition to an empty partition in an archive table using ALTER TABLE table SWITCH PARTITION # TO other table PARTITION #, which is pretty cool. To delete a partition of data, you can switch the partition out of the table into an empty table, then truncate the target table.
-Eddie
Eddie Wuerch
MCM: SQL
October 30, 2007 at 1:28 pm
So this is effectively a useless partition, correct?
The data would have been spread throughout the filegroup anyway, this just added complexity. Realistically I should have four different filegroups and push the data within those.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply