http://msdn.microsoft.com/en-us/library/aa964122%28SQL.90%29.aspx create partition function pfDaily (datetime) as RANGE RIGHT for values( '2005-05-07', '2005-05-08', '2005-05-09', '2005-05-10', '2005-05-11', '2005-05-12', '2005-05-13', '2005-05-14', '2005-05-15', '2005-05-16', '2005-05-17', '2005-05-18', '2005-05-19', '2005-05-20', '2005-05-21', '2005-05-22', '2005-05-23', '2005-05-24', '2005-05-25', '2005-05-26', '2005-05-27', '2005-05-28', '2005-05-29', '2005-05-30', '2005-05-31', '2005-06-01', '2005-06-02', '2005-06-03', '2005-06-04', '2005-06-05', '2005-06-06', '2005-06-07', '2005-06-08', '2005-06-09', '2005-06-10', '2005-06-11', '2005-06-12', '2005-06-13', '2005-06-14', '2005-06-15', '2005-06-16', '2005-06-17', '2005-06-18', '2005-06-19', '2005-06-20', '2005-06-21', '2005-06-22', '2005-06-23', '2005-06-24', '2005-06-25', '2005-06-26', '2005-06-27', '2005-06-28', '2005-06-29', '2005-06-30', '2005-07-01', '2005-07-02', '2005-07-03', '2005-07-04') go-- This Partition MUST be on Left side, so the data MUST be the -- day before of the first day.create partition function pfDaily_Aux (datetime) as RANGE RIGHT for values( '2005-05-07', '2005-05-08') go--Both partitions will be placed at the same FileGroup since the --system is planned to run on SAN disk. CREATE PARTITION SCHEME pfDaily as partition pfDaily all to ([primary]) goCREATE PARTITION SCHEME pfDaily_Aux as partition pfDaily_Aux all to ([primary]) goCREATE TABLE [dbo].[CDR] ( [ID_CDR] [int] NOT NULL , [Route] [int] NULL , [Direction] [tinyint] NULL , [IAM_Date] [datetime] NOT NULL , [ACM_Date] [datetime] NULL , [ANM_Date] [datetime] NULL , [REL_Date] [datetime] NULL , [RLC_Date] [datetime] NULL , [End_Date] [datetime] NOT NULL ) on pfDaily ([End_Date]) GOCREATE CLUSTERED INDEX [IX_End_Date] ON [dbo].[CDR]([End_Date]) ON pfDaily ([End_Date]) GOALTER TABLE [dbo].[CDR] WITH NOCHECK ADD CONSTRAINT [PK_CDR] PRIMARY KEY NONCLUSTERED ( [Id_CDR], [End_Date] ) on pfDaily ([End_Date]) GOCREATE TABLE [dbo].[CDR_AUX] ( [ID_CDR] [int] NOT NULL , [Route] [int] NULL , [Direction] [tinyint] NULL , [IAM_Date] [datetime] NOT NULL , [ACM_Date] [datetime] NULL , [ANM_Date] [datetime] NULL , [REL_Date] [datetime] NULL , [RLC_Date] [datetime] NULL , [End_Date] [datetime] NOT NULL ) on pfDaily_Aux ([End_Date]) GOCREATE CLUSTERED INDEX [IX_End_Date] ON [dbo].[CDR_AUX]([End_Date]) ON pfDaily_aux ([End_Date]) GOALTER TABLE [dbo].[CDR_AUX] WITH NOCHECK ADD CONSTRAINT [PK_CDR_AUX] PRIMARY KEY NONCLUSTERED ( [Id_CDR], [End_Date] ) on pfDaily_aux ([End_Date]) --************************************************************************************** -- -- Summary: Managing a Range Partitioned Table -- Delete data on 2nd most left partition. -- It means that the most left partition will always stay there -- to guarantee the size of the second one. This one will be -- moved. The most left partition will be empty. -- --**************************************************************************************USE AdventureWorks; GOCREATE PROCEDURE PRC_DEL_PARTITION_LEFT_ON_CDR ASALTER PARTITION SCHEME pfDaily NEXT USED [PRIMARY];ALTER PARTITION SCHEME pfDaily_Aux NEXT USED [PRIMARY];DECLARE @Day datetime DECLARE @Day_Next2 datetime DECLARE @Scratch varchar(2000)SET @Day = cast((select top 1 [value] from sys.partition_range_values where function_id = (select function_id from sys.partition_functions where name = 'pfDaily') order by boundary_id) as datetime)SET @Day_Next2 = DATEADD(DAY, 2, @Day)-- STEP 1 -- Add a new partition to table CDR_Aux to hold the -- Data from 2nd Left Partition of CDR.ALTER PARTITION FUNCTION pfDaily_Aux() SPLIT RANGE (@Day_Next2); -- STEP 2 -- Move the data for 2nd FAR LEFT Partition from table CDR to -- table CDR_AUX.ALTER TABLE CDR SWITCH PARTITION 2 TO CDR_AUX PARTITION 2;-- STEP 3 -- Merge the 1st and 2nd partitions of table CDR.ALTER PARTITION FUNCTION pfDaily() MERGE RANGE (@Day);-- STEP 4 -- Merge the partition of table CDR_AUX -- with the first partition.ALTER PARTITION FUNCTION pfDaily_Aux() MERGE RANGE (@Day);-- delete the data on CDR_AUX TRUNCATE TABLE CDR_AUXGO
create partition function pfDaily (datetime)
as RANGE RIGHT for values(
'2005-05-07', '2005-05-08', '2005-05-09', '2005-05-10', '2005-05-11', '2005-05-12', '2005-05-13', '2005-05-14',
'2005-05-15', '2005-05-16', '2005-05-17', '2005-05-18', '2005-05-19', '2005-05-20', '2005-05-21', '2005-05-22',
'2005-05-23', '2005-05-24', '2005-05-25', '2005-05-26', '2005-05-27', '2005-05-28', '2005-05-29', '2005-05-30',
'2005-05-31', '2005-06-01', '2005-06-02', '2005-06-03', '2005-06-04', '2005-06-05', '2005-06-06', '2005-06-07',
'2005-06-08', '2005-06-09', '2005-06-10', '2005-06-11', '2005-06-12', '2005-06-13', '2005-06-14', '2005-06-15',
'2005-06-16', '2005-06-17', '2005-06-18', '2005-06-19', '2005-06-20', '2005-06-21', '2005-06-22', '2005-06-23',
'2005-06-24', '2005-06-25', '2005-06-26', '2005-06-27', '2005-06-28', '2005-06-29', '2005-06-30', '2005-07-01',
'2005-07-02', '2005-07-03', '2005-07-04')
go-- This Partition MUST be on Left side, so the data MUST be the
-- day before of the first day.create partition function pfDaily_Aux (datetime)
as RANGE RIGHT for values(
'2005-05-07',
'2005-05-08')
go--Both partitions will be placed at the same FileGroup since the
--system is planned to run on SAN disk. CREATE PARTITION SCHEME pfDaily as partition pfDaily all
to ([primary])
goCREATE PARTITION SCHEME pfDaily_Aux as partition pfDaily_Aux all
to ([primary])
goCREATE TABLE [dbo].[CDR] (
[ID_CDR] [int] NOT NULL ,
[Route] [int] NULL ,
[Direction] [tinyint] NULL ,
[IAM_Date] [datetime] NOT NULL ,
[ACM_Date] [datetime] NULL ,
[ANM_Date] [datetime] NULL ,
[REL_Date] [datetime] NULL ,
[RLC_Date] [datetime] NULL ,
[End_Date] [datetime] NOT NULL
) on pfDaily ([End_Date])
GOCREATE CLUSTERED INDEX [IX_End_Date]
ON [dbo].[CDR]([End_Date]) ON pfDaily ([End_Date])
GOALTER TABLE [dbo].[CDR] WITH NOCHECK ADD
CONSTRAINT [PK_CDR] PRIMARY KEY NONCLUSTERED
(
[Id_CDR],
[End_Date]
) on pfDaily ([End_Date])
GOCREATE TABLE [dbo].[CDR_AUX] (
[ID_CDR] [int] NOT NULL ,
[Route] [int] NULL ,
[Direction] [tinyint] NULL ,
[IAM_Date] [datetime] NOT NULL ,
[ACM_Date] [datetime] NULL ,
[ANM_Date] [datetime] NULL ,
[REL_Date] [datetime] NULL ,
[RLC_Date] [datetime] NULL ,
[End_Date] [datetime] NOT NULL
) on pfDaily_Aux ([End_Date])
GOCREATE CLUSTERED INDEX [IX_End_Date]
ON [dbo].[CDR_AUX]([End_Date]) ON pfDaily_aux ([End_Date])
GOALTER TABLE [dbo].[CDR_AUX] WITH NOCHECK ADD
CONSTRAINT [PK_CDR_AUX] PRIMARY KEY NONCLUSTERED
(
[Id_CDR],
[End_Date]
) on pfDaily_aux ([End_Date])
--**************************************************************************************
--
-- Summary: Managing a Range Partitioned Table
-- Delete data on 2nd most left partition.
-- It means that the most left partition will always stay there
-- to guarantee the size of the second one. This one will be
-- moved. The most left partition will be empty.
--
--**************************************************************************************USE AdventureWorks;
GOCREATE PROCEDURE PRC_DEL_PARTITION_LEFT_ON_CDR
ASALTER PARTITION SCHEME pfDaily
NEXT USED [PRIMARY];ALTER PARTITION SCHEME pfDaily_Aux
NEXT USED [PRIMARY];DECLARE @Day datetime
DECLARE @Day_Next2 datetime
DECLARE @Scratch varchar(2000)SET @Day = cast((select top 1 [value] from sys.partition_range_values
where function_id = (select function_id
from sys.partition_functions
where name = 'pfDaily')
order by boundary_id) as datetime)SET @Day_Next2 = DATEADD(DAY, 2, @Day)-- STEP 1
-- Add a new partition to table CDR_Aux to hold the
-- Data from 2nd Left Partition of CDR.ALTER PARTITION FUNCTION pfDaily_Aux()
SPLIT RANGE (@Day_Next2);
-- STEP 2
-- Move the data for 2nd FAR LEFT Partition from table CDR to
-- table CDR_AUX.ALTER TABLE CDR
SWITCH PARTITION 2
TO CDR_AUX PARTITION 2;-- STEP 3
-- Merge the 1st and 2nd partitions of table CDR.ALTER PARTITION FUNCTION pfDaily()
MERGE RANGE (@Day);-- STEP 4
-- Merge the partition of table CDR_AUX
-- with the first partition.ALTER PARTITION FUNCTION pfDaily_Aux()
MERGE RANGE (@Day);-- delete the data on CDR_AUX
TRUNCATE TABLE CDR_AUXGO