Efficiency in Searching
There are many ways to implement a successful and efficient search interface for users, but several considerations must be kept in mind when setting up full-text catalogs and building standard queries for searching:
When you execute a call to the Search service with CONTAINS() or CONTAINSTABLE(), a separate result set is returned through the Search service to be joined with any other conditions to be applied. Consider the size of the catalogs involved and the tables catalogued.
It's not a good idea to have multiple search clauses in one SQL query. Each search clause will effect a separate catalog search, returning a result set that is not restricted by other conditions in the WHERE clause. Once all of the search results are back from the multiple catalog queries, the table joins and restrictions are applied. Make sure that any CONTAINS() or FREETEXT() functions use the asterisk (*) wildcard for the column argument, so that all searchable columns are hit at once.
If multiple tables need to be searched for one query, create an aggregate column in one table so that a full-text catalog only has to query and track one table. While this may add a little more maintenance on the back end, it also adds speed in retrieving results and makes the code between the user interface and the database simpler to maintain and enhance.