If your sql server supports it, you can create an indexed view and full-text search, which; you can use containsstable (*, '"chris") to read all columns.
If it does not support it, since all fields come from different tables, I think for scalability; if you can easily fill in the fields in one row per record in a separate table, I would get a full text search, not separate records. As a result, you will have a less complex FTS directory, and your queries will not necessarily perform 4 full-text searches at the same time. Running many separate FTS queries on different tables at the same time is a ticket to asking for performance issues in my experience. The disadvantage of this is that you lose the ability to independently look for your last name; if this is what you need, you may have to look for an alternative.
In our application, we found that one table was faster (we cannot rely on clients having corporate sql at hand); therefore, we fill in the data with spaces in the FTS table via update sp, then our main contact search searches the list. We have two separate searches to handle the search for things with precision (e.g. names or phone numbers) or just for free text. Another nice thing in the table is the relatively easy and inexpensive way to add additional columns to the search (we were asked to provide a social security number, for example: for this, we just added a column to the SP update, and we were away with little or no impact.
u07ch
source share