"NOT Column LIKE pattern" vs "Column NOT LIKE pattern"

All we need is an easy explanation of the problem, so here it is.

Is there a logical or performance difference between these two seemingly equivalent expressions?

  1. WHERE NOT [Column] LIKE '%pattern%'
  2. WHERE [Column] NOT LIKE '%pattern%'

…or do both result in the same execution plan and results?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

From a logical perspective, these are exactly the same.

Even the presence of NULL does not affect it, because if the column is NULL then [Column] LIKE '%pattern%' evaluates to UNKNOWN and NOT UNKNOWN is still UNKNOWN and therefore fails the WHERE.

As far as actual implementation, not only are the logically the same, they actually execute the same code.

A careful inspection of the XML query plans reveals the below XML in the case of [Column] NOT LIKE

<Predicate>
    <ScalarOperator ScalarString="NOT [fiddle_bf98353ecbfa4f60a8c1d988014ffc03].[dbo].[t].[Column] like &apos;%pattern%&apos;">
        <Logical Operation="NOT">
            <ScalarOperator>
                <Intrinsic FunctionName="like">
                    <ScalarOperator>
                        <Identifier>
                            <ColumnReference Database="[fiddle_bf98353ecbfa4f60a8c1d988014ffc03]" Schema="[dbo]" Table="[t]" Column="Column"></ColumnReference>
                        </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                        <Const ConstValue="&apos;%pattern%&apos;"></Const>
                    </ScalarOperator>
                </Intrinsic>
            </ScalarOperator>
        </Logical>
    </ScalarOperator>
</Predicate>

And this in the case of NOT [Column] LIKE

<Predicate>
    <ScalarOperator ScalarString="NOT [fiddle_bf98353ecbfa4f60a8c1d988014ffc03].[dbo].[t].[Column] like &apos;%pattern%&apos;">
        <Logical Operation="NOT">
            <ScalarOperator>
                <Intrinsic FunctionName="like">
                    <ScalarOperator>
                        <Identifier>
                            <ColumnReference Database="[fiddle_bf98353ecbfa4f60a8c1d988014ffc03]" Schema="[dbo]" Table="[t]" Column="Column"></ColumnReference>
                        </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                        <Const ConstValue="&apos;%pattern%&apos;"></Const>
                    </ScalarOperator>
                </Intrinsic>
            </ScalarOperator>
        </Logical>
    </ScalarOperator>
</Predicate>

In other words, they are exactly the same.

Method 2

I believe the best way is to Test yourself and check how it really works out in the Lab.

I am having Stackoverflow database in my Lab and I conducted the test and could see that there is no difference whatsoever whether it is logical read or CPU time or Execution plan.

Below is the Screenshot of Logical Reads and Time Stats:

"NOT Column LIKE pattern" vs "Column NOT LIKE pattern"

Now, lets check the execution plan:

"NOT Column LIKE pattern" vs "Column NOT LIKE pattern"

Also to add here that, I have an index on Location column however due to high number of rows as well as *(all columns) in select, optimizer preferred to go for Clustered Index Scan.

If I change the query and take only Location column in the select, Nonclustered index scan is performed however no change in the performance or execution plan.

"NOT Column LIKE pattern" vs "Column NOT LIKE pattern"

Please be mindful of leading wildcard in the where clause as it will have to do full column scan instead of seek operation as it needs to search the complete string.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply