Partitioned view not working

Question

I am trying to get partitioned views to work, and still see query optimizer scanning both tables. Here is my script:

CREATE TABLE A(DataDate [date] NOT NULL CHECK(datepart(yy, DataDate) <= 2010)) 

CREATE TABLE B(DataDate [date] NOT NULL CHECK(datepart(yy, DataDate) > 2010))

CREATE VIEW V AS SELECT * FROM A UNION ALL SELECT * FROM B 

select * from V where datepart(yy, DataDate) = 2009

But when I look at the execution plan I see that both table A and table B were scanned and the "Number of Executions" for both tables is 1. I would expect table A to have Number of Executions = 1, and table B to have Number of Executions = 0. I am on SQL Server 2012.

Can somebody clarify how this is supposed to work? Thank you.

Answer 1

Is supposed to work like this:

CREATE TABLE A(DataDate [date] NOT NULL CHECK(DataDate < '20110101'));

CREATE TABLE B(DataDate [date] NOT NULL CHECK(DataDate >= '20110101'));
go

CREATE VIEW V AS SELECT * FROM A UNION ALL SELECT * FROM B 
go

select * from V where DataDate >= '20090101' and DataDate < '20100101';
go

Answer 2

From the documentation in CREATE VIEW, on Partitioned Views, when discussing the constraints that must exist on the base tables:

Constraint C1 defined on table T1 must be of the following form:

C1 ::= < simple_interval > [ OR < simple_interval > OR ...]
< simple_interval > :: = 
< col > { < | > | <= | >= | = < value >} 
| < col > BETWEEN < value1 > AND < value2 >
| < col > IN ( value_list )
| < col > { > | >= } < value1 > AND
< col > { < | <= } < value2 >

Your constraints are not of this form, and thus it's not eligible to be a partitioned view. You need simple constraints, such as the ones that @Remus has shown, and not ones that call on functions.

---

And, of course, the other advice found anywhere you look up Partitioned Views in BOL:

The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes.

Partitioned tables have been the preferred method of partitioning since their introduction in SQL Server 2005.