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:
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.
asked 20 Apr '10, 19:04
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
may help with cardinality estimation if the query contains additional joins, which may well yield a better plan.
answered 21 Apr '10, 00:36