I was asked this question recently when I conducted a SQL Session and it felt like a very simple and easy question to answer and something I am sure we have all wondered at some point. SQL is a very complex machine and behaves differently based on how we write the code so a choice of operators might seem like something we should explore. The simple answer is NO there is no difference as you can see in the screenshots below however it still begs the question is there a preference for the SQL OS itself?
The below screenshot shows the query we will be using to understand this usage. We first start off with <> as the inequality operator.
Using <>
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME <> ‘TheMonthName’Stats
SQL SERVER parse
AND compile
time: CPU TIME = 47 ms
,elapsed TIME = 54 ms.SQL SERVER parse
AND compile
time: CPU TIME = 0 ms
,elapsed TIME = 0 ms.(18 rows affected) TABLE 'Workfile'.Scan count 0
,logical reads 0
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'Worktable'.Scan count 0
,logical reads 0
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'syscolpars'.Scan count 1
,logical reads 26
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'sysschobjs'.Scan count 1
,logical reads 40
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'sysobjvalues'.Scan count 1
,logical reads 3
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'sysscalartypes'.Scan count 1
,logical reads 2
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. SQL SERVER Execution
Times: CPU TIME = 0 ms
,elapsed TIME = 14 ms.Completion
time: 2024 - 02 - 22
T10: 41 : 11.8195372 + 05 : 30
Execution Plan
Now lets repeat the same process for the != operator. Pay close attention to the boxes in the screenshot with the execution plan. Even though we use the != operator in the query you can see the Optimizer uses <> as the default. However you can see for all intents and purposes everything else remains the same. Ther is no change in behaviour with regard to optimization or query speed no matter which one you use.
Using !=
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME != 'TheMonthName'Stats
SQL SERVER parse
AND compile
time: CPU TIME = 0 ms
,elapsed TIME = 0 ms.SQL SERVER parse
AND compile
time: CPU TIME = 0 ms
,elapsed TIME = 0 ms.(18 rows affected) TABLE 'Workfile'.Scan count 0
,logical reads 0
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'Worktable'.Scan count 0
,logical reads 0
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'syscolpars'.Scan count 1
,logical reads 26
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'sysschobjs'.Scan count 1
,logical reads 40
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'sysobjvalues'.Scan count 1
,logical reads 3
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. TABLE 'sysscalartypes'.Scan count 1
,logical reads 2
,physical reads 0
,page SERVER reads 0
,READ - ahead reads 0
,page SERVER READ - ahead reads 0
,lob logical reads 0
,lob physical reads 0
,lob page SERVER reads 0
,lob READ - ahead reads 0
,lob page SERVER READ - ahead reads 0. SQL SERVER Execution
Times: CPU TIME = 0 ms
,elapsed TIME = 10 ms.Completion
time: 2024 - 02 - 22
T10: 43 : 11.4902200 + 05 : 30Execution Plan
You can see from the above screenshot that even though in out query we != its actually translated into <> within the execution plan. So that answers the question. Is there any significant impact? Not unless you have a preference.