Microsoft® SQL Server® 2012 Bible

122

Part II: Building Databases and Working with Data

In this example, the BETWEEN selects all the work orders with a quantity greater than 9

and less than 20:

USE AdventureWorks;

SELECT WorkOrderID

FROM Production.WorkOrder

WHERE OrderQty BETWEEN 10 and 19

The BETWEEN search condition is commonly used with dates. However, BETWEEN without a time looks for the

beginning of the fi nal day, or with a time rounds up the fi nal millisecond to possibly include 12:00:00.000 of the next

day. The solution is to use the following:

WHERE Col >= StartDay AND Col < Ending Day + 1

For example,

WHERE SalesDate >= '6/1/2008' AND SalesDate < '7/1/2008'

There’s actually quite a lot to consider when working with dates, which is covered in Chapter 8.

Comparing with a List

The WHERE condition can compare the test value against the values in a list using IN,

SOME, ANY, or ALL. Each operator can also be mixed with a NOT to reverse the condition.

Algebra Actually Is Useful

As much fun as algebra class was, although you thought algebra might improve your logical minds,

few of you believe you would actually use algebra in your chosen profession.

Enter the SQL WHERE clause.

Here’s the problem: If you apply a function to the test column in the WHERE clause, then SQL Server is

forced to calculate that function on every row before it can fi lter the WHERE clause. This is a sure setup

for “Gee, I don’t know, it worked OK on my notebook” syndrome.

For a simple example, assume there’s an index on Coll. The following WHERE clause generates an

unnecessary scan, reading every row, as every column is modifi ed and then compared to 130 :

SELECT Col2, Col3

FROM table

WHERE Col11 + 30 = 130;

c06.indd 122c06.indd 122 7/30/2012 4:16:01 PM7/30/2012 4:16:01 PM

http://www.it-ebooks.info