January 17, 2011 at 2:09 am
Hi !
If i have a query in which i want to search a person name using replace function and i have index on the person name column .
Will the index be used in the query or the replace function will prevent that ?
SELECT *
FROM salesorder
where REPLACE(customer_full_name, ' ', '') like '%johndoe%'
How do i make the query faster in searching full name ?
the user keep complaining that it's really slow to search customer name
January 17, 2011 at 2:21 am
Both the replace and the leading % will prevent index seeks. The SELECT * will likely make SQL just scan the table as you're asking for every single column
As written there's not really any way you can make this query fast. Do you not have the first name and surname in separate columns? Do you need to let the user specify any portion of the name? Do you need every single column in the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply