Share via


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

The dialog boxes and menu commands that appear might differ from those described in Help, depending on your active settings or edition. To change your settings, open the Tools menu, and click Import and Export Settings. For more information, see Visual Studio Settings.

To create full-text indexes

  1. 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.

  2. On the Table Designer menu, click Fulltext Index.

    The Full-text Index dialog box opens.

  3. Click Add.

  4. 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.

See Also

Reference

Full-Text Index Dialog Box

Other Resources

Working with Indexes