COALESCE vs IS NOT NULL performance on checking empty string

Question

Some articles I found on the internet compared ISNULL with COALESCE, so I think my question is a little different.

I'm wondering which is better in terms of performance?

SELECT * FROM mytable WHERE mycolumn IS NOT NULL AND mycolumn <> '';

Or

SELECT * FROM mytable WHERE COALESCE(mycolumn,'') <> '';

Other than performance, are there any other concerns I should consider when deciding?

EDIT:

I'm using Teradata.

Answer 1

This version is slightly more sargable and allows an index to be (potentially) used

SELECT * FROM mytable WHERE mycolumn IS NOT NULL AND mycolumn <> '';

It can be simplified to

SELECT * FROM mytable WHERE mycolumn <> '';

The reason why I say "slightly" and "potentially" is that the non equality predicate may well mean you end up with a full scan anyway.

Answer 2

You might also consider using the ANSI_NULL ON setting. This will implicitly filter out null values on a column where you issue a search argument. Doing this simplifies your query, i'm not sure if it makes it faster. Logically in theory it should though, since less filter arguments need to be evaluated and no functions are being used in where clause which should enable full index selectivity. As an example you could re-factor your query like this:

SET ANSI_NULLS ON;
SELECT * FROM mytable WHERE NOT mycolumn = '';

I hope this helps.