Advertisement
Advertisement


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");
2018/02/19
1
219
2/19/2018 10:20:21 AM


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

2014/03/01

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.

2015/05/29

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

2015/11/18

Source: https://stackoverflow.com/questions/7510646
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]