Consider [this answer](https://stackoverflow.com/a/15179434/57611) on SO that reassures the asker about the `<>` operator that: > `<>` is ... the same as `!=`. But then a commenter pipes up and says: > It's true that they are, functionally, the same. However, how the SQL optimizer uses them is very different. =/!= are simply evaluated as true/false whereas <> means the engine has to look and see if the value is greater than or less than, meaning more performance overhead. Just something to consider when writing queries that may be expensive. I am confident this is false, but In order to address potential skeptics, I wonder if anyone can provide an authoritative or canonical source to prove that these operators are not just functionally the same, but identical in all aspects?
TopAnswers
Databases
Top Answer
Paul White
**During parsing**, SQL Server calls `sqllang!DecodeCompOp` to determine the type of comparison operator present:
[![Call stack][1]][1]
This occurs well before anything in the optimizer gets involved.
>From [Comparison Operators (Transact-SQL)][2]
>[![Comparison operators and meanings][3]][3]
Tracing the code using a debugger and public symbols[^symbols], `sqllang!DecodeCompOp` returns a value in register `eax`[^eax] as follows:
~~~
╔════╦══════╗
║ Op ║ Code ║
╠════╬══════╣
║ < ║ 1 ║
║ = ║ 2 ║
║ <= ║ 3 ║
║ !> ║ 3 ║
║ > ║ 4 ║
║ <> ║ 5 ║
║ != ║ 5 ║
║ >= ║ 6 ║
║ !< ║ 6 ║
╚════╩══════╝
~~~
`!=` and `<>` both return 5, so are **indistinguishable** in all later operations (including compilation & optimization).
---
Though secondary to the above point, it is also possible (e.g. using undocumented trace flag 8605) to look at the logical tree passed to the optimizer to confirm that both `!=` and `<>` map to `ScaOp_Comp x_cmpNe` (not equal scalar operator comparison).
For example:
```sql
SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID != 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);
SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID <> 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);
```
both produce:
~~~
LogOp_Project QCOL: [P].ProductID
LogOp_Select
LogOp_Get TBL: Production.Product(alias TBL: P)
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [P].ProductID
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
AncOp_PrjList
~~~
---
### Footnotes
[^symbols]: I use [WinDbg][4]; other debuggers are available. Public symbols are available via the usual Microsoft symbol server. For more information, see [Looking deeper into SQL Server using Minidumps][5] by the SQL Server Customer Advisory Team and [SQL Server Debugging with WinDbg – an Introduction][6] by Klaus Aschenbrenner.
[^eax]: Using EAX on 32-bit Intel derivatives for return values from a function is common. Certainly the Win32 ABI does it that way, and I'm pretty sure it inherits that practice from back in the old MS-DOS days, where AX was used for the same purpose - [Michael Kjörling][7]
[1]: https://i.stack.imgur.com/4qRy4.png
[2]: https://msdn.microsoft.com/en-us/library/ms188074.aspx
[3]: https://i.stack.imgur.com/ThPRY.png
[4]: http://www.windbg.org/
[5]: https://blogs.msdn.microsoft.com/sqlcat/2009/09/11/looking-deeper-into-sql-server-using-minidumps
[6]: http://www.sqlpassion.at/archive/2014/05/05/sql-server-debugging-with-windbg-an-introduction
[7]: https://dba.stackexchange.com/users/3984/michael-kj%C3%B6rling