Indexing expressions in sql
I have a database where one of the common queries has "where blobCol is null", I think this leads to poor performance (as with a full table scan). I don't need to index the contents of blobCol.
What indexes will improve this? Can an index be built on an expression (blobCol is not null) and not just a column?
+1
source to share
2 answers
Yes, most DBMS support it, for example, in PostgreSQL it
CREATE INDEX notNullblob ON myTable (blobCol is not NULL);
It seems that the best thing you could do on SQL Server is to create a computed column that, for example, will contain 1 if the blob is null and 0 otherwise, and create an index on it.
+2
source to share