My partitioned view is still accessing all underlying base tables

Question

I am trying to create a partitioned view, however my execution plan is showing that it is still accessing both underlying tables.

SQL Fiddle here

Why is my query still accessing both underlying tables and then concatenating them?

Answer 1

Most likely the issue is that your CHECK Constraint does not match your WHERE condition.

Your Check Constraints are in the form of:

(datepart(year,[StockDate])=(2016))

Your WHERE condition is in the form of:

StockDate = '20160101'

Change your Check Constraints to use full dates (plus there is no need to use a function -- DATEPART -- for such a simple filter). The following is for the 2016 table:

[StockDate] >= '01/01/2016' AND [StockDate] <= '12/31/2016'

Repeat that for the other tables, changing the year in both predicates to match the year of the table.

Please note that the syntax above does not state the time component because the field in question is a DATE datatype. If the datatype were DATETIME, then the end of the range would need to be expressed as:

'12/31/2016 23:59.59.997'

For more info on Partitioned Views, please see the MSDN page for Using Partitioned Views.