I am getting following error in my SQL server 2008 R2 database:
Cannot use a
CONTAINS
orFREETEXT
predicate on table or indexed view 'tblArmy' because it is not full-text indexed.
Expand Tables, and right-click the table that you want to disable or re-enable for full-text indexing. Select Full-Text index, and then click Disable Full-Text index or Enable Full-Text index.
Full-text indexes are created on text-based columns ( CHAR , VARCHAR , or TEXT columns) to speed up queries and DML operations on data contained within those columns. A full-text index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX .
Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on the rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.
If “Full-Text Indexing” is greyed out it means that full-text indexing is not enabled.
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
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)
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%'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With