Can CONTAINSTABLE tell you which column to search for in each search term?


Based on my understanding, it looks like CONTAINSTABLE can be given a list of indexed columns to search within. It'll take the given search parameters and look for them in any of the specified columns.

Is it possible to look for "FOO" in column A, and "BAR" in column B only, using CONTAINSTABLE?

I'm trying to optimize a search query of mine, and it's running slow. It's a basic CONTAINSTABLE with additional CONTAINS predicates for additional column filters. I was trying to condense all of the searches into a single CONTAINSTABLE if possible.

You can try joining the CONTAINSTABLE to another CONTAINSTABLE.

FROM CONTAINSTABLE (schema.FullTextTable, ColumnA, 'FOO') as CT1
JOIN CONTAINSTABLE (schema.FullTextTable, ColumnB, 'BAR') as CT2 ON CT1.[KEY] = CT2.[KEY]
JOIN schema.FullTextTable as FT ON CT1.[KEY] = FT.[KEY]

However before you go down that path I might consider reading the following thread; Sql serve Full Text Search with Containstable is very slow when Used in JOIN! Basically it might help to grab a quick temp table of the key values from the CONTAINSTABLE queries then reach back into the source table to grab the additional data you need or do additional filtering.