Contradiction detection
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.
Why SQL doing CONVERT_IMPLICIT
Because you have a literal in the query that needs to be parsed and converted to a date. This is not a performance concern.
SQL is not doing the contradiction detection to avoid the table scan.
With no indexes, a table scan is inevitable. However, as you can see from the table scan operator properties, a seek is used so only the partition containing the specified SalesDate
value is scanned. Run the query with STATISTICS IO ON
with and without the WHERE
clause to see the impact of partition elimination:
SELECT COUNT(1) FROM sales WHERE SalesDate>'20160101';
Table 'Sales'. Scan count 1, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT COUNT(1) FROM sales;
Table 'Sales'. Scan count 4, logical reads 2890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The partition aware seek operation was introduced in SQL Server 2008. Previously (SQL 2005), partition elimination was accomplished with separate execution plan operators.
EDIT I just realized the main question was mostly about the check constraint than partition boundaries. I think this answer still provides some value so I won't delete it.