ve been looking into table partitioning as a way to manage large historical tables. The idea is to be able to keep the size of the table reasonably static as new rows are inserted by removing older rows. My goal is to avoid unnecessary locking. Typically I might write a stored procedure that does something like this:

1. insert new data into table
2. remove rows older than

The problem here is that the table is very large and very busy (there are large quantities of inserts at certain times of day) and I’m afraid that the DELETE might block or slow down inserts, especially as I have a very limited ability to change indexes on this table (IE: to change fillfactor or to change which columns are indexed).

I’ve been looking into table partitioning. It seems designed to address this specific issue. From what I am seeing, there are two approaches to table partitioning.

If you have sql server enterprise or developer, then there is a table partitioning mechanism that can divide a single table up across multiple filegroups. This method uses a partitioning function to determine to which partition a given row should belong. All management of the table’s partitions is invisible to applications that access the table, and it is possible to swap out data on a particular filegroup for quick loads or deletes.

If you DO NOT have sql server enterprise or developer (my use case), then you can use a partitioned view.

The partitioned view is composed of manually created tables. Each table must have the same columns. One of the columns must be designated as the partition key. This column must be included in the primary key, and this column must have a check constraint specifying which data should go in this table. (IE: if the column is “quarter”, there might be four tables and the check constraint on the “quarter” column for each table might validate that only data for a specific quarter be inserted into that table).

CREATE VIEW DBO.QUARTER_VIEW
WITH SCHEMABINDING AS
select 
   a.id,
   a.qtr,
   a.value
from
   dbo.QUARTER_01 a

UNION ALL

select 
   a.id,
   a.qtr,
   a.value
from
   dbo.QUARTER_02 a

UNION ALL

select 
   a.id,
   a.qtr,
   a.value
from
   dbo.QUARTER_03 a

UNION ALL

select 
   a.id,
   a.qtr,
   a.value
from 
   dbo.QUARTER_04 a

Once the partition key column has been identified and the tables created, a view is created that uses “UNION ALL” to combine selects from the composite tables. For example:

CREATE VIEW DBO.QUARTER_VIEW
WITH SCHEMABINDING AS
select 
   a.qtr,
   a.value
from
   QUARTER_01

UNION ALL

select 
   a.qtr,
   a.value
from
   QUARTER_02

UNION ALL

select 
   a.qtr,
   a.value
from
   QUARTER_03

UNION ALL

select 
   a.qtr,
   a.value
from
   QUARTER_04

Using the partitioned view is easy then.

insert into DBO.QUARTER_VIEW(id,qtr,value) values (5,2,3.5)

If you then look at the base tables, you see that only QUARTER_02 contains a row.

select * from dbo.QUARTER_01
select * from dbo.QUARTER_02
select * from dbo.QUARTER_03
select * from dbo.QUARTER_04