Authoritative source that <> and != are identical in performance in SQL Server

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present:

Call stack

This occurs well before anything in the optimizer gets involved.

From Comparison Operators (Transact-SQL)

Comparison operators and meanings

Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register 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:

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

* I use WinDbg; 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 by the SQL Server Customer Advisory Team and SQL Server Debugging with WinDbg – an Introduction by Klaus Aschenbrenner.

** 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


I work at Microsoft in SQL Support and I asked Jack Li, Senior Escalation Engineer and Subject Matter Expert of SQL Server performance, "Does SQL treat != any differently than <> ?" and he said, "They are the same."


I think the following proves that <> doesn't do 2 comparisions.

  1. SQL Standard 92 defines <> as not equals operator , (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ) . Technically, != is an extension to the standard (even though I can't think about any RDBMS that doesn't implement it).
  2. If SQLServer treated <> as 2 operators, not one, it would do the same for >< which is in fact syntax error.

Tags:

Performance

Sql Server

Operator

Recent Posts