Unless TOP or FORXML is also specified, an invalid SQL statement in a view, inline function, derived table, subquery, or public table expression is different from that in other programming languages. The most obvious characteristic of an ORDERBY clause is the order of processing code. In programming languages with large numbers, code is processed in the encoding order, but in SQL, the first clause to be processed is FRO.
Unless TOP or for xml is also specified, invalid SQL statements in views, inline functions, derived tables, subqueries, and common table expressions of ORDER BY clauses are different from those in other programming languages. In programming languages with large numbers, code is processed in the encoding order, but in SQL, the first clause to be processed is FRO.
The order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP or for xml is also specified.
Unlike other programming languages, SQL is most characteristic of code processing. In programming languages with large numbers, the code is processed in the encoding order, but in SQL, the first clause to be processed is the FROM clause. Although the first SELECT statement appears, but it is almost always processed.
Each step generates a virtual table, which is used as the input for the next step. These virtual tables are unavailable to callers (client applications or external queries. Only the table generated in the last step will be returned to the caller. If a clause is not specified in the query, the corresponding steps are skipped. The following is a brief description of each logical step for SQL server 2000 and SQL Server 2005.
(
8) SELECT (
9) DISTINCT (
11) (1) FROM [left_table](3)JOIN(2) ON(4) WHERE(5) GROUP(6)(7) HAVING(10) ORDERIntroduction to the logical Query Processing StageFROM:Run the Cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause to generate the virtual table VT1.ON:Apply the ON filter to VT1. Only thoseVT2 is inserted only when the row is true.OUTER (JOIN ):If outer join (compared with cross join or (inner join) is specified, the left table is marked as a reserved table by the preserved table: left outer join, the right external join marks the right table as a reserved table, and the full external join marks both tables as reserved tables.) unmatched rows are not found and added to VT2 as external rows, generate VT3. if the FROM clause contains more than two tables, repeat steps 1 to 3 for the result table generated by the previous join and the next table until all tables are processed.WHERE:Apply the WHERE filter to VT3. Only enableIf the value is true, VT4.Group:GROUP the rows in VT4 BY column list in the group by clause to generate VT5.CUBE | ROLLUP:Insert Suppergroups into VT5 to generate VT6.HAVING:Apply the HAVING filter to VT6. Only enableIf this parameter is set to true, VT7.SELECT:Process the SELECT list and generate VT8.DISTINCT:Remove duplicate rows from VT8 to generate VT9.Order:Sort the rows in VT9 BY column list in the order by clause to generate a cursor (VC10 ).TOP:Select a specified number or proportion of rows from the beginning of VC10, generate table VT11, and return to the caller.Note: Step 10: sort the rows returned in the previous step BY column list in the order by clause, and return the cursor VC10. this step is the first and only step to use the column alias in the SELECT list. Unlike other steps, this step returns a cursor instead of a valid table. SQL is based on the set theory. A set does not sort its rows in advance. It is only a logical set of members, and the order of members is irrelevant. You can return an object for sorting a table, including rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a correct understanding of SQL.Because this step does not return the table (but returns the cursor), queries using the order by clause cannot be used as table expressions. Table expressions include views, inline Table value functions, subqueries, derived tables, and shared expressions. The result must be returned to the client application that expects a physical record. For example, the following derived Table query is invalid and an error is generated:Select * from (select orderid, customerid from orders order by orderid) as dThe following view also produces errors.Create view my_viewasselect * from ordersorder by orderidOracle supports this method.In SQL, table expressions do not allow queries with an ORDER BY clause, but there is an exception in the T-SQL (applying the TOP option ).Therefore, remember not to assume any specific sequence for the rows in the table. In other words, do not specify the order by Clause unless you are sure to ORDER the rows. Sorting requires cost. SQL Server needs to perform an ordered index scan or use a sort operator.
