LIKE vs CONTAINS on SQL Server
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');
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.
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.