Ardalan Shahgholi Ardalan Shahgholi - 5 months ago 10
SQL Question

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

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
?!?

Answer

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