Global Search Criteria - 1 criteria for multiple columns

I am curious of other's opinions on this. It may be more a UI issue than a
database one, but there is a database side to it, and I have seen that folks
here have a broader knowledge base than just proper table structures, so...
I have an application where we need to search for an employee. You can
search on name, employee ID, or department. The determination has been made
to allow the user to enter one value, and then search for the value in any
of the above columns.
i.e.
employee_id = @.SearchString
or
employee_name like '%' + @.SearchString + '%'
or
department_name like '%' + @.SearchString + '%'
The question is, what has your experience been with this approach, from both
a database perspective, and an end user / UI perspective.One technique in such situations is to use a computed column like:
CREATE TABLE tbl (
...,
employee_id ...,
employee_name...,
department_name...
comp_col AS ( employee_id + employee_name + department_name ) );
and create an index on this comp_col column.
Now change your search pattern in your query to use comp_col LIKE '%' + @.p +
'%'. Depending on your usage you can create a covering index on all the
three columns & avoid the computed column altogether as well.
Anith|||Thanks, I'll give that approach a try to help performance.
Does anyone have any non-database related feedback on this approach?
Usability, clarity to the end user, etc?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
+
> '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
> + '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>
Anith,
Could you explain the performance advantage to using either the computed
column or the covering index compared to just a straight where clause (e.g.
WHERE employee_id + employee_name + department_name LIKE '%' + @.p + '%' ) ?
I'm not knocking the approach, I would just like to understand. Also, it
seems as though once you use the LIKE clause, any advantage to an index
disappears.
Thanks,
Kevin|||I think I messed this one up :-( You are right, the wild card in the
beginning of the parameter will nullify the index usage.
Anith