| |
 |
|
SQL Server
Tips by Burleson |
Wonder Shorthands
SQL also came up with some wonder "shorthands" that improve the
readability of the code. The logical operator "x BETWEEN y AND z"
means "((y <= x) AND (x <= z))" -- note the order of comparison and
the inclusion of the endpoints of the range. Likewise, "x IN (a,b,c,..)"
expands out to "((x = a) OR (x = b) OR (x = c) OR ...)" at run time.
Most SQL engines are pretty good about optimizing the predicates and
not that good about optimizing calculations. For example, the engine
might not change (x + 0) or (x * 1) to (x) when they are compiling
the code. This means that you need to write very clear logical
expression with the simplest calculations in SQL.
Procedural languages like Fortran or Pascal are very good about
optimizing calculations, which only makes sense because all they do
is calculations! But SQL is a data retrieval language and the goal
is to get back the right set of data as fast as possible from the
secondary storage. Calculations are done at the speed of
electricity, while data is retrieved by mechanical disk reads. The
biggest improvements come from faster retrieval methods, not
improved calculations.
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
