LIKE vs CONTAINS on SQL Server

sql-server performance contains sql-like
user667429 picture user667429 · Sep 22, 2011 · Viewed 1.1M times · Source

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

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Sep 22, 2011

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

Related questions

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Read More