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

Hello, 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/techinf...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?