Consider this answer 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?


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

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID <> 4

both produce:

LogOp_Project QCOL: [P].ProductID
        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)


* 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

| improve this answer | |

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

| improve this answer | |
  • Why did Microsoft remove the debugger? Isn't there really one in Microsoft that will make this possible again? Why doesn't anyone answer? This is annoying to me. If you can answer? Thankful, i hope you wish the best. – Amirhossein Jul 5 at 8:44
  • dba.stackexchange.com/questions/221349/… I don't know. I can ask around. – stacylaray 6 hours ago
  • A workaround is to add an input parameter for debug and set it to off. When you want to debug, turn it on and put in print statements. This doesn't require sysadmin rights and will work regardless of the database you are attached to. The debugger required you to be sysadmin and have a bunch of ports open, which a lot of customers couldn't do. Also, it wouldn't work connecting to AzureDB, for obvious reasons. – stacylaray 5 hours ago

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.
| improve this answer | |

That is incorrect, Books Online (BOL) says they are functionally the same:

!= (Not Equal To) (Transact-SQL)

And if you look at an execution plan where != is used, under Predicate, it changes != to <>.

| improve this answer | |
  • 2
    The problem is that the language "functionally the same" is already admitted in the referenced comment, but makes an additional distinction about performance, despite your and my knowledge that "functionally the same" includes how it actually works and any performance characteristics thereof. If one were going to go about proving this beyond all determined skepticism, what would one do? – ErikE Nov 30 '16 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.