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 Table (Heap) on the Partition Scheme
CREATE TABLE Sales 
(
    SalesDate DATE constraint ck_date check(SalesDate&lt;'2016-01-01'),
    Quantity INT
) ON psSales(SalesDate);

-- Insert test data
INSERT INTO Sales(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;


Qery

select count(1) from sales where SalesDate&gt;'20160101'


Please see the execution plan below. 

Execution Plan

Questions


Why SQL doing CONVERT_IMPLICIT
Why SQL is not doing the contradiction detection to avoid the table scan.


Many thanks
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 &gt; CONVERT(date, '20160101', 112).

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