Query performance on paritioned views with check constraints

Hi,

I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.

I have two partitioned tables and a view on top of both tables as below:

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

create view TableView

as

select * from Table_1

union all

select * from Table_2

GO

Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.

I then ran the query execution plan for the following query on both SQL server 2000 and 2005.

select * from TableView where TableID = 10

On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.

Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:

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 run the query execution plan for the same query again.

select * from TableView where TableID = 10

This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.

I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?

Is it a bug in SQL server?

Or am I missing any thing?

Any help appreciated.

Thanks

NOT FOR REPLICATION means that your check constraint is not enforced when a replication agent performs insert, update, or delete operations. This means that the system cannot assume that the data in the column actually meets that constraint and so it has to scan both tables.

Removing the option means that it knows that data in the column meets the constraint and only has to scan the table whose constraint contains that value.

|||

Thanks,

I understand why we use NOT FOR REPLICATION clauses. But replication was not is the scope of this query. I meant to know the performance impact for queries on the partitioned view, with and without the NOT FOR REPLICATION clause on check constraint in the underlying tables.

for more detail, I have mentioned the problem here

http://vikramkamath.wordpress.com/2007/03/21/partitioned-views-check-constraints-with-not-for-replication-clause/

Cheers