Parting Shots

Suppose you have a column of prices, defined as DECIMAL(7,2) and you need to answer the questionWhat prices are even dollars?

To mislead you, here are two quotes from the Microsoft SQL Server 2000 on line documentation:

• Quote #1: "Avoid data conversion functions."

• Quote #2: "If the same column is on both sides of the comparison operator, the expression is not sargable."

Now, here are three search conditions that answer the question:

 Search condition #1 ... WHERE MOD(decimal_column, 1) = 0 Search condition #2 ... WHERE CAST(decimal_column AS CHAR(7)) LIKE '%.00%' Search condition #3 ... WHERE decimal_column = CAST(decimal_column AS INTEGER) 

Which expression is best? There is an answer if you've read this chapter so far.

• Search condition #1 is the worst. Although there is no CAST, it depends on an implicit DECIMAL-to-INTEGER data conversion (because modulus operations work on integers). There is also an implicit divide operation.

• Search condition #2 is in the middle. Some DBMSs store DECIMAL values as character strings, so the data conversion isn't hard. Nevertheless, LIKE is slow when the pattern starts with a wildcard.

• Search condition #3 is the best (GAIN: 7/7). It breaks the rules stated in both Quote #1 and Quote #2, but we said those quotes were misleading (Microsoft uses an unusual definition of "sargable"). If you compare all three conditions using the point counts shown in Tables 2-1 and 2-2 at the beginning of this chapter, you'll find that condition #3 scores highest because it uses the equals operator and has the fewest expressions. Also, it's closest to the sargable ideal because it starts with:

   <column> <comparison operator> 

Portability

MySQL doesn't support CAST. The gain shown is for only seven DBMSs.

In other words, there's nothing more to say. If you answered "Search condition #3" to the question, you know how syntax-based optimizations work.