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

Prosopopoeia

Accept an answer, would you? – Booking

He hasn't been on in years man. – Goodly

A better question to ask is, "What is the difference?". Because it should never be a matter of speed that aids a developer deciding. LIKE and CONTAINS do different things - and (in many cases) return different results. LIKE is an exact match, whereas CONTAINS may use fuzzy-logic to equate "test" to "exam" - When you only want "test"! Even if they (with the current data you have) both return the same expected results; they may not in the future. It all comes down to intent: "What is it you intend to accomplish with the query?" Speed does not factor in this case. – Slippy

whereas CONTAINS may use fuzzy-logic to equate "test" to "exam" - When you only want "test"! What the what? Where did you come up with this? – Claudine

Answer 1

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

Chenopod

@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. – Chenopod

This helped us sort out a query on SharePoint. Have another Great Answer badge. – Qulllon

Answer 2

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.

Parapsychology

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

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. – Parapsychology

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). 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. – Patronage

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. – Capet

Answer 3

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

Manche

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. – Fastback

If you read the documentation for CONTAINS (learn.microsoft.com/en-us/sql/t-sql/queries/…), 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"') – Rogovy

Who votes this answer up if it doesn't work and why? – Branny

Answer 4

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.

Windom

Answer 5

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

Cretan

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. – Nosology