Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I try the following SQL query :

SELECT FirstName, SecondName FROM Persons WHERE CONTAINS(FirstName, 'Mat')

and I got this error :

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

Does I need to index the FirstName column ? What's wrong ?

share|improve this question

1 Answer

up vote 7 down vote accepted

If you want to use the CONTAINS or FREETEXT you need to create a FULL TEXT CATALOG and create a FULL TEXT INDEX on the column. However, you can use the LIKE statement to validate if a column "contains" a specific string.

SELECT FirstName, SecondName FROM Persons WHERE FirstName LIKE '%Mat%'

You must be aware that the LIKE is slower than the contains but need less configuration. If you want to use the FULL TEXT CATALOG, you can use this link.

Hope this help you.

share|improve this answer
2  
The only point I'll add is to be aware of the performance implications of both approaches. For the LIKE approach, SQL Server will have to scan every row when you use LIKE '%name%', but you may get away with range scans when you use LIKE 'Na%' assuming you have an index on the column. There is other overhead associated with the Full text indexing which I won't get into here, I just recommend you read up on both and test them to see what does (and will as data volumes grow) perform best :) – AndrewSQL Jun 29 '11 at 16:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.