Can CONTAINSTABLE be told which column to look in per search term?

Question

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.

What does your current query look like? You might be able to do this with a couple joins. — Brad D, Jan 14 '15 at 01:25

score 0 · Answer 1 · edited May 23 '17 at 10:24

You can try joining the CONTAINSTABLE to another CONTAINSTABLE.

SELECT * 
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]
WHERE.....

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.

score 0 · Accepted Answer · answered Mar 30 '15 at 04:21

0

The direct answer to my original question is: no.

answered Mar 30 '15 at 04:21

Ryan

7,733
10
61
106

Can CONTAINSTABLE be told which column to look in per search term?

2 Answers2

Linked