SQL Server Full-Text-Search FREETEXTTABLE search multiple columns

Question

I'm using the below query to return results from a table using Full-Text-Search. In SQL2000 it was only possible to search one or all columns in a table. Is it possible in SQL 2008?

I would like to search two tables, Problem and Solution (Both indexed and in the same table):

DECLARE @topRank int set @topRank=(SELECT MAX(RANK) 
FROM FREETEXTTABLE([Support_Calls], Problem, 'test', 1)) 
SELECT [ID] AS [Call No],Company_Name, Problem, Solution, CONVERT(VARCHAR(20),CAST((CAST(ftt.RANK as DECIMAL)/@topRank * 100) AS DECIMAL(13,0))) + '%' as Match 
FROM [Support_Calls] INNER JOIN FREETEXTTABLE([Support_Calls], Problem, 'test') as ftt ON ftt.[KEY]=[ID] ORDER BY ftt.RANK DESC;

From what I can see the FREETEXTTABLE does not accept more than one column?

Solution 1

You specify them in parentheses; FREETEXTTABLE(tablename, (col1,col2,col3), 'expr') or use an asterisk to seach all columns in the index.

Solution 2

From MSDN,

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement like a regular table name. Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row.

They give the following syntax:

FREETEXTTABLE (table , { column_name | (column_list) | * } 
          ,'freetext_string' 
     [ , LANGUAGE language_term ] 
     [ ,top_n_by_rank ] )

So yes, what Alex K. said as well.

Comments

[Tobiasopdenbrouw]

Won't the asterisk hurt performance, though, if used without discretion?

[Alex K.]

Yes indeed, if there is a column in the index that you don't care about.

[Rich]

How would i do if I need only a portion of the word but still will display the full word? Like if the search value is Curt, the search result would be Curtain, Curtis, Curtman...

[Germán Martínez]

@Rich You would put '"Curt*"' in the @SearchTerm - * are wildcards but they must be in double-qoutes and you can only do this with CONTAINS not FREETEXT