MS SQL Partitioned Tables

One of our tables was quite large and was performing poorly and was difficult to manage.  If the users wanted the table imported into another database, SSIS & DTS would choke and we ran into locking and blocking issues if the table was being read while being written to.  We decided to try partitioning the table to see if we could get better results.  We were able to copy over partitions when needed and performance when writing and reading to and from the partition view improved.   

Here are sample scripts for partitioning:

CREATE TABLE table_2010
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not null CONSTRAINT PK_table_2003
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_2010_mailing_date
CHECK (mailing_date <= '2010-12-31'))

CREATE TABLE table_201101
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not null CONSTRAINT PK_table_201101
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_201101_mailing_date
CHECK (mailing_date between '2011-01-01' and '2011-01-31'))

CREATE TABLE table_201102
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not nullCONSTRAINT PK_table_201102
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_201102_mailing_date
CHECK (mailing_date between '2011-02-01' and '2011-02-28'))

CREATE TABLE table_201103
(Field1 char(12) NOT NULL,Field2 char(9) not null,mailing_date char(10) not nullCONSTRAINT PK_table_201103
PRIMARY KEY(Field1,Field2,mailing_date
),CONSTRAINT CHK_table_201103_mailing_date
CHECK (mailing_date between '2011-03-01' and '2011-03-31'))

CREATE partitioned_vw as
Select Field1, Field2, mailing_date
from table_2010
union all select Field1, Field2, mailing_date
from table_201101
union all select Field1, Field2, mailing_date
from table_201102
union all select Field1, Field2, mailing_date
from table_201103

We never implemented swapping out partitions.  If I implement this in the future, I will add another blog entry to go over that.