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

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.

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:

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%'

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

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

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

will act as % same as

where [Column] Like 'A00%'

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