Full Text Search Step by Step Tutorial

Download and Install AdventureWorks for SQL Server 2008

Download AdventureWorks database: AdventureWorksDB.msi in Codeplex

Then double click your .msi file to Install your database.

By default it will install your database in
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your database will not appear automatically in your Object Explorer.

Go to Databases, right click then choose Attach...


If your database does not appear yet, refresh your Databases folder in the Object Explorer of your SQL Server Management Studio. To view your Object Explorer go to your menu bar then go to Views/Object Explorer or press F8.

Then you will see among your databases AdventureWorksDW.

Enable Full Text Search in Database

Create a new Full Text Catalog



A window will pop up to prompt for more details:
Full Text Catalog Name: FTCatalog

Create a new Full Text Index

Install Full Text Search
If your option is blocked (if it is not clickable) then re-run your SQL Server 2008 Installer. Under Installation (below Planning, on the left panel), choose New SQL Server stand-alone isntallation or add features to an existing installation.

Install Support Files.

Under Installation Type, choose Add features to an existing instance of SQL Server 2008.

Under Feature Selection check Full-Text Search.

Click Next then complete the installation. This will take around 5 minutes.

A Wizard window will appear after choosing Define Full-Text Index...

Follow the directions of the Wizard: choose the Primary Key for Indexing, then select all tables to enable full-text queries on all of them. Then choose the FTCatalog that we created previously when prompted. Skip Define population schedules for this tutorial. Then hit Finish.

Populate Index


Query Scripts - Test Full Text Search

Let's test our Full Text Search through the following SQL scripts:


On the new Query window, copy and paste the following scripts:

USE AdventureWorks
GO

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing OR Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing AND Assistant');
GO

Then Execute or F5 to run the query.


Notice that the results of the first SELECT query and the second SELECT query are the same.

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing OR Assistant');

The FREETEXT( ) query searches all job titles with either the string "Marketing" or "Assistant". The results are arranged according to their foreign keys.

By using the "AND" constraint on the third SELECT, only job titles with exact matches as the query string showed.

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing AND Assistant');

The arrangement of the results still depend on the foreign key (EmployeeID).

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

Source: http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/