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