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

Question

I am getting following error in my SQL server 2008 R2 database:

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

Georgenegeorges

Have you actually created a full text catalog? – Agenesis

Answer 1

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)

Prophylaxis

Good information Alex! NOTE: for those using Azure SQL, it does not support the Contains Table as of right now. See here: msdn.microsoft.com/library/azure/ee336253.aspx – Jijib

Also, if using SQL Express Advanced Services, see here to create a Full Text Index: #10407837 – Jijib

Answer 2

A workaround for CONTAINS: If you don't want to create a full text Index on the column, and performance is not one of your priorities you could use the LIKE statement which doesn't need any prior configuration:

Example: find all Products that contains the letter Q:

SELECT ID, ProductName
FROM [ProductsDB].[dbo].[Products]
WHERE [ProductsDB].[dbo].[Products].ProductName LIKE '%Q%'

Yonyona

link. LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. – Spoondrift

Ah, sweet... the easy, round-about way that doesn't require you to dig out your installer and go screwing around with your environment settings. Well done! – Coverage

Answer 3

You must define Full-Text-Index on all tables in database where you require to use a query with CONTAINS which will take sometime.

Instead you can use the LIKE which will give you instant results without the need to adjust any settings for the tables.

Example:

SELECT * FROM ChartOfAccounts WHERE AccountName LIKE '%Tax%'

The same result obtained with CONTAINS can be obtained with LIKE.

see the result:

Invincible

That's really clever – Tical

This is a nice trick, but the way this answer is phrased is wrong. Don't "just use" it. There are some serious performance implications for doing this and anyone putting this into a production system should think very carefully about doing full table scans like this on a non-indexed column. – Pipeline

Answer 4

You might need to enable the table for full-text indexing.

Guitarfish

enable/disable Full text search is grayed out in context menu. I guess I need to install Full text search. How can I install full text search ? – Georgenegeorges

Run the install for SQL Server and there should be an option for changing installed components (or something similar). Click that then check the Full Text check box at the appropriate time and you should be good. Sorry, I don't have one handy or I'd give more specific instructions. – Alcoran

@Hi Tom: I didnt see option to add features when I run the SQL server 2008 R2 installer. – Georgenegeorges

Answer 5

There is one more solution to set column Full text to true.

These solution for example didn't work for me

ALTER TABLE news ADD FULLTEXT(headline, story);

My solution.

1. Right click on table
2. Design
3. Right Click on column which you want to edit
4. Full text index
5. Add
6. Close
7. Refresh

NEXT STEPS

1. Right click on table
2. Design
3. Click on column which you want to edit
4. On bottom of mssql you there will be tab "Column properties"
5. Full-text Specification -> (Is Full-text Indexed) set to true.

Refresh

Version of mssql 2014

Macula

Answer 6

you have to add fulltext index on specific fields you want to search.

ALTER TABLE news ADD FULLTEXT(headline, story);

where "news" is your table and "headline, story" fields you wont to enable for fulltext search

Rasmussen

Answer 7

Select * from table
where CONTAINS([Column], '"A00*"')  

will act as % same as

where [Column] Like 'A00%'

Asteroid