LIKE vs CONTAINS on SQL Server

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");

The second (assuming you means [`CONTAINS`][1], 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');

[1]:

[To see links please register here]

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.

![Plan][1]


[1]:

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.

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*".

I didn't understand actually what is going on with "Contains" keyword. I set a full text index on a column. I run some queries on the table.
Like returns 450.518 rows but contains not and like's result is correct
```
SELECT COL FROM TBL WHERE COL LIKE '%41%' --450.518 rows
SELECT COL FROM TBL WHERE CONTAINS(COL,N'41') ---40 rows
SELECT COL FROM TBL WHERE CONTAINS(COL,N'"*41*"') -- 220.364 rows
```