Using Full Text Indexing

In this page I will explain how to create a full text index on large character columns, Filestream or XML.
Step 1: create a catalog
USE [my_db]

CREATE FULLTEXT CATALOG [proverbs]
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]

You can check this with:
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs

Here I have created a full text catalog called proverbs which will be stored in the database. If you are using SQL 2005 you can use the IN PATH N'd:\somepath' to specify it's location, this tag is depricated in SQL 2008. ACCENT_SENSITIVITY means that there will be a difference between café and cafe. The last line sets the dbo as owner.

Step 2: create an index
CREATE FULLTEXT INDEX ON [dbo].[proverbs]
KEY INDEX PK_proverbs ON proverbs
WITH CHANGE_TRACKING AUTO

This creates an index on the table [dbo].[proverbs] using the cluster index name PK_proverbs in the catalog proverbs created in step 1. Basically adds the table with the primary key to the catalog

Then add the column on which you want to search:
ALTER FULLTEXT INDEX ON [dbo].[proverbs] ADD ([proverb])

This enables fulltext indexing on the column proverb of the table [dbo].[proverbs].


Then enable the index which will do a rebuild of the index:
ALTER FULLTEXT INDEX ON [dbo].[proverbs] ENABLE

Step 3: test with a select
SELECT * FROM [dbo].[proverbs] WHERE CONTAINS ([proverb], 'something')

With the CONTAINS statement you can use keywords AND, OR, NEAR, and use wildcards like '*'