Is there a difference between NOT (ColumnName LIKE '%a%') and ColumnName NOT LIKE '%a%'

Question

I came across this when amending someone else's View in SQL Server.

Are there any differences between:

NOT (ColumnName LIKE '%a%')

and

ColumnName NOT LIKE '%a%'

Are there any performance differences or any difference in results? Or is this just another strange way (strange to me anyway) that SQL Server alters your code when you use a Query builder or a View builder?

Answer 1

No, there's no difference.

The two syntaxes both make sense - not like is the clearer one, but it's not like you can just disable not (a simple unary boolean operator) in cases like this.

Most of the time, the form of the query doesn't really matter for performance - any time it does, you're probably looking at a subtle bug in the execution planner. Before the query gets anywhere close to executing, it's torn apart, analyzed and rebuilt into the actual steps needed to get the results - whenever you wonder what's really happening in a query, just execute it with Include actual execution plan, and you'll see.

If you're a human, there's little reason not to use not like - you'd mostly use the other way as part of some automatic generation of code (negating the result of another expression), not in human written code. Unless you're building that query by slapping strings together, of course - then it might make sense even when you write it yourself.

Oh, and as for style, note that as I said, not is an operator, not a function call. So the proper way to use parentheses would be either

not ColumnName like '%a%'

or better,

(not ColumnName like '%a%')

or, if you're crazy (or again, generating code):

(not (ColumnName LIKE '%a%'))

Using the function call syntax might be confusing to some - and operator precedence in T-SQL is not exactly intuitive (it tries too hard to be easy to use and avoid parentheses, which is kind of confusing sometimes). Most SQL generators will tend to be on the cautious side, and use more parentheses than strictly necessary, which usually leads to better readability (and correctness :)), while having no meaningful performance impact or anything.