partitioned table

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.