Partition Columns in a Primary Key

Dinesh Karunarathne has started a new blog and first post is on Partitions Views.

He finished with his post with,

If the partitioned column is part of the PRIMARY KEY, data can directly be inserted in to the view.

I want to elaborate on this point further with a practical example I had.

I had a Order table as I came up with a design to partition order table with Date. This means I have say, three order tables, Orders_2010, Orders_2011 and Order_2012.

So the tables are created and you will notice that you have CLUESTERED PRIMARY KEY for OrderID,OrderDate columns. Traditionally, you would create CLUSTERED PRIMARY KEY only on OrderID.

Next is creating the partition constraints as shown below.

If you use DATEPART function instead of BETWEEN, this will not be possible to use as a partition view.

Each base table has a partitioning column whose key values are enforced by CHECK constraints.

The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

Next is creating the View.

If you create PRIMARY KEY only on OrderID, you will not be able to insert into the view and you will get the following error.

Msg 4436, Level 16, State 12, Line 2
UNION ALL view 'LogicalPartitioning.dbo.vw_Orders' is not updatable because a partitioning column was not found.

Also, you cannot have IDENTITY property for OrderID. If you use it, you will get the following error.

Msg 4433, Level 16, State 4, Line 2
Cannot INSERT into partitioned view 'LogicalPartitioning.dbo.vw_Orders' because table '[LogicalPartitioning].[dbo].[Orders_2010]' has an IDENTITY constraint.