LIKE vs CONTAINS in SQL Server

Though LIKE is an operator and CONTAINS is a predicate and has several differences between them, they can also be used interchangeably in several use cases. Here we will see how to use both LIKE and CONTAINS to search for a word or phrase and then go through the major differences between them (LIKE Vs CONTAINS).

LIKE & CONTAINS in similar use-case

To start with, here is an example of how to use both LIKE and CONTAINS to get the same result set. For this illustration, I have used the table Application.Cities in Microsoft’s sample database WideWorldImporters.

USE WideWorldImporters
GO

/* LIKE */
SELECT * FROM Application.Cities 
    WHERE CityName LIKE 'Xenia' OR CityName LIKE 'Yale'

/* CONTAINS */
Select * FROM Application.Cities 
    WHERE CONTAINS (CityName, '"Xenia" or "Yale"')
GO
Using Like vs Contains

LIKE vs CONTAINS

#LIKECONTAINS
1LIKE is an operator which is used to find whether a character string matches a specified pattern.CONTAINS is a predicate which can be used to search for a word, prefix of a word, a word near another word, synonym of a word, etc..
2It does not need the column to be indexed.It performs full-text search only on full-text indexed columns.
3You can use regular characters or wildcard characters like %, _, [] and [^] in a patternThe only wildcard character which can be used is asterisk (*).
4The wildcard characters can be used in different places of the pattern as needed. Example: ‘%Some Phrase%’, ‘_ome Phrase’, ‘[P-S]ome Phrase’, ‘Some P[^a-g]%’, etc..The wildcard asterisk (*) will work only if it is used at the end of the word or phrase. Example: ‘”Some Phrase*”‘.
5Performance may be slower if you use wildcards at the beginning of the pattern as it may not be able to use any of the indexes.Since wildcards can be used only at the end, performance should be faster as it can use indexes like the full-text index.
6Pattern used for comparison is a string which can have a maximum of eight thousand bytes.The search condition is nvarchar. Large strings like varcher(max) and nvarchar(max) are not accepted.

Performance

As mentioned above in the comparison, performance wise, using LIKE will be slower compared to CONTAINS. For example, I ran the above example statements against the Application.Cities table with more than 37,000 records in WideWorldImporters database and obtained the actual execution plan. If you notice the execution plan in the below screen shot, you can see LIKE was using 98% of the execution cost compared with 2% by CONTAINS. This is because CONTAINS used the full text index while LIKE cannot use the index. Thus it is clear that CONTAINS is faster than LIKE.

Like vs Contains Performance

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.