275

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");
Aldwoni
  • 1,168
  • 10
  • 24
user667429
  • 2,993
  • 2
  • 18
  • 15

5 Answers5

218

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');
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • @edze - you mean, the same page that is already linked to be my first mention of `CONTAINS`? What of it? The original form of the question had `Column CONTAIN("%test%",Column)>0` which was no-where close to valid. It's still not completely right. – Damien_The_Unbeliever Sep 22 '11 at 06:57
  • This helped us sort out a query on SharePoint. Have another Great Answer badge. – ouflak Apr 04 '17 at 16:10
19

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
MI C
  • 207
  • 2
  • 2
  • 10
    The clustered index leaf pages *are* the table. A `LIKE` query with a leading wildcard won't be able to use the index part efficiently. It will need to just scan the whole thing. Whilst doubtless there might be some circumstances in which the full CI scan performs better than a query using the full text index (perhaps if a very high proportion of rows match for example) this will largely be the exception not some general rule you "can confirm". – Martin Smith Mar 01 '14 at 19:52
  • Well am looking at an actual execution plan fetching over 200,000 records. Putting both queries in a batch, both scanned the clustered index, but in addition the "CONTAINS" query does have an added cost of FULL TEXT MATCH and a MERGE JOIN. – MI C Mar 01 '14 at 20:14
  • If it chooses a merge join then SQL Server is estimating more than x% of the rows will end up matching the predicate. (Where X = the [tipping point](http://www.sqlskills.com/blogs/kimberly/category/the-tipping-point/)). In that case I'd imagine both could end up quite evenly matched. The costs shown in the execution plan are just estimates (even in the actual plan). Whilst there are additional execution plan operators in the FT plan it does have some benefits. The merge join can stop before the end of the scan when it runs out of FT results and also it doesn't have to evaluate the `LIKE`. – Martin Smith Mar 01 '14 at 20:34
  • 1
    I have run a similar query to check the execution plan in sql 2012 and it gave me an Index Seek. Maybe in the example here the table was nearly empty. In some cases sql use an index scan in very small table instead to use the index because is faster. – Juan Apr 14 '14 at 17:31
9

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.

sqluser
  • 5,502
  • 7
  • 36
  • 50
Omri Valfer
  • 99
  • 1
  • 3
8

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

John Doe
  • 905
  • 8
  • 9
  • 5
    Does putting the asterisk before and after the search term work? In reading the documentation for `CONTAINS`, it only mentions using prefix terms like 'test*', not suffix terms like '*test' and not full substring searching like '*test*'. I have not tried it, though. – matt forsythe Jan 15 '16 at 21:10
  • 8
    If you read the documentation for CONTAINS (https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql), only searching for prefixes is supported. I have tried this experimentally numerous times and it is not possible to find "this is the greatest" (in SQL Sever) with Contains(Column, '"*test*"') – cl0rkster Sep 15 '17 at 14:39
  • Who votes this answer up if it doesn't work and why? – The incredible Jan Feb 10 '22 at 09:52
0

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
Muhammed Hanifi Dikmen
  • 13
  • 4
  • This doesn't help to answer the question. If you don't understand CONTAINS please check the documentation and feel free to ask a specific question in another post. – J. Chris Compton Oct 25 '22 at 18:54