Contradiction detection

I am trying to learn Table Partitioning. However, I struggling to understand why SQL Server behaving stage for a simple query.

Test dataset

```
/* --------------------------------------------------
-- Create helper function GetNums by Itzik Ben-Gan
-- http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-- GetNums is used to insert test data
-------------------------------------------------- */

-- Drop helper function if it already exists
IF OBJECT_ID('GetNums') IS NOT NULL
DROP FUNCTION GetNums;
GO

-- Create helper function
CREATE FUNCTION GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

/* ------------------------------------------------------------
-- Create example Partitioned Table (Heap)
-- The Partition Column is a DATE column
-- The Partition Function is RANGE RIGHT
-- The Partition Scheme maps all partitions to [PRIMARY]
------------------------------------------------------------ */

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'Sales')
DROP TABLE Sales;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
DROP PARTITION FUNCTION pfSales;

-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE right FOR VALUES
('2013-01-01', '2014-01-01', '2015-01-01');

-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);

-- Create the Partitioned Ta

You're getting a trivial and simple parameterized plan for your simple query.

Add AND 1 = (SELECT 1); to the end of your query, and you'll get the constant scan/contradiction detection.

Using just AND 1 = 1 doesn't always work if the aim is to also avoid trivial plans. See Query Plans: Trivial Optimization vs Simple Parameterization by Erik Darling.

You should also get into the habit of being careful about data types. A string is not a date, and there is no special T-SQL syntax to specify a date literal. You can use CONVERT to be explicit, e.g. SalesDate > CONVERT(date, '20160101', 112).

Related reading: Why Doesn’t Partition Elimination Work? by Paul White.

StackExchange Database Administrators Q#184706, answer score: 9