WHERE clause on NULLable columns, containg LIKE, keep index usage

Question

For a table with a column ColA as VARCHAR(x), how should I rewrite this condition

ColA LIKE @param + '%'

taking into account that:

I was thinking at:

ISNULL(ColA, '') LIKE (CASE WHEN @param IS NULL THEN '%' ELSE @param + '%' END)

but this will not make use of index defined on ColA.

If @param is null, should rows where colA is null be returned? Ie should nulls be considered "equal"? — Bohemian, Feb 14 '14 at 07:27
"There is an index on ColA, and it should be used for all searches" - well, it won't be. A `LIKE` condition with a leading wildcard can *not* use an index. — Damien_The_Unbeliever, Feb 14 '14 at 07:37
@Damien_The_Unbeliever: Oh, you are right. I'll rephrase this: Title + '%' — user3104183, Feb 14 '14 at 07:45

Bohemian · Answer 1 · 2014-02-14T08:40:25.490

1

Split into two conditions:

...
WHERE ((@param is null AND ColA is null)
  OR (@param IS NOT NULL AND ColA LIKE @param + '%'))

But OR often kills index usage, so if that doesn't work, try further split into two queries:

...
WHERE @param is null
AND ColA is null
UNION
...
WHERE @param IS NOT NULL
AND ColA LIKE @param + '%'

But

edited Feb 14 '14 at 08:40

answered Feb 14 '14 at 08:25

Bohemian

I think the second condition `@param IS NOT NULL` is needless. In case of `@param=NULL` we get `ColA LIKE NULL` and it's always FALSE. – valex Feb 14 '14 at 08:29
@valex *logically* it might be unnecessary, but *performance* may be far greater with it in because the entire LIKE search can be avoided when param is null. With the additional test, it may execute the like anyway (depending on optimizer) – Bohemian Feb 14 '14 at 08:43
I Agree it depends on optimizer. – valex Feb 14 '14 at 08:52
What is I have 2 columns, ColA, ColB, with 2 indexes? – user3104183 Feb 14 '14 at 10:48
Do you have a param for each, or do they share the same param? – Bohemian Feb 14 '14 at 10:51

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

Just use:

WHERE ((@param is NULL) and (ColA is NULL)) OR (ColA LIKE '%' + @param + '%')

But if you use LIKE '%' + @param + '%' index can't be used (if it isn't FULL-TEXT index):

edited May 23 '17 at 10:25

Community

answered Feb 14 '14 at 07:36

valex

It will return ColA=NULL only if @param is NULL. – valex Feb 14 '14 at 08:11
No it won't it will return every row when param is null – Bohemian Feb 14 '14 at 08:19
@Bohemian Seems I've misunderstood that question. I changed the answer. – valex Feb 14 '14 at 08:23

2 Answers2