Rewrite a WHERE clause

I have a WHERE clause that could be an "=" or a "LIKE" depending upon
if the passed variable is populated or not. I would like to know the
best way to write the WHERE clause to make it dynamically switch
between the 2 and make best use of the indexes.

CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))
CREATE INDEX IDX_myTable_County ON myTable(COUNTY)

DECLARE @.COUNTY VARCHAR(50)
SET @.COUNTY = 'SANTA CLARA' -- Could also be SET @.COUNTY = NULL

SELECT ID FROM myTable
WHERE COUNTY LIKE (CASE WHEN @.COUNTY IS NOT NULL THEN @.COUNTY ELSE '%'
END)

This does not seem like best practice to me because I am forced to use
"LIKE" even when @.COUNTY is populated with data. Ultimately I'd like:

WHERE (CASE WHEN @.COUNTY IS NOT NULL COUNTY = @.COUNTY ELSE COUNTY LIKE
'%' END)

but that is incorrect syntax on "=".

Also, I do not want to use a dynamically built statement. Is there a
way around this?

Thanks,
JoshHello, Josh

See this article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/dyn-search.html#Umachandar
There are some solutions in the article that are not using Dynamic SQL.

Razvan|||Josh,

You might try this, if your data is all typical alphabetic
values in English (no letters of the alphabet that come
after Z). I'm also assuming your collation is case-insensitive.

where COUNTY >= coalesce(@.COUNTY,'A')
and COUNTY <= coalesce(@.COUNTY,'ZZZZZZZZZZZZZZZZZZZ')
-- as many Z's as the declared length of the COUNTY column

This may be more efficient than your LIKE solution, but the
only way to be certain is to do some comparisons. You also
run the risk in situations like this of getting bad cached
query plans, at least if your actual query selects more than
the ID column, since the best query plan for @.COUNTY = NULL
is a table scan and the best query plan for @.COUNTY <> NULL
is a non-clustered index seek followed by a bookmark lookup.
If the second plan is cached and used later when @.COUNTY is
NULL, it will be very inefficient. A way around this, should
it occur, may be to add something to force query recompilation.
In a stored procedure, that might be adding WITH RECOMPILE,
or in an sp or otherwise, adding something to the query that
will prevent autoparameterization (adding AND 1 = 1 to the
WHERE clause will do this, I believe)

Maybe that's more than you needed to know, but your question
suggests you are thinking about some important considerations
in query design.

Steve Kass
Drew University

joshsackett wrote:
> I have a WHERE clause that could be an "=" or a "LIKE" depending upon
> if the passed variable is populated or not. I would like to know the
> best way to write the WHERE clause to make it dynamically switch
> between the 2 and make best use of the indexes.
> CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))
> CREATE INDEX IDX_myTable_County ON myTable(COUNTY)
> DECLARE @.COUNTY VARCHAR(50)
> SET @.COUNTY = 'SANTA CLARA' -- Could also be SET @.COUNTY = NULL
> SELECT ID FROM myTable
> WHERE COUNTY LIKE (CASE WHEN @.COUNTY IS NOT NULL THEN @.COUNTY ELSE '%'
> END)
> This does not seem like best practice to me because I am forced to use
> "LIKE" even when @.COUNTY is populated with data. Ultimately I'd like:
> WHERE (CASE WHEN @.COUNTY IS NOT NULL COUNTY = @.COUNTY ELSE COUNTY LIKE
> '%' END)
> but that is incorrect syntax on "=".
> Also, I do not want to use a dynamically built statement. Is there a
> way around this?
> Thanks,
> Josh|||Thanks to both of you for your suggestions. I will try your methods and
see which is actually faster and which grabs the correct query plan
every time.|||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.|||I would keep things simple

if @.COUNTY is NULL
--- scan the table
select * from some_table
else
--- might use an index
select * from some_table where conty = @.county
end if

this way you'll have 2 precompiled plans for 2 different cases|||--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/techin.../2000/books.asp|||just add "= 1" to the end

here is a working example:

where (case when @.arg is null then
case when
[property_id] = 1 //put your test when @.arg is null here
then 1 else 0 end
else
case when
[property_id] = 2 //and your other one here
then 1 else 0 end
end) = 1|||to explain

