Ravindra Devrani

SQL Tip: Don't use `WHERE field LIKE '%search_term%'` unless you really need it. It would not use index on the field. It would be problematic, if you have a table with large record set. Let's look at the example

Note: The 'Book' table have 999996 records, and it has a non-clustered index on the column 'Title'.

Query 1:

SELECT
b.*
FROM Book b
WHERE b.Title Like '%fairy%';

This query Won't use the index. It Performs 'index scan' and read all 999996 rows (whole 'Book' table).

Query 2:

SELECT
b.*
FROM Book b
WHERE b.Title Like 'fairy%';

This query will use 'index seek' and read only 1 row. Since we have only one record that starts with the text 'fairy'.

7 months ago | [YT] | 4