Is text to search for in the column_name. Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.
freetext_string is nvarchar. An implicit conversion occurs when another character data type is used as input. In the following example, the @SearchWord variable, which is defined as varchar(30), causes an implicit conversion in the FREETEXT predicate.
USE AdventureWorks;
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);
Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. In the example, declare @SearchWord as nvarchar(30).
USE AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);
You can also use the OPTIMIZE FOR query hint for cases in which a nonoptimal plan is generated.
Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word, or stopword, and will be discarded.
Use of WEIGHT, FORMSOF, wildcards, NEAR and other syntax is not allowed. freetext_string is wordbroken, stemmed, and passed through the thesaurus. If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed.