indexing expressions in sql

Question

I have a database where one of the common queries is has a "where blobCol is null", I think that this is getting bad performance (as in a full table scan). I have no need to index the contents of the blobCol.

What indexes would improve this? Can an index be built on an expression (blobCol is not null) rather than just a column?

Answer 1

Some databases allow indexes on expressions but a plain index should reduce the running time of your query significantly.

Answer 2

Yes, most DBMSs support it, for instance in PostgreSQL it is

CREATE INDEX notNullblob ON myTable (blobCol is not NULL);

It seems that the best you could do on SQL Server though is to create a computed column that, for example, will contain 1 if blob is null and 0 otherwise and create an index over that.