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'

enter image description here

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

What if you add the `AND DATEPART(yy, ADate) = 2014` explicitly? — zerkms, Nov 18 '14 at 01:38
why don' u use partitions , in that case no need of multiple tables. — radar, Nov 18 '14 at 01:43
@ArdalanShahgholi, http://msdn.microsoft.com/en-us/library/ms188730.aspx , you actually split data into different file groups — radar, Nov 18 '14 at 01:58
@RADAR tanks for your response , but which time we most use partition view? — Ardalan Shahgholi, Nov 18 '14 at 02:12

Dan Guzman · Accepted Answer · 2018-02-20T13:24:02.330

5

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' )
    );

edited Feb 20 '18 at 13:24

answered Nov 18 '14 at 01:38

Dan Guzman

43,250
3
46
71

Wouldn't it produce the better execution plan with `where DATEPART(yy, ADate) = 2014`? I don't think the issue is sargabiility; I think the issue is equivalence. – Gordon Linoff Nov 18 '14 at 01:39
TIL that sargable is a word. Any ideas about its etymology? – Thilo Nov 18 '14 at 01:42
@Gordon_Linoff, I think Ardalan wants SQL Server to eliminate the unneeded tables from the plan, either statically or dynamically. With a non-sargable expression in the check constraint, elimination will not occur. With a non-sarable expression in the WHERE clause like DATEPART, all tables in the view will also be scanned. – Dan Guzman Nov 18 '14 at 01:46
1

Almost any expression in a check constraint is "sargable", if the same expression is used in the `where` clause. The problem is that SQL Server does not recognize the *equivalence* between the `where` expression and the expression in the `check` constraint. – Gordon Linoff Nov 18 '14 at 01:51

score 3 · Answer 2 · answered Nov 18 '14 at 02:04

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.

It is true that the term sargable is most often used to refer to efficient use of indexes. However, the same concept applies more generally to elimination of tables and partitions in queries. If you examine the execution plan with the redundant DATEPART predicate, I think you'll find that all tables were touched even though the optimizer could have been smart enough to include only the table matching the constraint. — Dan Guzman, Nov 18 '14 at 12:58

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

2 Answers2