Using Full Text Search in SQL Server 2008
Create a Full-Text Index
Once you have successfully created a Full-Text Catalog, the next step is to create a Full-Text Index. In this example, we will be creating a Full-Text Index on the HumanResources.Employee table of the AdventureWorks database. Note that you can create only one full-text index on a particular SQL Server table.
1. Right click the HumanResources.Employee table and select Full-Text Index and choose Define Full-Text Index… as shown below to open the Full-Text Indexing Wizard.
2. In the Welcome to the SQL Server Full-Text Indexing Wizard screen click Next to continue with the configuration.
3. In the Select an Index wizard screen, you will be shown a list of all the valid indices which are available for the HumanResources.Employee table under the Unique Index drop down list shown below . In this example select the PK_Employee_EmployeeID index and click Next to continue with the wizard.
4. In the Select Table Columns screen, you will be shown a list of all the columns which have character-based or image-based data types and which are eligible for full-text queries. Select all the columns individually and also choose the desired Language for Word Breaker. In this example use English as the language for Word Breaker. Note that Full-Text Search in SQL Server 2008 supports more than 50 different languages. Click Next to continue.
5. In the Select Change Tracking wizard screen, select a change tracking method (see below). When you define automatic or manual change tracking, the full population of the index starts immediately once the wizard is complete. To avoid a full population at the end of the wizard you need to choose Do not track changes option and uncheck the Start full population when index is created check box as shown below. Click Next to continue.
6. In the Select Catalog, Index File Group, and Stop List screen, select the AdventureWorksFullTextCatalog as the full text catalog, this is the catalog created earlier. Select the index Filegroup as PRIMARY and leave rest of the settings unchanged and click Next to continue with the wizard.
7. In Define Population Schedules (Optional) screen, set the schedule to populate the full-text index. To manually populate the full-text index leave this screen as it is and click next to continue.
8. In the Full-Text Indexing Wizard Description screen, you will be able to see a quick summary of the options which you have selected so far. Click Finish to complete the configuration of the Full-Text Index.
9. You will be shown a success message once the full-text index is configured successfully :