Optimising wildcard prefixed LIKE conditions

Suppose you want to write a query to find all products in your database that have a name beginning with “Long-Sleeve”. You’d more than likely use something like below (examples based on AdventureWorks LT sample database): SELECT name FROM SalesLT.Product WHERE name LIKE 'Long-Sleeve%' This produces a good execution plan, performing an index seek on the nonclustered index that exists on the name column. If you look at the seek operation, you’ll see the query optimiser has done a good job of ensuring an index can be used by looking at the seek predicate: [Read More]

Optimising date filtered SQL queries

How you structure your SQL queries is very important and choosing the wrong approach can have big effects on the performance of the query. One of the key things that should flag up the potential for needing optimisation, is if you are using a function/calculation within a WHERE clause. These can lead to inefficient execution plans, preventing sub-optimal index use. A classic example to demonstrate this is when querying the data for a specific month. [Read More]