phone book lookup query

[Shoval Tomer]

Hi,
Can you recommend the best way (fast and most productive) to search an
employees table?

Let's say I have a table that has this kind of structure:
firstname, lastname, state, city, street, phonenumber,
socialsecuritynumber, dateofbirth, etc.

I'd like to provide a search window with one text entry field, that
the text entered will be searched at all fields, and even in more then
one field toghether (like if you enter "david z" it will search for
like '%david%' in first name and like '%z%' in last name).

the problem is I can't write the query that'll perform fast but search
all of these fields, plus there's no way to use an index when using
LIKE.

I can't loose the functionality described above (I'm not the client,
and not the project manager) but I am trying to find an efficient
solution to the problem.

Since most systems has some sort of directory table (like authors in
pubs) you would think it will be a classic problem, but I found no
recommendations on how to approach it.

any help will be most appreiciated.

[-P-]

"Shoval Tomer" <sh****@softov.co.il> wrote in message news:71**************************@posting.google.c om...

Hi,
Can you recommend the best way (fast and most productive) to search an
employees table?

Let's say I have a table that has this kind of structure:
firstname, lastname, state, city, street, phonenumber,
socialsecuritynumber, dateofbirth, etc.

I'd like to provide a search window with one text entry field, that
the text entered will be searched at all fields, and even in more then
one field toghether (like if you enter "david z" it will search for
like '%david%' in first name and like '%z%' in last name).
the problem is I can't write the query that'll perform fast but search
all of these fields, plus there's no way to use an index when using
LIKE.

That's implying a "CONTAINS" - are you sure you don't want "STARTS WITH"?

"and first_name like 'david%'
and last_name like 'z%' "

will use an available index on first or last name. If you're obligated to use "contains", then you're correct - the
optimizer won't use an index for those.

--
Paul Horan
Sr. Architect
VCI Springfield, MA
www.vcisolutions.com

[Erland Sommarskog]

Shoval Tomer (sh****@softov.co.il) writes:

Can you recommend the best way (fast and most productive) to search an
employees table?

Let's say I have a table that has this kind of structure:
firstname, lastname, state, city, street, phonenumber,
socialsecuritynumber, dateofbirth, etc.

I'd like to provide a search window with one text entry field, that
the text entered will be searched at all fields, and even in more then
one field toghether (like if you enter "david z" it will search for
like '%david%' in first name and like '%z%' in last name).

the problem is I can't write the query that'll perform fast but search
all of these fields, plus there's no way to use an index when using
LIKE.

I can't loose the functionality described above (I'm not the client,
and not the project manager) but I am trying to find an efficient
solution to the problem.

Since most systems has some sort of directory table (like authors in
pubs) you would think it will be a classic problem, but I found no
recommendations on how to approach it.

I have a longer discussion on http://www.sommarskog.se/dyn-search.html.

As for LIKE, you may consider adding clues that the user can add:
"Starts with", "contains". In that way you can control whether the leading
% has to be there. This is probably also good for usability, since
it can help the user to cut down the number of hits.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp