Like operator in SQL queries and performance

Like operator is used after where clause in SQL query. Its main use is to search specified pattern in any column of database table.

Like operator can be used mainly in three types:

1.
SELECT * FROM table1 WHERE column1 LIKE ‘sometext%’

This is used if you want to select all the rows from table where content in any column starts with any specified text. Here % is wildcard which means any string which also could be null.

2.
SELECT * FROM table1 WHERE column1 LIKE ‘%sometext’

This is used if you want to select all the rows from table where content in any column ends with any specified text. Here % is wildcard which means any string which also could be null.

3.
SELECT * FROM table1 WHERE column1 LIKE ‘%sometext%’
This is used if you want to select all the rows where specified text is found any where in the column. In this case wild card is used on both side because random string could be on both side of the text.

Third variant above is most widely used with Like operator. This is because when filters are applied for searching, it meant to search anywhere.

All above variants differ in execution speed as well, which depends on different aspects.

  • Type of Like operator (any of above three)
  • Column data type
  • Number of rows in the table
Order of speed in searching with like operator is 1 > 2 > 3. Where first is fastest and third is slowest.
While using Like operator keep in mind that nvarchar can create major overhead so instead of using nvarchar use text type.
Note: Above article is written using keyword and query behavior in MSSQL Server.

Leave a Reply

Your email address will not be published.