Create a parition function with the boundary values. In this example there will be 4 paritions [datetime<20110101], [20110101<=datetime<20120101],[20120101<=datetime<20130101] and [20130101<=datetime]
CREATE PARTITION FUNCTION TestPartitionFunction (datetime)
AS RANGE right FOR VALUES ('20110101', '20120101', '20130101') ;
GO
Also need a partition scheme to map partitions to file groups. Here All partitions map to the Primary file group
CREATE PARTITION SCHEME TestPartitionScheme
AS PARTITION TestPartitionFunction
ALL TO ([PRIMARY]) ;
GO
Create a paritioned table based on the partition schme
CREATE TABLE dbo.TestParitionTable (InsertDT datetime, Data varchar(100))
ON TestPartitionScheme (InsertDT) ;
GO
Insert test data
declare @i int = 0;
while (@i< 1000)
begin
insert into TEST.dbo.TestParitionTable
values(DATEADD(day, @i, '20100101'), 'HELLO '+CONVERT(varchar(10),@i))
set @i = @i + 1;
end
Check partitions
SELECT obj.name, p.*
from sys.all_objects obj
join sys.partitions p
on obj.object_id = p.object_id
where obj.name ='TestParitionTable'
There are four paritions:
name partition_number rows
TestParitionTable 1 365
TestParitionTable 2 365
TestParitionTable 3 270
TestParitionTable 4 0
There is no sql statement to truncate a partition but you can "switch" in/out partition data.
To switch out a partition, create a temp table AnotherTest with the same table schema and switch the partition to the temp table.
create table AnotherTest(InsertDT datetime, Data varchar(100));
ALTER TABLE TestParitionTable SWITCH PARTITION 3 TO AnotherTest ;
This actually assigns the reference to Partition 3 to the temp table, so if select from the TestParitionTable the partition 3 data will be all gone. If select from AnotherTest then the partition 3 data is all there.
To physically truncate partition 3, run
truncate table AnotherTest;
To switch data from a temp table into a partition
create table Test.dbo.AnotherTest(InsertDT datetime
CHECK (InsertDT >='20130101' and InsertDT is not null)
, Data varchar(100));
ALTER TABLE AnotherTest SWITCH TO TestParitionTable PARTITION 4;
Make sure the Check constraint is in the temp table's definition, otherwise the Switch in operation will fail as it needs to make sure both the temp table and the partition have the same range of data.
Note, the Check constraint comes with WITH CHECK option on first time of creation by default. However, if SSIS doesn't "CHECK CONSTRAINT" in destination component, it will reset the constraint to WITH NO CHECK. This will fail the SWITCH operation due to the NOCHECK option.
ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL enforces WITH CHECK.