Those pesky empty strings (or should they be NULLS)

[justin_willey]

In situations where a varchar field could get contaminated with empty strings, does it make any difference from a performance point of view whether you go for:

... WHERE fieldname is not null and fieldname !=''  

OR

... WHERE isnull(field,'') !=''   

OR

... something better maybe  

As far as index usage (asuming there is an index on fieldname), maybe the first is better especially as there's no function call involved - or does it all depend on the data? Presumably the ISNULL() isn't sargable, but then neither is IS NOT NULL I think?

This is often a situation where there are a lot of nulls in fieldname (say 50%) and the possibility of a few empty strings.

[Former Member · Accepted solution]

You are correct that IS NOT NULL is not currently sargable, nor is the ISNULL() function. ISNULL is worse than IS [NOT] NULL in that its selectivity estimate is a raw guess, whereas for IS [NOT] NULL the server will utilize the column's histogram for an accurate estimate.

Since neither IS NOT NULL nor ISNULL() is sargable, to a significant extent there is no advantage in performance of one over the other. However, because of better estimates, using the conjunction

WHERE fieldname is not null and fieldname !='' 

may help with cardinality estimation if the query contains additional joins, which may well yield a better plan.

[justin_willey · Accepted solution]

Many thanks Glenn - that's very clear

[VolkerBarth · Accepted solution]

@Glenn: You say "not currently sargable" - what are the plans to change this behaviour? Is it planned for Innsbruck?

[Former Member · Accepted solution]

We are looking at making IS NOT NULL sargable. No promises as to when that functionality will be added.

[Former Member · Accepted solution]

very interesting.... I have an application that looks for null values in column (which is indexed), and processes those records accordingly, and in so doing, it UPDATEs the NULLs with values. If I understand what 'sargable' means, it means that regardless of the index, that my table is going to be read sequentially looking for records with a NULL value.

Since looking for records with IS NULL is not sargeable, It seems to me that the default value for my column (currently NULL) should really be an empty string, so that the db engine can find those records quickly, and therefore my application responds faster.

Am I right here?

[VolkerBarth · Accepted solution]

As to the docs, IS NULL is sargable - and has been with v8 (and possibly even before). So I do not think switching to empty strings would be faster in your situation.

For 10.0.1, cf. the docs.

[former_SQLA_member1694882 · Accepted solution]

@Glenn: As "IsNull(AField, '')" is so much easier to write and read than "(if AField is null then '' else AField endif)" is there a possibility for the query engine to recognise IsNull(...) and rewrite it to the longer form so that IsNull is able to take advantage of the IS [NOT] NULL sargability?

[Former Member · Accepted solution]

IS NULL has been a sargable predicate since at least Watcom SQL 3.2.

[VolkerBarth · Accepted solution]

...that's even before you got your hands on the product, right? 🙂

[johnsmirnios]

IS NOT NULL was made sargable in 12.0.1.

[VolkerBarth]

That's good news - is there any reason this is not documented as a behaviour change (unless I have overseen this)?

[Former Member]

IS NOT NULL is listed as a sargable predicate in

SQL Anywhere Server - SQL Usage » Query optimization and execution » How the optimizer works » Using predicates in queries

However, that change does not appear to have made it to the list of behaviour changes in the "What's new" section for the 12.0.1 release.

[VolkerBarth]

Yes, that's the doc page John has linked to:) - Something to add to the DCX "What's new" page?