SQL Server 2008 Technical Insider--t-sql query notes

Original: SQL Server 2008 Technical Insider--t-sql query notes

1, SQL Programming has many unique features, such as: set-oriented thinking mode, query elements of the logical processing sequence, three-valued logic. If you do not have the knowledge to start using SQL programming, you will get redundant, low performance code, and difficult to maintain.

2. The component responsible for generating the actual work plan (execution plan) in SQL Server is: query optimizer. The optimizer will take a shortcut only if the result set is correct. It is important to clearly distinguish between logical and physical processing of queries.

3, the various stages of the logical query:

(5) SELECT (5-2) DISTINCT (5-3) TOP (<top_specification>) (5-1) <select_list> (1) from (1-J) <left_table> ; <join_type> join <right_table> on <on_predicate>     | ( 1-A) <left_table> <apply_type> apply <right_table_expression> as <alias>     | ( 1-P) <left_table> PIVOT (<pivot_specification>) as <alias>     | ( 1-u) <left_table> UNPIVOT (<unpivot_specification>) as <alias> (2) WHERE <where_predicate> (3) GROUP by <group_by_specification> (4) have 


First step: From: Represents the source table of the query, the stage involved in the join operation is (1-J1) Cartesian product, (1-J2) on filter and (1-J3) add outer row. and generate virtual table VT1.
1.1, Cartesian product: Two tables to perform Cartesian product, generate virtual table vt1-j1.
1.2. On filter: Inserts a vt1-j2 for the line in vt1-j1 that satisfies on true.
1.3. Add an external row: If you specify a outer join (relative to a cross JOIN or inner join), rows that do not match in the table are persisted, added as outer rows, and the VT1-J3 is generated.
Step two: Where: Row filter the data in VT1 based on the predicate in the WHERE clause, and insert VT2 only for the row with the result true.
Step three: Group by: VT2 are grouped according to the list in group by, and each group has only one result row, generating VT3.
Fourth step: Having: VT3 jinxin filter based on the verb that has occurred, only the group with the result true is inserted VT4.
Fifth step: Select: Processes the elements in the SELECT clause, producing VT5.
5.1. Calculation expression: Generates vt5-1 according to the expression in the select list.
5.2, DISTINCT: Delete duplicate rows in vt5-1, generate vt5-2.
5.3, TOP: Define the logical sort according to the ORDER BY clause, select the previous specified quantity or percentage line from vt5-2, and produce the vt5-3.
Sixth step: ORDER by: Sorts the vt5-3 according to the column name specified in the ORDER BY clause. Generates a cursor VC6.
For three-valued logic:
A, all query filters (on, WHERE, and have) treat Null as false.
b, the null value in the CHECK constraint is treated as true.
C, UNIQUE constraints, set operations (UNION and except), and sorting and grouping operations, consider two null to be equal.
Attention:
1. If there are multiple table operators in the FROM clause, they are processed in left-to-right order. The result of each table operator is the left input of the next table operator, and the virtual table is generated as the input for the next stage.
2. Because the data is not grouped before where, the WHERE clause cannot use aggregations. In the filter, on the delete of the middle branch of the reserved table is not final, and where is the final. There is a logical difference between on and where when an outer join is used.
3. If group BY is specified in the query, all subsequent steps can only be performed on the specified group.
4. Having is the only filter that can be used to group data.
5. Because SQL has multiple simultaneous computations (All-at-once operation), the logical order in the SELECT statement is irrelevant.
6, ORDER by is the only step that can use aliases in select.






SQL Server 2008 Technical Insider--t-sql query notes