Check constraint and index scan
sabin 
Hi,

I have an uncertain : there is a table with 2 fields (partition and dataAreaID) which have the same value for all records. 
And for one field (partition field), is in place a check constraint(`CHECK (partition =1)`).

There is an non clustered index on 3 fields: `partition, dataAreaID, city`. (`Partition` and `dataAreaID` are added automatically, thanks to dynamic AX)

Also , there is a check constraint on `partition` field.
And the query is searching for a particular value in `city` field.


The next query, with added  `and 1=(select 1)` to enter in optimization phase, to simulate a complex query,  is scanning the non clustered index and also is generating a missing index.

    select partition,dataAreaID,city
    from dbo.x_yes as y	
    where 
        partition = 1 
        and dataAreaID = 1
        and city ='london'
        and 1=(select 1)

the query with out `and 1=(select 1)` is generating a trivial plan and (I believe) an optimal plan: seek on the existing non clustered index

	select partition,dataAreaID,city
    from dbo.x_yes as y	
    where 
	    partition = 1 
     	and dataAreaID = 1
    	and city ='london'

**What happens here**? What is "breaking" the efficient use of that index resulting in a scan instead of a seek ? Is that  check constraint that is taken into account or parametrization is contributing to it ?

With out check constraint, both queries, are seeking on the nonclustered index.

With check constraint on partition field, only the second query is seeking on the nonclustered index, trivial plan and simple parametrization on.

Also adding option (recompile) will return to scan(still trivial plan)


The plan with `and 1=(select 1)`
https://www.brentozar.com/pastetheplan/?id=B1CCVzKEw


The plan with `out and 1=(select 1)` 
https://www.brentozar.com/pastetheplan/?id=SyWErGFVv


The plan with `out and 1=(select 1)` and `option(recompile)`
https://www.brentozar.com/pastetheplan/?id=ByULrfFEv


**SQL Server 2019**, and dbfiddle 
<> https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=72a5f4f7b96df4089fde1d2892cb2ead



The scripts:

    create table dbo.x_yes
    (
        id int identity(1,1) not null primary key ,
        partition tinyint not null,
        dataAreaID tinyint not null,
        city varchar(50) not null,

        colInt int null, 
        colChar char(7000) null,
        constraint cc_xYes_partition CHECK (partition =1)
    )


/*The check constraint is trusted*/

	select is_not_trusted, name
    from sys.check_constraints
    where name like 'cc_xYes%'
    
    is_not_trusted	name
    0	            cc_xYes_partition

 
/*There is an index on partition, dataAreaID, city*/

    create index i_yes on x_yes (partition, dataareaID, city);
    
    insert into x_yes(partition, dataAreaID, city, colInt, colChar)
    select s.partition, s.dataAreaID, s.city, s.colInt, s.colChar
    from 
        (select 1 as partition,1 as dataAreaID,'berlin' as city,1 as colInt,'no' as colChar UNION ALL
         select 1,1,'praga',1,'no' ) as s
        cross join
        (select 1 as idx union all select 2 union all select 3 union all select 4 union all select 5) as a
        cross join
        (select 1 as idx union all select 2 union all select 3 union all select 4 union all select 5) as b
        cross join
        (select 1 as idx union all select 2 union all select 3 union all select 4 union all select 5) as c
        cross join
        (select 1 as idx union all select 2 union all select 3 union all select 4 union all select 5) as d
        cross join
        (select 1 as idx union all select 2 union all select 3 union all select 4 union all select 5) as e
        cross join
        (select 1 as idx union all select 2 union all select 3 union all select 4 union all select 5) as f


    insert into x_yes(partition, dataAreaID, city, colInt, colChar)
    values (1,1,'london',1,'no')
Top Answer
Paul White 
### Explanation

Ordinarily, SQL Server will remove the `WHERE` clause predicate `partition = 1` during *predicate normalization*.

It can do this because:

* There is a trusted check constraint `partition = 1`
* The partition column is also constrained to be `NOT NULL`

Removing the redundant predicate means *index matching* cannot link the remaining predicates to the index with `partition` as the leading key.

It is just as if you had written the query without the `partition` predicate at all. One might argue the optimizer ought to be able to match the index by reasoning about the check constraint, but it doesn't do that today.

### Parameterization

As mentioned in the question, one way to avoid this issue is to parameterize the query. A parameterized predicate like `partition = @pValue` cannot be simplified away by the check constraint.

To be clear, the issue isn't about *trivial plan* versus *cost-based optimization*. SQL Server will attempt [simple parameterization](https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide#SimpleParam) for a narrow range of simple statements. When this is successful, the `partition` predicate is not eliminated and you get your desired index seek.

Here a couple more examples that qualify for a trivial plan, but not simple parameterization. Both result in an index scan:

```
select partition,dataAreaID,city
from dbo.x_yes as y	
where 
	partition = 1 
	and dataAreaID = 1
	and city ='london'
OPTION (MAXRECURSION 100); -- hint prevents simple param

select partition,dataAreaID,city
from dbo.x_yes as y	
where 
	partition IN (1, 1) -- IN list prevents simple param
	and dataAreaID = 1
	and city ='london';
```

It is unfortunate that Dynamics AX places `partition` as the leading key of the nonclustered index.

Avoiding any of the contributing factors will avoid the issue, but explicitly parameterizing queries against this table is a good option:

```
EXECUTE sys.sp_executesql
    N'
    select
        y.partition,
        y.dataAreaID,
        y.city
    from dbo.x_yes as y
    where 
	    y.partition = @P1 
	    and y.dataAreaID = @P2
	    and y.city = @P3
    ',
    N'@P1 tinyint, @P2 tinyint, @P3 varchar(50)',
    @P1 = 1, 
    @P2 = 1, 
    @P3 = 'london';
```
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.