1

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?

Lock
  • 5,422
  • 14
  • 66
  • 113
  • How many records do you have in your tables? BTW, partitioned view is like regular views just with filter. when selecting from view, SQL server still needs to access the tables. – FLICKER Apr 04 '16 at 22:58
  • @FLICKER That is not correct. The purpose of Partitioned Views is specifically to avoid accessing tables that can be logically excluded from the results based on a particular condition that is defined in the `CHECK` constraints. – Solomon Rutzky Apr 05 '16 at 01:07

1 Answers1

0

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.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171