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