SARG

Is the below Where clause is sargable?
Is using the function ISNULL makes it non-sargable?
Create proc SearcCustomer
@.FirstName varchar(30) = null,
@.LastName varchar(30) = null,
@.Address varchar(30) = null
as
SELECT * from customers
WHERE
((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
AND
((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
AND
((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
I have non-clustured index on all the columns. Please advise if I can
write this in a better way. The Input can be any of the three
parameters or multiple.
Thanks
ShijuHi
"shiju" wrote:
> Is the below Where clause is sargable?
No
> Is using the function ISNULL makes it non-sargable?
Yes columns involved in an expression are not SARGable and also the '%' +
@.variable + '%' isn't (BTW you code does not parse because of +'@.address!! )
> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
If you SET SHOWPLAN_TEXT ON and then execute the procedure you will know if
it is using any indexes, this procedure will do a clustered index scan or a
table scan if there is no clustered index
Ken Handerson's book The Gurus Guide to Transact-SQL ISBN 0201615762 has a
good section on SARGs
Also look at
http://www.sommarskog.se/dyn-search.html
> Thanks
> Shiju
>
John|||> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
To add to John's response, applying a function to a column will render that
expression non-sargable. However, the ISNULL function doesn't matter here
because a variable is used. The real culprit is the LIKE expression.
If you eliminate the leading '%' from the LIKE expression, the indexes can
be used more efficiently. Also, I would use IS NULL instead of ISNULL here
for readability. For example:
WHERE
(@.FirstName IS NULL OR FirstName LIKE '@.FirstName+'%')
AND
(@.LastName IS NULL LastName LIKE '@.LastName+'%')
AND
(@.Address IS NULL OR Address like '@.Address'+'%')
You might also read Erland's dynamic search condition article
(http://www.sommarskog.se/dyn-search.html).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1177835585.469292.300480@.l77g2000hsb.googlegroups.com...
> Is the below Where clause is sargable?
> Is using the function ISNULL makes it non-sargable?
> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
> Thanks
> Shiju
>