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

Question

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?

Answer 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.

Answer 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:

Now, lets check the execution plan:

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.

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.