Bits of .NET

LIKE '%abc%' is un-indexable — always full scan.

✔ Rarely Known Fix

Use a computed column + index:

ALTER TABLE Products
ADD NameIndex AS LOWER(Name);

CREATE INDEX IX_Products_NameIndex ON Products(NameIndex);

Query:

WHERE NameIndex LIKE '%abc%'

💡 Result

Massive speed boost without full scans.