Some queries in SQL Server

The most obvious feature of SQL differs from other programming languages is the order in which the code is processed. In a large number programming language, code is processed in encoded order, but in the SQL language, the first processed clause is the FROM clause, although the SELECT statement first appears, but is almost always finally processed.

Each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller. If you do not specify a clause in the query, the corresponding step is skipped. The following is a brief description of each logical step that is applied to SQL Server 2000 and SQL Server 2005.

The order in which some keywords are executed:

(1) From [left_table]

(2) On <join_condition>

(3) <join_type> join <right_table>

(4) WHERE <where_condition>

(5) GROUP by <group_by_list>

(6) With <cube | rollup>

(7) Having

(8) SELECT

(9) DISTINCT

(10) ORDER by <order_by_list>

(one) <top num> <select list>

Introduction to the Logical query processing phase

1. from: performs a cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause, generating a virtual table VT1
2. on: The applies an on filter to the VT1. Only those lines that make <join_condition> true are inserted into the VT2.
3. OUTER (join): If you specify a OUTER join (relative to a cross join or (INNER join), keep the table (preserved Table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, a full outer join marks two tables as a reserved table) and no matching rows are found in the VT2 as an outer row. Generate VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that you generated for the previous join until you have finished processing all the tables.
4. Where: applies a where filter to VT3. Only rows that make <where_condition> true are inserted VT4.
5. GROUP By: generates VT5 by grouping rows in VT4 by the list of columns in the GROUP BY clause.
6. cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.
7. has: has a having filter applied to VT6. Only groups that make
8. Select: processes the select list, producing VT8.
9. DISTINCT: removes duplicate rows from VT8, resulting in VT9.
10. ORDER BY: generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.
　　
11. TOP: selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.

Note: Step 10, sort the rows returned by the column list in the ORDER BY clause, and return the cursor VC10. This step is the first and only step you can use the column aliases in the select list. This step differs from the other step in that it does not return a valid table, but instead returns a cursor. SQL is based on the set theory. The collection does not pre-order its rows, it is only a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a proper understanding of SQL.

Because this step does not return a table (instead of returning a cursor), a query that uses the ORDER BY clause cannot be used as a table expression. Table expressions include: views, inline table-valued functions, subqueries, derived tables, and common expressions. Its result must be returned to the client application that expects to get the physical record.

This is the problem that happened today.

"ORDER BY is not valid in views, inline functions, derived tables, subqueries, and common expressions unless you specify the top or for XML otherwise."

Links: http://q.cnblogs.com/q/69584/

Some queries in SQL Server