LIKE vs CONTAINS on SQL Server

Question

Which one of the following queries is faster (LIKE vs CONTAINS)?

SELECT * FROM table WHERE Column LIKE '%test%';

or

SELECT * FROM table WHERE Contains(Column, "test");

Answer 1

CONTAINS Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server.

CONTAINS can search for:

1. A word or phrase.
2. The prefix of a word or phrase.
3. A word near another word.
4. A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
5. A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

Note: CONTAINS is faster and will work if there is updated full-text index. If you want exact regular expression match then you can use like.

Answer 2

Also try changing from this:

    SELECT * FROM table WHERE Contains(Column, "test") > 0;

To this:

    SELECT * FROM table WHERE Contains(Column, '"*test*"') > 0;

The former will find records with values like "this is a test" and "a test-case is the plan".

The latter will also find records with values like "i am testing this" and "this is the greatest".

Answer 3

I think that CONTAINS took longer and used Merge because you had a dash("-") in your query adventure-works.com.

The dash is a break word so the CONTAINS searched the full-text index for adventure and than it searched for works.com and merged the results.

Answer 4

The second (assuming you means CONTAINS, and actually put it in a valid query) should be faster, because it can use some form of index (in this case, a full text index). Of course, this form of query is only available if the column is in a full text index. If it isn't, then only the first form is available.

The first query, using LIKE, will be unable to use an index, since it starts with a wildcard, so will always require a full table scan.

---

The CONTAINS query should be:

SELECT * FROM table WHERE CONTAINS(Column, 'test');

Answer 5

Having run both queries on a SQL Server 2012 instance, I can confirm the first query was fastest in my case.

The query with the LIKE keyword showed a clustered index scan.

The CONTAINS also had a clustered index scan with additional operators for the full text match and a merge join.