100

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.

Alex Aza
  • 70,453
  • 24
  • 147
  • 129
DotnetSparrow
  • 25,248
  • 60
  • 171
  • 307

7 Answers7

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

Full-Text Search setup

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

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

enter image description here

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)

Mauricio Gracia Gutierrez
  • 8,130
  • 4
  • 51
  • 77
Alex Aza
  • 70,453
  • 24
  • 147
  • 129
  • 1
    Good information Alex! NOTE: for those using Azure SQL, it does not support the Contains Table as of right now. See here: http://msdn.microsoft.com/library/azure/ee336253.aspx – Termato Jul 15 '14 at 15:14
  • Also, if using SQL Express Advanced Services, see here to create a Full Text Index: http://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index – Termato Sep 08 '14 at 19:05
76

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%'
Mohammad Sepahvand
  • 16,889
  • 20
  • 78
  • 119
  • 2
    [link](http://msdn.microsoft.com/en-us/library/ms142571.aspx). 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. – Manolis May 23 '13 at 06:42
  • 3
    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! – Christine Jan 11 '17 at 18:57
21

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: enter image description here

Ashraf Sada
  • 3,511
  • 1
  • 36
  • 43
  • That's really clever – WonderWorker Feb 11 '16 at 10:20
  • 6
    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. – caesay Nov 06 '17 at 06:36
3

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

mellamokb
  • 53,762
  • 11
  • 101
  • 131
  • 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 ? – DotnetSparrow May 14 '11 at 16:48
  • 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. – Tom H May 14 '11 at 17:05
  • @Hi Tom: I didnt see option to add features when I run the SQL server 2008 R2 installer. – DotnetSparrow May 14 '11 at 17:08
1

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

Icet
  • 632
  • 2
  • 11
  • 29
1

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

SimonQuest
  • 632
  • 1
  • 5
  • 17
0
Select * from table
where CONTAINS([Column], '"A00*"')

will act as % same as

where [Column] Like 'A00%'
Paul Roub
  • 35,100
  • 27
  • 72
  • 83
Dnyanesh Mijagiri
  • 17
  • 1