How to: Create Full-Text Indexes
You create a full-text index if you want to perform full-text searches on text-based columns in your database tables. A full-text index relies on a regular index, so you will need to create that first. The regular index must be created on a single, non-null column, and it is best to choose a column with small values rather than a column with large ones.
In SQL Server 2008, all databases are enabled for full-text indexing by default whenever users create them. Additionally, users automatically enable an individual table for full-text indexing when they create a full-text index on it and add a column to the index. Users automatically disable a table for full-text indexing when they drop the last column from its full-text index.
To create a full-text index, you must first create a catalog using an external tool such as SQL Server Management Studio or Enterprise Manager.To allow the index to include the textual data from Microsoft Office files (.doc, .xls, and .ppt files), text files (.txt files), and HTML files (.htm files), you must store those files in a field with the data type "image." For more information, go to MSDN on the Microsoft Web site, and search for "Full Text Search."
Note
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
To create full-text indexes
In Server Explorer, right-click the table for which you want to create a full-text index, and click Open Table Definition.
The table opens in Table Designer.
On the Table Designer menu, click Fulltext Index.
The Full-text Index dialog box opens.
Click Add.
Click the new index in the Selected Full-text Index list, and set properties for the index in the grid to the right.
For more information about how to set properties, see Full-Text Index Dialog Box.