Vikram Kamath

March 21, 2007

Partitioned views: CHECK constraints with NOT FOR REPLICATION clause

Filed under: SQL Server — Vikram @ 17:08

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:

Create two identical tables:

create table [dbo].[Table_1]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),  
 [AnyOtherColumn] int  NOT NULL ,  
) ON [Primary]
GO

create table [dbo].[Table_2]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),  
 [AnyOtherColumn] int  NOT NULL ,
) ON [Primary]
GO

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.


create table [dbo].[Table_1]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK ([TableID] BETWEEN 1 AND 999),  
 [AnyOtherColumn] int  NOT NULL ,  
) ON [Primary]
GO

create table [dbo].[Table_2]
(
 [TableID]  INTEGER PRIMARY KEY NONCLUSTERED
                CHECK ([TableID] BETWEEN 1000 AND 1999),  
 [AnyOtherColumn] int  NOT NULL ,
) ON [Primary]
GO

create view TableView
as
 select * from Table_1
 union all
 select * from Table_2
GO

Now see the execution plan for the same query

select * from TableView where TableID = 10

This is what you see in SQL server 2000:

This is the execution plan on SQL server 2005

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: