Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Increase speed of a NOT EQUAL operator and a workaround using a computed column

20 March,2016 by Tom Collins

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.

Read More on speeding up bulk operations

How to delete millions of rows in batches

How to complete an INSERT in batches for large data sets

Data Loading Performance Guide

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Increase speed of a NOT EQUAL operator and a workaround using a computed column | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer