SQL 查询的执行顺序 [英] Order Of Execution of the SQL query

问题描述

我对这个查询的执行顺序感到困惑，请解释一下.我对何时应用联接、调用函数、使用 Case 添加新列以及何时添加序列号感到困惑.请解释所有这些的执行顺序.

select Row_number() OVER(ORDER BY (SELECT 1)) AS 'Serial Number',EP.FirstName,Ep.LastName,[dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole) 作为 RoleName，(选择前 1 个 convert(varchar(10),eventDate,103)from [3rdi_EventDates] where EventId=13)作为 EventDate，(CASE [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole)当90 天客户"然后DC"WHEN 'Association Client' THEN 'DC'当自闭症低语者"然后DC"当'CampII'然后'AD'当船长"然后广告"当脊椎按摩师"然后AD"当教练"然后广告"END) 作为来自 [3rdi_EventParticipants] 的类别作为 EP内连接 [3rdi_EventSignup] 作为 EP.SignUpId = ES.SignUpId 上的 ES其中 EP.EventId = 13和用户 ID (从用户角色中选择不同的用户 ID--where roleid不在(6,7,61,64)和roleid不在(1,2))其中 roleid 不在 (19, 20, 21, 22) 和 roleid 不在 (1,2))

这是从上面的查询中调用的函数.

CREATE 函数 [dbo].[GetBookingRoleName](@UserId 作为整数，@BookingId 作为整数)返回 varchar(20)作为开始声明@RoleName varchar(20)如果@BookingId = -1Select Top 1 @RoleName=R.RoleName From UserRoles UR 内连接 Roles R on UR.RoleId=R.RoleId Where UR.UserId=@UserId and R.RoleId not in(1,2)别的从 RoleId = @BookingId 的角色中选择 @RoleName= RoleName返回@RoleName结尾

解决方案

SQL 没有执行顺序.是一种声明性语言.优化器可以自由选择任何合适的顺序来产生最佳执行时间.给定任何 SQL 查询，任何人基本上不可能假装它知道执行顺序.如果您添加有关所涉及模式的详细信息(确切的表和索引定义)和估计的基数(数据大小和键的选择性)，则可以猜测可能的执行顺序.>

最终，唯一正确的顺序"是描述实际执行计划的顺序.请参阅使用 SQL Server Profiler 事件类显示执行计划 和 显示图形执行计划 (SQL Server Management Studio).

一个完全不同的事情是查询、子查询和表达式如何将自己投射到有效性"中.例如，如果您在 SELECT 投影列表中有一个别名表达式，您可以在 WHERE 子句中使用别名吗?像这样:

SELECT a+b as c从T其中 c=...;

c 别名在 where 子句中的使用是否有效?答案是不.查询形成一个语法树，树的较低分支不能引用树中定义的较高部分.这不一定是执行"的顺序，更多的是语法解析问题.相当于用C#写这段代码:

void Select (int a, int b){如果 (c = ...) 那么 {...}int c = a+b;}

就像在 C# 中一样，这段代码不会编译，因为变量 c 是在定义之前使用的，上面的 SELECT 不会正确编译，因为别名 c 是在树中引用的比实际定义的要低.

不幸的是，与众所周知的 C/C# 语言解析规则不同，关于如何构建查询树的 SQL 规则在某种程度上是深奥的.在 Single SQL Statement 中简要提到了它们处理 但详细讨论了它们是如何创建的，什么顺序是有效的，什么是无效的，我不知道任何来源.我并不是说没有好的资源，我确信一些优秀的 SQL 书籍涵盖了这个主题.

请注意，语法树顺序与 SQL 文本的视觉顺序不匹配.例如，ORDER BY 子句通常是 SQL 文本中的最后一个，但作为一个语法树，它位于其他一切之上(它对 SELECT 的输出进行排序，因此它位于 SELECTed 列之上，以便说话)，因此是有效引用c别名:

SELECT a+b as c从T按 c 订购；

更新

其实是这样的:SELECT 语句的逻辑处理顺序

<块引用>

以下步骤显示了逻辑处理订单，或绑定订单，对于 SELECT 语句.这个命令确定何时定义的对象一步是可用的后续步骤中的条款.为了例如，如果查询处理器可以绑定到(访问)表或视图在 FROM 子句中定义，这些对象及其列被制作可用于所有后续步骤.相反，因为 SELECT 子句是第 8 步，任何列别名或该子句中定义的派生列不能被前面引用条款.然而，它们可以是被后续条款引用，例如作为 ORDER BY 子句.请注意，实际物理执行语句由查询决定处理器和订单可能会有所不同这份清单.

1. 来自
2. 开启
3. 加入
4. 哪里
5. 分组依据
6. WITH CUBE 或 WITH ROLLUP
7. 拥有
8. 选择
9. 与众不同
10. 订购者
11. 顶部

I am confused with the order of execution of this query, please explain me this. I am confused with when the join is applied, function is called, a new column is added with the Case and when the serial number is added. Please explain the order of execution of all this.

select Row_number() OVER(ORDER BY (SELECT 1))  AS 'Serial Number', 
    EP.FirstName,Ep.LastName,[dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole) as RoleName,  
    (select top 1 convert(varchar(10),eventDate,103)from [3rdi_EventDates] where EventId=13) as EventDate,
    (CASE [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole)  
            WHEN    '90 Day Client' THEN 'DC'
            WHEN    'Association Client'  THEN  'DC'
            WHEN    'Autism Whisperer'    THEN  'DC'
            WHEN    'CampII'             THEN   'AD' 
            WHEN    'Captain'              THEN 'AD' 
            WHEN    'Chiropractic Assistant' THEN 'AD'
            WHEN    'Coaches'               THEN 'AD'
            END) as Category from [3rdi_EventParticipants] as EP  
    inner join [3rdi_EventSignup] as ES on EP.SignUpId = ES.SignUpId  
    where EP.EventId = 13
    and userid in (  
    select distinct userid from userroles  
    --where roleid not in(6,7,61,64) and roleid not in(1,2))  
    where roleid not in(19, 20, 21, 22) and roleid not in(1,2))

This is the function which is called from the above query.

CREATE function [dbo].[GetBookingRoleName]  
(  
 @UserId as integer,
 @BookingId as integer
)  
RETURNS varchar(20)  
as  
begin  
declare @RoleName varchar(20)  

if @BookingId = -1
Select Top 1 @RoleName=R.RoleName From UserRoles UR inner join Roles R on UR.RoleId=R.RoleId Where UR.UserId=@UserId and R.RoleId not in(1,2)  
else
Select @RoleName= RoleName From Roles where RoleId = @BookingId

return @RoleName  
end

解决方案

SQL has no order of execution. Is a declarative language. The optimizer is free to choose any order it feels appropriate to produce the best execution time. Given any SQL query, is basically impossible to anybody to pretend it knows the execution order. If you add detailed information about the schema involved (exact tables and indexes definition) and the estimated cardinalities (size of data and selectivity of keys) then one can take a guess at the probable execution order.

Ultimately, the only correct 'order' is the one described ion the actual execution plan. See Displaying Execution Plans by Using SQL Server Profiler Event Classes and Displaying Graphical Execution Plans (SQL Server Management Studio).

A completely different thing though is how do queries, subqueries and expressions project themselves into 'validity'. For instance if you have an aliased expression in the SELECT projection list, can you use the alias in the WHERE clause? Like this:

SELECT a+b as c
FROM t
WHERE c=...;

Is the use of c alias valid in the where clause? The answer is NO. Queries form a syntax tree, and a lower branch of the tree cannot be reference something defined higher in the tree. This is not necessarily an order of 'execution', is more of a syntax parsing issue. It is equivalent to writing this code in C#:

void Select (int a, int b)
{
   if (c = ...) then {...}
   int c = a+b;
}

Just as in C# this code won't compile because the variable c is used before is defined, the SELECT above won't compile properly because the alias c is referenced lower in the tree than is actually defined.

Unfortunately, unlike the well known rules of C/C# language parsing, the SQL rules of how the query tree is built are somehow esoteric. There is a brief mention of them in Single SQL Statement Processing but a detailed discussion of how they are created, and what order is valid and what not, I don't know of any source. I'm not saying there aren't good sources, I'm sure some of the good SQL books out there cover this topic.

Note that the syntax tree order does not match the visual order of the SQL text. For example the ORDER BY clause is usually the last in the SQL text, but as a syntax tree it sits above everything else (it sorts the output of the SELECT, so it sits above the SELECTed columns so to speak) and as such is is valid to reference the c alias:

SELECT a+b as c
FROM t
ORDER BY c;

Updated

Actually there is this: Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

这篇关于SQL 查询的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！