Cannot Use a Contains or Freetext Predicate on Table or Indexed View Because It Is Not Full-Text Indexed

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

1. Make sure you have full-text search feature installed.

2. Create full-text search catalog (if needed)

   First check if any catalog already exists

     select *
     from sys.fulltext_catalogs
   

   If no catalog is found create one

     use [DatabaseName]
     create fulltext catalog FullTextCatalog as default
   

   you can verify that the catalog was created in the same way as above

3. Create full-text search index.

     create fulltext index on Production.ProductDescription(Description)
     key index PK_ProductDescription_ProductDescriptionID
   

   Before you create the index, make sure:
   
   - you don't already have full-text search index on the table as only one full-text search index allowed on a table
   
   - a unique index exists on the table. The index must be based on single-key column, that does not allow NULL.
   
   - full-text catalog exists. You have to specify full-text catalog name explicitly if there is no default full-text catalog.

You can do step 2 and 3 in SQL Sever Management Studio. In object explorer, right click on a table, select Full-Text index menu item and then Define Full-Text Index... sub-menu item. Full-Text indexing wizard will guide you through the process. It will also create a full-text search catalog for you if you don't have any yet.

You can find more info at MSDN

After following the steps you need a few minutes so that the full text search index is created (this depends on the size of the table and column data)

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view

You may alter a column to be unique if that's one that could be or add an id of some sort to do that part.

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view it is not full-text indexed

If you want to use CONTAINS the column needs to be full-text indexed.

If you don't, or can't, then you can search for data containing a . using LIKE

 where Location LIKE '%.%'

---

Related Topics