March 21, 2007

Partitioned views: CHECK constraints with NOT FOR REPLICATION clause

I am posting this entry to remind myself about what happens when you have the NOT FOR REPLICATION clause with a check constraint on a column in a table.

I was going through how data partitioning can be achieved in SQL server 2000/2005. As mentioned in the one the various articles on the internet, the simple way is to create identical tables with the same primary key columns and have a view on top that includes each of the tables with a UNION clause. However that is not the scope of this post.

The scope of this post is the impact on queries on partitioned views by having a check constraint in the underlying partitioned tables and that the check constraints are created with a NOT FOR REPLICATION clause. I have a standalone database without any replication involved. I would do the following for example:

Note the NOT FOR REPLICATION clause in the check constraints on the TableID column.

Creating a view on top of that
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO

Now use the following query to see the query execution plan:

select * from TableView where TableID = 10

In SQL server 2000 the query execution would look like this:

In SQL server 2005 the execution plan would look like this:

In both cases you would notice that there is an index scan on both the tables that are part of the view. What would happen if there was no primary key? It would have done a table scan on all the tables. In this example there are only two tables. What would be the performance hit if there are many more tables with huge amount of data in it? The last thing you would want is a table scan on all tables to retrieve just one row.

Now drop the tables and the view and create them again, now without the NOT FOR REPLICATION clause in the check constraint.

The difference this time is that it has done an index seek only on the partition where the data would be present. Since the check constraint on Table_1 allows values for TableID column only in the range of 1 and 999, it picks up data only from that partition table and does not bother looking for it in the other tables in the view.

So, why does the NOT FOR REPLICATION clause on the check constraint reduce the performance of the query so drastically?

Do not use the NOT FOR REPLICATION clause unless you need it.

Now, to add to the fun create the tables again, now with the primary key as clustered and without NOT FOR REPLICATION clause. Run the query execution plan for the same query again.

On SQL server 2000

On SQL server 2005

On both SQL server versions there is a clustered index scan just on the partition that is in the scope of the query.

If any one of you figures out why the NOT FOR REPLICATION on the check constraint makes such a huge query degradation, I would like to know why.

I have opened a thread on the MSDN forum for the same problem. You can find it here.