Why SQL Server Optimizer do not use CHECK constraint definitions to find which table contains the rows?

Question

I use SQL Server 2012 and I have a large table and I divided my table in some tables like below :

Create Table A2013
(
    Id int identity(1,1),
    CountA int ,
    Name varchar(50),
    ADate DATETIME NULL
      CHECK (DATEPART(yy, ADate) = 2013)
)

Create Table A2014
(
    Id int identity(1,1),
    CountA int ,
    Name varchar(50),
    ADate DATETIME NULL
      CHECK (DATEPART(yy, ADate) = 2014)
)

Insert Into A2013 Values ( 102 , 'A','20131011' )
Insert Into A2013 Values (15 , 'B' ,'20130211' )
Insert Into A2013 Values ( 54, 'C' ,'20131211' )
Insert Into A2013 Values ( 54, 'D' ,'20130611' )
Insert Into A2013 Values ( 95, 'E' ,'20130711' )
Insert Into A2013 Values (8754 , 'F' ,'20130310' )

Insert Into A2014 Values ( 102 , 'A','20141011'  )
Insert Into A2014 Values (15 , 'B' ,'20140911' )
Insert Into A2014 Values ( 54, 'C' ,'20140711' )
Insert Into A2014 Values ( 54, 'D' ,'20141007' )
Insert Into A2014 Values ( 95, 'E' ,'20140411' )
Insert Into A2014 Values (8754 , 'F' ,'20140611' ) 

I created a partition view like below:

Create View A 
As
    Select * From A2013
    Union 
    Select * From A2014

I hope SQL Optimizer use a good plan and use my CHECK constraint definitions to determine which member table contains the rows but it scan two table when run this query :

Select * From A Where A.ADate = '20140611'

I expected that SQL Optimiser do not use table A2013?!?

Answer 1

The CHECK CONSTRAINT expression must be sargable in order for the optimizer to eliminate the unneeded tables in the execution plan. The constraints below avoid applying a function to the column and are sargable:

CREATE TABLE dbo.A2013
    (
      Id int IDENTITY(1, 1)
    , CountA int
    , Name varchar(50)
    , ADate datetime NULL
      CONSTRAINT CK_A2013_ADate
                     CHECK ( ADate >= '20130101'
                             AND ADate < '20140101' )
    );

CREATE TABLE dbo.A2014
    (
      Id int IDENTITY(1, 1)
    , CountA int
    , Name varchar(50)
    , ADate datetime NULL
      CONSTRAINT CK_A2014_ADate
                     CHECK ( ADate >= '20140101'
                             AND ADate < '20150101' )
    );

Answer 2

The issue is not whether the expression is sargable. As far as I know, the term "sargable" applies to the use of indexes in queries. The question is whether SQL Server recognizes the where clause as matching the check constraint.

The check constraint you have is:

CHECK (DATEPART(yy, ADate) = 2014)

The where clause is:

Where A.ADate = '20140611'

The problem is that the second is not recognized as a subset of the first. You could fix this by adding redundancy:

Where A.ADate = '20140611' and DATEPART(yy, A.ADate) = 2014

Or, you could fix this by using ranges -- but be careful about data types, because data type conversion can definitely confuse the optimizer. I think the following will work:

CHECK ADate BETWEEN '2014-01-01' and '2014-12-31'
WHERE A.ADate = '2014-06-11'

(The hyphens are optional and can be dropped.)

The documentation (as far as I can tell) is not really explicit about the cause:

The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables. Therefore, it limits its search to those tables.

. . .

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.