SQL Server 2005 Full-Text Search

SQL Server 2005 comes with its own full-text search capabilities, which enables developers to use full-text search functions and predicates, provided that the correct full-text search catalog and indexing exist.

Full-text functions and predicates include:

1. CONTAINS (predicate)
2. FREETEXT (predicate)
3. CONTAINSTABLE (function)
4. FREETEXTTABLE (function)

Here are some of their characteristics and differences:

CONTAINS and FREETEXT both return a TRUE or FALSE value and are specified in the WHERE or HAVING clauses of a SELECT statement.

CONTAINSTABLE and FREETEXTTABLE both return a table of zero, one, or more rows, so they must always be specified in the FROM clause.

CONTAINS and FREETEXT can only be used to specify selection criteria, which Microsoft SQL Server uses to determine the membership of the result set, ie, inside or outside the result set.

CONTAINSTABLE and FREETEXTTABLE are also used to specify selection criteria. The table returned has a column named KEY that contains full-text key values. Each full-text registered table has a column whose values are guaranteed to be unique. The values returned in the KEY column of CONTAINSTABLE or FREETEXTTABLE are the unique values, from the full-text registered table, of the rows that match the selection criteria specified in the full-text search condition.

Here are some example full-text queries(extracted from MSDN):

1. SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');


2. DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);

3. SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

4. SELECT K.RANK, CompanyName, ContactName, Address
FROM Customers AS C
INNER JOIN
CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9))', 3) AS K
ON C.CustomerID = K.[KEY];


5. SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];

Note that full-text search is different from LIKE predicate. With the LIKE predicate, you will get exact matches, whereas in full-text search, you can specify certain ambiguity.

Also, for some far eastern characters, such as Chinese, Japanese...etc., SQL Server has some language-specific features to generate more desirable results.

Reference: http://msdn2.microsoft.com/en-us/library/ms142519.aspx