In almost all cases where a NOT EQUAL operator (<>) is used in a JOIN or a WHERE there will be a scan. Although you can often avoid a scan by using 2 <> , it’s always better to try and use an EQUAL OPERATOR (=)
This situation becomes pronounced when there is an attempt at a large bulk load or archiving job. Applying the <> operator can have a serious impact on run times of jobs. It may not be as noticeable over a few rows , but if you’re targeting millions of rows, you may need to rethink your strategy.
There are plenty of workarounds to this problem. It may require you break away from completing the task in a 1 step transaction, it may require a few interim steps.
One particular workaround is to use a computed column . Let’s work through an example.
Step 1 – Initial sql statement with a NOT EQUAL operator , which generates a Scan
FROM myTable1 mt
WHERE mt.myvalue <> ' hghghg ';
Step 2 – Create a computed column for rows which qualify to be manipulated
ALTER TABLE myTable ADD computed_col AS (CASE WHEN myvalue = 'hghghg' then 1 ELSE 0 END)
All of a sudden you have an extra column which has a value based on the condition you’ve set
Step 3 – Adjust the SQL code from Step 1 , notice now it includes the equal operator
FROM myTable mt
WHERE mt.computed_col = 1
This workaround is not suitable for every situation where you may see a scan as a result of a NOT EQUAL operator. But it is a useful technique for ad-hoc bulk edit jobs.
You may need to drop the computed column when completed.
Before you use the computed column workaround, doublecheck with the application owner or vendor , as in some cases adjustments to tables are not allowd.