How to eliminate tables in a partitioned view

I can't get my query that joins a regular table to a partitioned view to eliminate tables that don't meet the predicates on the partitioning column. In particular, I'm interested in the case where I LEFT OUTER JOIN to the partitioned view and my predicate covers a range of values. When I modify the query to use an INNER JOIN or restrict the predicate to a single value, the tables are correctly eliminated. Here's a script to demonstrate the issue. I tested this in SQL Server 2016 SP1.

```
--Create 2 tables for prices; 1 for 2017 and 1 for 2018
CREATE TABLE Price_2017
(
PriceDate DATE NOT NULL,
PriceValue FLOAT NOT NULL
)
GO

ALTER TABLE Price_2017 ADD CONSTRAINT PK_Price_2017 PRIMARY KEY(PriceDate);
GO

ALTER TABLE Price_2017 WITH CHECK ADD CONSTRAINT CK_Price_2017
CHECK (PriceDate >= '2017-01-01' AND PriceDate = '2018-01-01' AND PriceDate <= '2018-12-31');
GO

ALTER TABLE Price_2018 CHECK CONSTRAINT CK_Price_2018;
GO

--Create a partitioned view for all dates
CREATE VIEW Price_All AS
SELECT p.PriceDate, p.PriceValue
FROM dbo.Price_2017 p
UNION ALL
SELECT p.PriceDate, p.PriceValue
FROM dbo.Price_2018 p;

--Create some prices
INSERT INTO Price_2017 (PriceDate, PriceValue) VALUES('2017-01-01',1);
INSERT INTO Price_2017 (PriceDate, PriceValue) VALUES('2017-01-02',2);

INSERT INTO Price_2018 (PriceDate, PriceValue) VALUES('2018-01-01',10);
INSERT INTO Price_2018 (PriceDate, PriceValue) VALUES('2018-01-02',20);

--Create another table that we will relate to prices
CREATE TABLE Purchase
(
PurchaseDate DATE NOT NULL,
Quantity INT NOT NULL
)
GO

ALTER TABLE Purchase ADD CONSTRAINT PK_Purchase PRIMARY KEY(PurchaseDate);
GO

--Put some stuff in the other table
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2017-01-01', 1);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2017-01-02', 2);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2017-01-03', 3);
INSERT INTO Purchase (PurchaseDate, Quantity) VALUES ('2018-01-01', 4);
INSERT INT

SQL Server is indeed eliminating the 2018 partition even though it shows up in the plan.

The startup expression filter in the plan eliminates the unneeded 2018 table at execution time. After running the query with actual execution plan turned on, hover your mouse over the Price_2018 table's seek:

Note that "Number of Executions" is 0. That means SQL Server eliminated the seek at runtime.

You can also run the query with STATISTICS IO ON to see that only the 2017 table is touched:

SET STATISTICS IO ON;
GO
SELECT pu.PurchaseDate, pu.Quantity, pr.PriceValue
FROM Purchase pu
LEFT OUTER JOIN Price_All pr --notice, left join
ON pr.PriceDate = pu.PurchaseDate
WHERE pu.PurchaseDate BETWEEN '2017-01-01' AND '2017-01-02'; --notice, range of dates

The Statistics IO output shows:

(2 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Price_2017'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Purchase'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The Price_2018 table isn't in that list - meaning it didn't get touched.

StackExchange Database Administrators Q#199864, answer score: 5