I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case]

Is there any way to improve the performance of the query

FROM myTable
WHERE Currency <> ''
AND Country <> ''

From what I understand, <> is causing a complete table scan?

  • 1
    What percent of the rows will match the condition? (Currency <> '' AND Country <> '') Sep 4, 2013 at 11:16
  • About 33% roughly. There are also queries on Currency = '' AND Country <> '' etc. etc.. And also Currency = '' AND Country = ''...which don't seem to be an issue - they run much quicker.
    – BlueChippy
    Sep 4, 2013 at 11:29
  • 1
    I suggest you at least provide the table definition (CREATE TABLE). Partial indexes may help here. And add a tag with the DBMS you use (SQL-Server I suppose) and the version (2008, 2008R2, 2012, etc.) Sep 4, 2013 at 11:49
  • At a philosophical level - an empty string and NULL should never be considered the same thing.
    – RDFozz
    May 18, 2018 at 19:39
  • @RDFozz Why do you think I stopped using Oracle? (Well, one of the reasons, anyway). May 19, 2018 at 7:46

3 Answers 3


Do those columns have indexes on them at all? That is rather vital information for questions like this. If there is no index covering either of those columns at all then there is nothing it can do except table scan to match those filters.

If there is at least one index covering one (or both) that it could use, it may be deciding against it anyway due to the SELECT *. If the selectivity of the index suggests to it that many rows will match then a table scan may actually be more efficient than an index seek/scan followed plus row lookups to get the rest of the data that is not contained within the index.

For instance if I run

SELECT parent_id FROM org WHERE parent_id <> ''

on one of our systems it performs an index seek only. Running

SELECT * FROM org WHERE parent_id <> ''

performs a table scan instead. If I force the index choice with

SELECT * FROM org (WITH(INDEX(ix_org_parent_id) WHERE parent_id <> ''

it performs an index seek to locate the start of the records, then the RID lookups to find all the other column data. According to the query plan output it judges this to be ~6 times as costly as just doing the table scan, the estimated costs of the three queries above being 0.22926, 1.81429, and 11.1612 respectively. In this case the number of rows matching the WHERE clause is ~55% of ~50,000.

  • Followed your examples (table has covering indexes) and found the same as you. Table is approx 750,000 rows and found that you are right...the table scan was actually faster - so it seems that this is the most efficient way (and now I know why too!)
    – BlueChippy
    Sep 5, 2013 at 5:15

I assume you're using SQL Server (T-SQL reference there).

When you execute the T-SQL you can choose to include the actual execution plan, this will allow to see how SQL went about its execution, including whether a scan took place. Within this execution plan, SQL may even give hints on how to improve the performance which you may or may not decide to take up.

http://technet.microsoft.com/en-us/library/ms189562.aspx http://sqlserverplanet.com/optimization/execution-plans

Hope this helps!

  • I ran Ctrl-L/Estimated plan and it didn't suggest anything...trying Actual Plan now...same as Ctrl-L...just seems to be slow
    – BlueChippy
    Sep 4, 2013 at 11:35

Instead of using 'not equal' you could use 'greater than'.

    values (''), ('a'), ('b'), ('c')
) as t(c)
where c > '';

Without the predicate four rows are returned; with, three. This is the desired outcome.

This assumes a Western-ish collation. Otherwise have a test to see what is returned.

Of course the optimizer may still choose not to use the index depending on many criteria.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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