CONTAINS function in MS SQL 2008 R2

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 ?

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.

StackExchange Database Administrators Q#3560, answer score: 10