Microsoft® SQL Server® 2012 Bible

115

Chapter 6: Introducing Basic Query Flow

6

FIGURE 6-2

A simplifi ed view of the logical fl ow of the query showing how data moves through the major clauses

of the SQL select command.

Data

Source(s)

From

Order

Where By

Col(s),

Expr(s) Predicate

Following is a more detailed explanation of the logical fl ow of the query. Every step except

step 4 is optional:

1. [From]: The query begins by assembling the initial set of data, as specifi ed in
   the FROM portion of the SELECT statement. (Chapter 9, “Merging Data with Joins,
   Subqueries, and CTEs,” discusses how to build even the most complex FROM clauses.)


2. [Where]: The fi lter process is actually the WHERE clause, selecting only those rows
   that meet the criteria.


3. [Aggregations]: SQL can optionally perform aggregations on the data set, such
   as fi nding the average, grouping the data by values in a column, and fi ltering the
   groups (see Chapter 10, “Aggregating, Windowing, and Ranking Data”).


4. Column Expressions: The SELECT list is processed, and any expressions are calcu-
   lated (covered in Chapter 8, “Data Types, Expressions, and Scalar Functions”).


5. [Order By]: The resulting rows are sorted according to the ORDER BY clause, which
   can be ascending or descending. The default sort order is ascending.


6. [Over]: Windowing and ranking functions can provide a separately ordered view of
   the results with additional aggregate functions.


7. [Distinct]: Any duplicate rows are eliminated from the result set.


8. [Top]: After the rows are selected, the calculations are performed, and the data is
   sorted into the wanted order, SQL can restrict the output to the top few rows.


9. [Insert, Update, Delete]: The fi nal logical step of the query is to apply the data
   modifi cation action to the results of the query. These three verbs are explained in
   Chapter 12, “Modifying Data in SQL Server.”


10. [Output]: The inserted and deleted virtual tables (normally only used with a trig-
    ger) can be selected and returned to the client, inserted into a table, or serve as a
    data source to an outer query.


11. [Union]: The results of multiple queries can be stacked or combined using a union
    command (see Chapter 9).

c06.indd 115c06.indd 115 7/30/2012 4:15:57 PM7/30/2012 4:15:57 PM

http://www.it-ebooks.info