Before you can create a full-text index on a table, you have to determine whether the table has a unique, single-column, non-nullable index. The MSFTESQL (Microsoft Full-text Engine for SQL Server) service uses this unique index to map each row in the table to a unique, compressible key. To create a unique, single-column, non-nullable index, run the following T-SQL statement:
CREATE UNIQUE INDEX ui_<tablename> ON <Tablename>(<ColumnName>)
Now that you have a unique key, you can create a full-text index on the table as follows:
1. Select and expand “Database” node (for the database in question) in SQL Management Studio
2. Select and expand “storage” node.
3. Right-click on “Full Text Catalogs” and choose “new full text catalog”
4. Enter Catalog name using the convention shown in this example: “dbxxxx-ftc”
5. Browse to Catalog location: D:\MSSQL\FTDATA
6. Click “Okay”
Article ID: 451, Created: March 9, 2011 at 5:47 PM, Modified: March 9, 2011 at 5:47 PM