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

User · Answer

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

User · Answer

Also try changing from this       SELECT   FROM table WHERE Contains Column   test    gt  0    To this       SELECT   FROM table WHERE Contains Column     test      gt  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

User · Answer

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

User · Answer

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

[sql-server] LIKE vs CONTAINS on SQL Server

Examples related to sql-server

Examples related to performance

Examples related to contains

Examples related to sql-like