By: Nitin Rana | Updated: 2008-09-10 | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Partitioning
Problem
SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL also suggests that while SWITCHING partitions, you should set up a NON PARTITIONED TABLE in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming. However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.
Solution
This solution talks about how to manage old partitions without creating a NON PARTITIONED table in each filegroup. If you are not dealing with multiple filegroups, you can refer to this other tip Switching data in and out of a SQL Server 2005 data partition since that tip talks very clearly about how to manage partitions if you have only one filegroup such as the PRIMARY filegroup..
Considering, in a real heavy loaded environment, you might have multiple filegroups within a partition scheme, it is not feasible to create NON partitioned table on every filegroup for the cleanup purpose. This tip shows how you can switch multiple partitions by creating another partitioned table using the same Partition Scheme as the original table.
Step 1
To start with, let's create a sample database which contains
several filegroups for our demo purposes.
--Create the Database
CREATE DATABASE
MSSQLTIPS;
--Now Create Five Filegroups
ALTER DATABASE
MSSQLTIPS
ADD filegroup
FGDayRange01;
ALTER DATABASE
MSSQLTIPS
ADD filegroup
FGDayRange02;
ALTER DATABASE
MSSQLTIPS
ADD filegroup
FGDayRange03;
ALTER DATABASE
MSSQLTIPS
ADD filegroup
FGDayRange04;
ALTER DATABASE
MSSQLTIPS
ADD filegroup
FGDayRange05;
Step 2
Now let's create the files for each filegroup.
--Now Add One File to Each Filegroup
ALTER DATABASE
MSSQLTIPS
ADD FILE
(
NAME
=
FGDayRange_01,
FILENAME
= 'C:\FGDayRange_01.NDF',
SIZE
= 5MB,
FILEGROWTH
= 1MB
)
TO FILEGROUP
FGDayRange01;
ALTER DATABASE
MSSQLTIPS
ADD FILE
(
NAME
=
FGDayRange_02,
FILENAME
= 'C:\FGDayRange_02.NDF',
SIZE
= 5MB,
FILEGROWTH
= 1MB
)
TO FILEGROUP
FGDayRange02;
ALTER DATABASE
MSSQLTIPS
ADD FILE
(
NAME
=
FGDayRange_03,
FILENAME
= 'C:\FGDayRange_03.NDF',
SIZE
= 5MB,
FILEGROWTH
= 1MB
)
TO FILEGROUP
FGDayRange03;
ALTER DATABASE
MSSQLTIPS
ADD FILE
(
NAME
=
FGDayRange_04,
FILENAME
= 'C:\FGDayRange_04.NDF',
SIZE
= 5MB,
FILEGROWTH
= 1MB
)
TO FILEGROUP
FGDayRange04;
ALTER DATABASE
MSSQLTIPS
ADD FILE
(
NAME
=
FGDayRange_05,
FILENAME
= 'C:\FGDayRange_05.NDF',
SIZE
= 5MB,
FILEGROWTH
= 1MB
)
TO FILEGROUP
FGDayRange05;
USE mssqltips
GO
Step 3
We then create our partition function.
CREATE Partition FUNCTION pfFiveDayRange (Datetime)
AS RANGE RIGHT FOR VALUES
('2008-09-01', '2008-09-02', '2008-09-03', '2008-09-04', '2008-09-05' )
GO
Step 4
Then we create our partition scheme.
--Now create a Partition Scheme to Hold Five
Filegroups
--These Five Filegroups will hold the data for the five
dates defined in Partition Function.
CREATE PARTITION
SCHEME psFiveDayRange
AS
PARTITION pfFiveDayRange
TO (
[PRIMARY],
[FGDayRange01],
[FGDayRange02],
[FGDayRange03],
[FGDayRange04],
[FGDayRange05]
);
Step 5
We create a new partitioned table.
--Now create a Partitioned Table.
CREATE TABLE
MSSQLTIPS_Partitions
(ID
INT
IDENTITY,
businessDate DATETIME
NOT NULL)
ON psFiveDayRange
(businessDate);
Step 6
We load some sample data.
SET NOCOUNT
ON
--Load 50 records with each date
INSERT INTO
MSSQLTIPS_Partitions
(businessDate)
VALUES
('2008-09-01')
GO 50
INSERT INTO
MSSQLTIPS_Partitions
(businessDate)
VALUES
('2008-09-02')
GO 50
INSERT INTO
MSSQLTIPS_Partitions
(businessDate)
VALUES
('2008-09-03')
GO 50
INSERT INTO
MSSQLTIPS_Partitions
(businessDate)
VALUES
('2008-09-04')
GO 50
INSERT INTO
MSSQLTIPS_Partitions
(businessDate)
VALUES
('2008-09-05')
GO 50
Step 7
Here we check to see how the data was loaded
and how many rows are in each partition.
-- By Now you have 250 records (50 Each Date)
sitting on each Filegroup.
-- Let's check this out.
SELECT
COUNT(*)
AS
[Total Records],
$Partition.pfFiveDayRange(businessDate)
AS
[Partition Number]
FROM MSSQLTIPS_Partitions
GROUP BY
$Partition.pfFiveDayRange(businessDate);
Step 8
Now you want to clean up the partitions for dates 2008-09-01,
2008-09-02, and 2008-09-03. The traditional way to do so is by creating three NON
PARTITIONED tables in each filegroup FGDayRange01, FGDayRange02, FGDayRange03 and
switching each partition to the corresponding NON PARTITIONED table. Now, you can
forget about doing all that and do it all in one step by creating another PARTITIONED
TABLE using the same partition scheme as the original table.
--Note that the DDL for the cleanup table
must be exactly same as the original table.
CREATE TABLE
MSSQLTIPS_Partitions_Cleanup
(ID
INT
IDENTITY,
businessDate DATETIME
NOT NULL)
ON psFiveDayRange
(businessDate);
Step 9
Now you can switch all partitions within one table since the
partition scheme takes care of the physical layout of the structure.
ALTER TABLE
MSSQLTIPS_Partitions
SWITCH PARTITION
$PARTITION.pfFiveDayRange('2008-09-01')
TO
MSSQLTIPS_Partitions_Cleanup
PARTITION
$PARTITION.pfFiveDayRange('2008-09-01')
ALTER TABLE
MSSQLTIPS_Partitions
SWITCH PARTITION
$PARTITION.pfFiveDayRange('2008-09-02')
TO
MSSQLTIPS_Partitions_Cleanup
PARTITION
$PARTITION.pfFiveDayRange('2008-09-02')
ALTER TABLE
MSSQLTIPS_Partitions
SWITCH PARTITION
$PARTITION.pfFiveDayRange('2008-09-03')
TO
MSSQLTIPS_Partitions_Cleanup
PARTITION
$PARTITION.pfFiveDayRange('2008-09-03')
Step 10
Run a count to see that data has been switched to the clean
up table.
SELECT
COUNT(*)
FROM
MSSQLTIPS_Partitions
-- Should return 100 Records
SELECT
COUNT(*)
FROM
MSSQLTIPS_Partitions_Cleanup
-- Should Return 150 Records
-- See
Partition Details
SELECT
COUNT(*)
AS
[Total Records],
$Partition.pfFiveDayRange(businessDate)
AS
[Partition Number]
FROM MSSQLTIPS_Partitions
GROUP BY
$Partition.pfFiveDayRange(businessDate);
SELECT
COUNT(*)
AS
[Total Records],
$Partition.pfFiveDayRange(businessDate)
AS
[Partition Number]
FROM MSSQLTIPS_Partitions_Cleanup
GROUP BY
$Partition.pfFiveDayRange(businessDate);
Step 11
Now the three dates from the original table have been switched
to the cleanup table. Now you can simply truncate the cleanup table.
TRUNCATE TABLE
MSSQLTIPS_Partitions_Cleanup
Now you are all done. You can merge partitions in the original table or reload the table with the correct data or do anything else you wish. I hope you all enjoyed the idea of not creating multiple non-partitioned tables to hold the partitions for switch command.
Next Steps
- You can read more about Partitioned Tables and Indexes by reading this white paper SQL Server 2005 Partitioned Tables and Indexes
- You can go to SQL Server BOL and read more on the following topics
- $Partition Function
- CREATE PARTITION SCHEME
- CREATE PARTITION FUNCTION
- FILEGROUP
- Sys.Partition% system table/views
- Sys.destination_data_spaces
About the author
Nitin Rana is a Microsoft Certified Professional and currently has over 12 years of IT experience with the SQL server core components.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2008-09-10
