Query Design Analysis - SQL Server Query Performance Tuning - page 356

Avoid Functions on the WHERE Clause Column
In the same way as arithmetic operators, functions on WHERE clause columns also hurt query performance—and for
the same reasons. Try to avoid using functions on WHERE clause columns, as shown in the following two examples:

SUBSTRING versus LIKE •

• Date part comparison

SUBSTRING vs. LIKE
In the following SELECT statement ( substring.sql in the download), using the SUBSTRING function prevents the use of
the index on the ShipPostalCode column.

SELECT d.Name
FROM HumanResources.Department AS d
WHERE SUBSTRING(d.[Name], 1, 1) = 'F';

Figure 18-8 illustrates this.

Figure 18-8. Execution plan showing the detrimental effect of using the SUBSTRING function on a WHERE clause column

As you can see, using the SUBSTRING function prevented the optimizer from using the index on the [Name]
column. This function on the column made the optimizer use a clustered index scan. In the absence of the clustered
index on the DepartmentID column, a table scan would have been performed.
You can redesign this SELECT statement to avoid the function on the column as follows:

SELECT d.Name
FROM HumanResources.Department AS d
WHERE d.[Name] LIKE 'F%';

This query allows the optimizer to choose the index on the [Name] column, as shown in Figure 18-9 .

Figure 18-9. Execution plan showing the benefit of not using the SUBSTRING function on a WHERE clause column