Multiple columns with ContainsTable and boolean logic with full text index

Question

I have what I think is a very basic scenario, but what I've read makes it sound like this is not easy using SQL Server Full Text catalog and indexes.

I have 2 columns, First and Last name. I want to support full-text search on them such that if someone types "John Smith" people with a match on both first and last come up first.

Although it's easy to create an index across multiple columns, and easy to search multiple columns, the scoring doesn't reflect multiple columns.

SELECT [Key], Rank 
FROM CONTAINSTABLE([User], (FirstName,LastName), '<CLAUSE_HERE>')

1. If CLAUSE_HERE is "john smith" I get no results, because that phrase does not exist in either field.
2. If it's "john OR smith" I get all users with either name in either field, sorted in an unhelpful order.
3. If it's "john AND smith" I get no results, because neither field contains both words.

Seems like the only solution is to autogenerate a query that runs containstable on each field, does some math, sums the scores, etc. Does that sound right? Is there an easier way around it? My actual query has a lot more fields to it - this is a simplified example.

Answer 1

Make a computed column which mushes together the fields that you're interested in searching on (in a way that makes sense for your search formats), and full-text index that.

As far as I'm aware this is the only work around if you want to full-text in this way because of the behavior you describe in your question.