Database Reference
In-Depth Information
Figure 18-6.
Execution plan showing automatic transformation of a nonindexable
!<
operator to an indexable
>=
operator
As you can see, the optimizer often provides you with the flexibility of writing queries in the preferred T-SQL
syntax without sacrificing performance.
Although the SQL Server optimizer can automatically optimize query syntax to improve performance in many
cases, you should not rely on it to do so. It is a good practice to write efficient queries in the first place.
Avoid Arithmetic Operators on the WHERE Clause Column
Using an arithmetic operator on a column in the
WHERE
clause can prevent the optimizer from using the index on the
column. For example, consider the following
SELECT
statement:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE poh.PurchaseOrderID * 2 = 3400;
A multiplication operator,
*
, has been applied on the column in the
WHERE
clause. You can avoid this on the
column by rewriting the
SELECT
statement as follows:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE poh.PurchaseOrderID = 3400 / 2;
The table has a clustered index on the
PurchaseOrderID
column. As explained in Chapter 4, an
Index Seek
operation on this index is suitable for this query since it returns only one row. Even though both queries return the
same result set, the use of the multiplication operator on the
PurchaseOrderID
column in the first query prevents the
optimizer from using the index on the column, as you can see in Figure
18-7
.
