Performance penalty for LIKE when I really mean Equal?

Hi,
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:

> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>