123
Chapter 6: Introducing Basic Query Flow
6
Algebra to the rescue. Somehow fi gure out a way to move that function to the parameter on the right
side of the = and off the column, so that the column on the left side is unencumbered by any calcula-
tion or functions:SELECT Col2, Col3
FROM table
WHERE Col11 = 130 – 30;Now SQL Server can evaluate 130 – 30 and perform a blazingly fast index seek on the rows with 100
in Col1. Although this is a simple example, the principle is true. How you write your WHERE clauses has
a signifi cant effect on the performance of your queries.This is only a small taste of the Query Optimizer and whether WHERE clause expressions are searchable
arguments, known as sargs. Reading query execution plans and tuning queries and indexes are covered
in greater detail in Chapters 44, “Interpreting Query Execution Plans.” and 45, “Indexing Strategies.”SOME and ANY search conditions are functionally similar to IN — all are true if any value
in the list is true — with three signifi cant differences:■ (^) SOME and ANY require a subquery. A list of literal values won’t do.
■ (^) SOME and ANY are used with a mathematical operator (=, >, <, =>, etc.).
■ (^) IN, SOME, and ANY function differently when used with a NOT condition.
The AND search condition also requires a true subquery and returns a true when the
search condition is true for every value in the list.
IN, SOME, ANY, and ALL are revisited in Chapter 9. This chapter focuses on IN with a literal list.
IN is similar to the EQUALS comparison operator because it searches for an exact match
from a list. If the value is in the list, then the comparison is true. For instance, if you
query the StateProvince table in the AdventureWorks database providing a list of state or
province codes similar to the following:
USE AdventureWork;
SELECT Name
FROM Person.StateProvince
WHERE StateProvinceCode IN ('NC', 'WV');
Result:
Name
North Carolina
West Virginia
c06.indd 123c06.indd 123 7/30/2012 4:16:02 PM7/30/2012 4:16:02 PM
http://www.it-ebooks.info