Posted by Erland Sommarskog on 08/30/05 00:32

--CELKO-- (jcelko212@earthlink.net) writes:
> SQL programmers tend to trust the optimizer rather than write programs
> that change on the fly. We also like the easiest to read form of
> identical expressions:
>
> county LIKE COALESCE (@my_county, '%')
>
> Go with this and let the compiler figure out if the parameter is a
> NULL, a string or a pattern. The LIKE predicate generates a simple
> finite state machine to parse a string using the pattern given. The
> state machine for '%' is very fast.

On SQL 2000 this is a very poor advice. The query will table scan in
all cases. It should scan if @my_count is NULL of course. The optimizer
does not know when it builds the plan which value @my_county will have,
so it must have a plan that handles any value.

In SQL 2005 you can add the query hint

OPTION (RECOMPILE)

to get statement recompilation of that query only. In this case, it
will pick the plan which matches the value of @my_county best - or at
least I expect it two.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]