we are using case when <boolean> then 1 else 0 end

to get around sql servers strict handling of boolean type

then converting back from 1 > true and 0 > false at the end

you can't directly use "where case (blah)" because case doesn't return
a boolean value
but you can use "where case (blah) = 1" to convert the value coming out
of case to a boolean value|||I thought that 2005 was geting a "multi-plan" feature like DB2. It
actually saves multiple exection plans and effectively does what AK
proposed, but without you having to tell it. Do you know if the OPTION
(RECOMPILE) discards or saves prior plans?|||Morgan:
I don't understand quite what you mean. Can you use my query above and
fit it to your example?

Thanks,
josh|||--CELKO-- (jcelko212@.earthlink.net) writes:
> I thought that 2005 was geting a "multi-plan" feature like DB2.

That does not sound familiar. (Actually, already SQL 2000 have multiple
plans for a query, but this is related to the setting of SET options.)

> It actually saves multiple exection plans and effectively does what AK
> proposed, but without you having to tell it. Do you know if the OPTION
> (RECOMPILE) discards or saves prior plans?

Since the hint instructs SQL Server to recompile the query each time, it
would be pointless to save the plan. (Compare stored procedure saved
WITH RECOMPILE; the plans for these are never in cache.)

OPTION (RECOMPILE) should be great for these dynamic search queries
where the user specifies the last name on one search and the next time
the shoe size.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||joshsackett (joshsackett@.gmail.com) writes:
> Morgan:
> I don't understand quite what you mean. Can you use my query above and
> fit it to your example?

What he said is that:

WHERE (CASE WHEN @.COUNTY IS NOT NULL COUNTY = @.COUNTY ELSE COUNTY LIKE
'%' END)

does not work, because you have mixed the CASE expression with the WHERE
condition. You could say:

WHERE COUNTY = CASE WHEN @.COUNTy IS NOT NULL THEN @.COUNTY ELSE COUNTY END

or briefer:

WHERE COUNTY = coalesce(@.COUNTY, COUNTY)

(coalesce is just syntactic sugar for CASE WHEN x IS NOT NULL THEN x WHEN
...)

Or, as said earlier in the thread you could go for some elaborate trick for
dynamic searches.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"--CELKO--" <jcelko212@.earthlink.net> writes:
>I thought that 2005 was geting a "multi-plan" feature like DB2. It
>actually saves multiple exection plans and effectively does what AK
>proposed, but without you having to tell it. Do you know if the OPTION
>(RECOMPILE) discards or saves prior plans?

I think I've found what you are thinking of. You can store a "plan guide"
for a query. But this is fairly limited, as all you can do is to specify
an OPTION clause for the query. So for instance, you cannot force an
index this way.

You can play with parameters, so maybe you can get differnt plans for
different parameters, although I did not study it that deep.

It seems to me that the main use for this is when you have queries in a
third-party app that you cannot change, but which perfoms poorly.

--
Erland Sommarskog, Stockholm, esquel@.sommarskog.se|||Erland Sommarskog (esquel@.sommarskog.se) writes:
>"--CELKO--" <jcelko212@.earthlink.net> writes:
>>I thought that 2005 was geting a "multi-plan" feature like DB2. It
>>actually saves multiple exection plans and effectively does what AK
>>proposed, but without you having to tell it. Do you know if the OPTION
>>(RECOMPILE) discards or saves prior plans?
>I think I've found what you are thinking of. You can store a "plan guide"
>for a query. But this is fairly limited, as all you can do is to specify
>an OPTION clause for the query. So for instance, you cannot force an
>index this way.
>You can play with parameters, so maybe you can get differnt plans for
>different parameters, although I did not study it that deep.
>It seems to me that the main use for this is when you have queries in a
>third-party app that you cannot change, but which perfoms poorly.

Oops! There was even more to it!

There is a new query hint USE PLAN. With this hint you can specify the
entire query plan. And since you specify USE PLAN in the OPTION clause,
it follows from this that you can indeed store an entire query plan
for a query. Since there is some parameterisation stuff with plan
guides as well, you might also be able to save more than one plan. I
have not dug into that yet.

--
Erland Sommarskog, Stockholm, esquel@.sommarskog.se