SQL Server 2005技术内幕：查询、调整和优化

SQL Server 2005技术内幕：查询、调整和优化1——扫描及查找

 

 　  闲话少说，切入正题，扫描和查找时从表和索引中读取数据的迭代器，作为SQL Sever中所支持的最基本的迭代器，几乎会在每一个查询计划中出现。一定要区分扫描和查找之间的不同，扫描是用来处理整个表和索引的全部分支；查找时在谓词的基础上有效的返回索引中的一个或多个范围中的行。

首先是关于扫描的示例，如下所示：

select [orderId] from [Orders] O where [RequiredDate] = '1998-02-26'

RequiredDate列是没有索引的。所以SQL Server需要读取Orders表中的每一行，在RequiredDate中评估此谓词，如果这一行符合条件，则返回此行。

因为扫描操作会涉及表中的每一行而不管其是否符合条件，所以扫描的成本与表中的行数成正比。因此当表很小或对谓词来说有很多行符合条件时，扫描时有效的。然而，当表很大并且当大部分的行都不符合条件时，扫描就要涉及很多不必要的页和行，并且执行大量的I/O操作。

下面来看一个索引查找的例子。其谓词在OrderDate列，而这一列有一个索引。

select [orderId] from [Orders] O where [orderdate] = '1998-02-26'

这时SQL Server就可以使用这个索引直接搜索满足此谓词的行。这样，就可以将这个谓词看做是一个查找谓词。索引确保了查找操作符只返回符合条件的行。由于只在符合条件的行及包含这些行的页中进行，所以查找的效率与符合条件的行数、页数成正比，而与表中的总行数无关。因此查找效率取决于是否选择了合适的查找谓词。即如果一个查找谓词能够过滤表中的大部分内容，那么查找效率会大大提高。

在SQL Server中，扫描与查找的区别类似于堆扫描、聚集索引扫描与非聚集索引扫描之间的区别。

SQL Server在执行索引查找前，需要确定查询中索引关键字是否适合计算谓词。推荐使用可以作为索引查找基础的可查找谓词。同时SQL Server还需要确定索引中是否包含或覆盖了查找中所涉及的列的集合。下面将详细介绍什么样的谓词可查找，什么样的谓词不可查找，以及索引所覆盖的列。

(一)   单列索引

　　确定一个谓词是否能在单列索引中进行查找非常简单。SQL Server可以使用单列索引来响应大部分的简单比较，包括等价于不等价（大于、小于等），复杂一些的表达式，列中的函数和带有引导通配符的Like谓词，通常会让SQL Server避免使用索引查找。

　　例如，假设在列Coll上有一个单列索引，可以使用这个索引查找下面这些谓词：

• [Coll] = 3.14
• [Coll] > 100
• [Coll] between 0 and 99
• [Coll] like ‘abc%’
• [Coll] in (2,3,5,7)

　　但是，不能使用这个索引对下列这些谓词进行查找：

• [Coll] Like ‘%like’
• [Coll] + 1 = 9

(二)   复合索引

　　复合或多列索引相对来说要复杂一些。复合索引关键字的顺序很重要。它决定了索引的排序方式，并影响SQL Server使用此索引计算的查找谓词集。

排序的理解就像电话薄。电话薄就像是带有关键字（姓、名）的索引，按照姓氏来排序。如果知道某人的姓氏，就可以很容易的找到这个人。然而，如果只知道这个人的名字不知道姓氏，想要找到这个名字的人员名单就很困难，因为需要一个以名字来排序的电话薄。

　　同理，如果两列上存在一个索引，当第一列包含等价谓词时，就只能使用索引去满足第二列上的谓词。甚至在无法使用索引去满足第二列的谓词时，或许可以在第一列中使用。这时，为第二列引入剩余谓词。这个谓词的计算方式与其他扫描谓词类似。

　　例如，假设在列Col1和Col2上有一个两列索引。可以使用这个索引查找单列索引上的任何谓词。同时，也可以用它来查找其他谓词：

• [Col1] = 3.14 and [Col2] = ‘pi’
• [Coll] = ‘xyzzy’ and [Col2] <= 0

　　在下面这个例子中，使用索引满足列Col1上的谓词。列Col2需要一个剩余的谓词。

• [Col1] > 100 and [Col2] > ‘pi’
• [Col1] like ‘abc%’ and [Col2] = 2

　　在无法使用这个索引去查找下一个谓词集，甚至无法查找Col1列时，须使用其他索引（即Col2为引导时的索引），或者带有谓词的扫描。

• [Col2] = 0
• [Col1] + 1 = 9 and [Col2] between 1 and 9

(三)   确定关键字

　　在大多情况下，索引关键字是在Create Index语句中声明的列的集合。然而，在带有聚集索引的表中创建非唯一的非聚集索引时，如果没有明确表示它们是非聚集索引关键字的一部分，那么聚集索引的关键字会被附加到非聚集索引的关键字中。这时，就可以在这些固有的关键字中进行查找了。

(四)   已覆盖的列

　　表中的堆或聚集索引包含了表中所有的列。另一方面，非聚集索引只包含表中列的子集。通过限制存储在非聚集索引中的列集，SQL Server可以在每页中存储更多的行。由于减少了I/O的数量及所涉及的页的数量，这将节省磁盘空间并提高查找及扫描效率。然而，一个索引的扫描或查找操作只能返回被索引覆盖的列。

　　每个非聚集索引都会覆盖创建时所指定的关键列。同时，如果基本表是个聚集索引，那么这个表中每个非聚集索引都会覆盖聚集索引关键字，而不管它们是否为非聚集索引关键列的一部分。在SQL Server2005中，还可以在非聚集索引中使用Create Index语句的Include子句添加其他非关键列。需要注意的是，与索引关键字不同，排序与索引包含的列无关。

SQL Server 2005技术内幕：查询、调整和优化2——Bookmark Lookup

 

根据上篇知道非聚集索引不能覆盖表中所有的列。假设在非聚集索引关键字中有一个带有谓词的查询用来选择没有被索引覆盖的列。如果SQL Server在非聚集索引中进行查找，会丢掉一些必须的列。反之，如果在聚集索引中进行查找，会丢掉一些必须的列。反之，如果在聚集索引中进行扫描，则会获取所有的列。反之，如果在聚集索引中进行扫描，则会获取所有的列。但这要涉及表中的每一行，从而影响效率。例如下面这个查询。

 

select [orderId], [customerid] from [Orders] where [orderdate] = '1998-02-26'

 

 

　　这个查询与之前阐述索引查找所有的查询一样，但这个查询选择了这样两列：OrderId与CustomerId。非聚集索引OrderDate只覆盖了OrderId列。

针对这个问题，SQL Server提供的解决方案。对于每一个从非聚集索引取回的行都可以查找聚集索引中剩余行的值（例如示例中的行CustomerId）。把这个操作称之为“bookmark lookup”。书签指向堆或聚集索引中的行。SQL Server严格地为非聚集索引中的每一行都存储了书签。这样，在基本表中就可以一直找到非聚集索引所对应的行。

Bookup lookup可以与堆一起使用，就像上面所提到的可以与聚集索引一起使用一样。在SQL Server 2000中，堆上的Bookmark lookup与聚集索引上的一样。在SQL Server 2005中，堆上的bookup lookup与聚集索引的一样。堆上的bookmark lookup继续使用嵌套循环连接，而是用RID looup运算符来代替聚集索引。RID lookup运算符包括堆bookmark lookup上的查找谓词，但堆不是索引，RID lookup也不是索引查找。

Bookmark lookup不是简单的运算符。假设非聚集索引关键字与聚集索引关键字不存在如何关联，每个Bookmark lookup执行随机的I/O操作到聚集索引或堆中。随机I/O的成本很高。当比较各种执行计划的替代品时，包括扫描、查找及带有bookmark lookup的查找，优化器必须确定在执行更多的顺序I/O时是否能话费更少的成本，并使用覆盖所有需求列的索引扫描来搜索更多的行；或执行更少的随机I/O并使用带有多个选择性的谓词和bookmark lookup的查找。因为随意I/O比顺序I/O要消耗更多的成本，所以聚集索引扫描比带有bookmark look索引查找更省事。

由此我们可以得出结论，在查询列表中的选择列，尽可能的是查找谓词中索引覆盖的列，这样可以直接返回结果，不需要bookmark lookup，避免了整体扫描聚集索引！

SQL Server 2005技术内幕：查询、调整和优化3——连接

 

连接

SQL Server支持三种物理连接运算符：嵌套循环连接、合并连接及哈希连接。在bookmark lookup示例中，已经接触了嵌套循环连接。没有最好的连接运算符，而且没有连接运算符好或不好。每一个连接运算符在正确的环境都会执行的很好，在错误的环境都会执行的不好。

 

嵌套循环连接

         嵌套循环连接是最简单和最基础的连接方式。它对一个表（外表）的每行和另外一个表（称为内行）的每行比较，寻找满足谓词连接的行。这里的“内表”和“外表”指的是连接的输入。“内连接”和“外连接”指的是逻辑上连接操作符的语义。可以用伪代码表示嵌套循环连接的算法。

 

For each row R1 in the outer table

         For each row R2 in the inter table

                   If R1 joins with R2

                            Return (R1, R2)

 

所有的行都会进行比较，导致这个算法的消耗与外表、内表乘机的大小成比例。因为消耗随着内表大小的增加而变得更大，在实践中优化器试着通过减少对于每个外表必须处理的内表行数来减少消耗。

例如，考虑下面的查询：

 

 

 

select O.[OrderId] 

from [Customers] C join [Orders] O on C.[CustomerId] = O.[CustomerId]

where C.[City] = 'London'

 

 

当执行此查询时，使用下面的查询计划：

 

 

 

 

 

在这个计划中外表是客户，内表是订单。因此，对于嵌套循环连接符，SQL Server从客户表开始寻找。连接每一次提取一个客户，对于每个客户，在Order表中执行一次索引。因为这里有6个客户，它在Order表中执行6次索引查询。注意在Order表中的索引查询依赖与客户表中的客户ID。6次中的每一次，SQL Server重复在Order表中查询索引，客户ID有不同的值。因此，6次索引查询执行的每次结果不同，也返回不同的行。

         把客户ID称作关联参数。如果嵌套循环连接包含有关联参数，则在查询计划中以OUTER REFERENES表示。经常把嵌套循环连接这种有依赖与关联参数的索引查询称作索引连接。索引连接是嵌套循环连接中最常见的类型。

         上一个例子说明了SQL Server对嵌套循环连接提高性能的两个重要技术：关联参数和一个在连接内部中基于关联参数的索引查询。另一个没有提高的性能优化的方法是在连接内部使用存储池。一个存储池能够从连接内部缓存和重新访问结果。存储池在有很多重复值的关联参数和在连接内部估计会有很大消耗时有用。通过使用存储池，SQL Server可以避免对相同关联参数的连接内部重新计算多次。

         不是所有嵌套循环连接都有关联参数。得到一个没有关联参数的简单方法是交叉连接，交叉连接把一个表中所有的行和另一外表中所有的行结合起来。为了用嵌套循环连接实现一个交叉连接，必须搜索和连接内表和外表的每行。内表行的集合不会依赖外表处理的行而改变。因此，对于交叉连接，没有关联参数。

         如果没有合适的索引或适合索引查询的谓词连接，优化器可以使用一个没有连接参数的查询计划。对于决定一个谓词连接是否适合使用一个索引查询的规则，是和决定另外谓词是否适合索引查询一样的。例如：通过以下查询可以查到返回雇佣的员工数：

 

 

select E1.[EmployeeId], COUNT(*)

from [Employees] E1 join [Employees] E2

   on E1.[HireDate] < E2.[HireDate]

group by E1.[EmployeeId]

 

 

在雇佣日期列中没有索引，因此，这个查询产生一个有谓词，但是没有关联参数和索引查询的简单嵌套循环连接：

 

合并连接

合并连接不支持任何连接谓词的嵌套循环连接，合并连接至少需要一个等值连接谓词。而且，合并连接的输入必须存储在连接器上。例如，如果有一个连接谓词[Customers].[CustomerId] = [Orders].[CustomerId] ,客户ID都必须存储在客户的ID列中。

合并连接也是在读的同时对两个存储输入的一行进行比较。在每个步骤中，比较每个输入的下一行。如果两行是相同，输出一个连接后的行并继续。如果行是不同的，舍弃两个输入行中较少的那个并继续。因为输入是存储，连接舍弃的任何行必须比两个输入中任何剩下的行要小，因此可以永不连接。合并连接不需要对两个输入中的每一行扫描。只要到了两个输入中的某一个的末尾，合并连接就会停止扫描。

         嵌套循环连接总的消耗和在输入表中行的乘积成比例，不同于嵌套循环连接，合并连接的表最多读一次，总的消耗和输入行数的总数成正比例，因此何必连接对于大量的输入是较好的选择。

 

排序合并连接和索引合并连接

         对于一个合并连接，SQL Server有两种方法得到排序的输入；直接使用排序操作符对输入排序，或者可能从一个索引中读行。一般来说，一个通过使用索引获得排序次序的计划比直接使用排序的消耗要少。

 

连接谓词和逻辑连接类型

         合并连接支持多等值连接谓词，只要在所有连接键上输入是排序的。只要二者的输入有着一样的排序，特定排序次序就是不匹配。例如，如果有一个连接谓词T1.[Col1] = T2.[Col1] and T1.[Col2] = T2.[Col2]，只要表T1和表T2在（Col1，Col2）上都一样排序，就可以使用合并连接。

 

哈希连接

       哈希连接是第二个物理连接操作符。当提到物理连接操作符时，哈希连接是一个重要的部分。嵌套循环连接对于小的数据集很有用，哈希连接对于大型数据集很有用。哈希连接在并行性和比例行方面优于其他连接，并且对于数据仓库的查询请求反映很快。跟合并连接一样，哈希连接至少需要一个等值连接谓词，支持剩余谓词。不同于合并连接，哈希连接不需要排序的输入集。

         哈希连接在执行时分成两个阶段，即构建阶段和探索阶段。在构建阶段，哈希连接从一个输入（通常称之为左输入或构建输入）中读入所有的行，对等值连接键上的列哈希，然后创建或构建一个内存哈希表。在探索阶段，哈希连接从第二个输入（通常称之为右输入）中读入所有的行，在相同的等值连接键上对行哈希，然后在哈希表中查找或探索匹配的行。由于哈希函数可能导致冲突，哈希连接必须对潜在的匹配进行检查来确定已连接。

         注意，不同于输入行立即开始的嵌套循环连接和合并连接，哈希连接在构建输入时被阻塞。也就是说，哈希连接必须读入和处理所有的构建输入，之后才能返回行。不同于其他连接方法，哈希连接需要一个内存区域存储哈希表。因此，SQL Server在任何时候，对于并发的哈希连接数据有限制。这些特性和约束通常对于数据仓库不是什么问题，但是他们可能为大多数联机事务处理带来不必要的麻烦。

SQL Server 2005技术内幕：查询、调整和优化4——聚合

 

对于聚合，SQL Server支持两种物理操作符：流聚合与哈希聚合。

 

标量聚合

标量聚合通过在选择列表中没有GROUP BY语句的聚合函数查询。标量聚合总是返回一个单一的行。SQL Server总是使用流聚合操作符实现标量聚合。

下面是个简单的例子：

 

select COUNT(*) from [Orders]

 

 

这个查询产生下面的计划：

 

 

 

流聚合操作符就是计算输入行的大小并返回结果。流聚合实际计算Bigint的总数([Expro1004])。计算的数量需要把这个结果转换成期待的Int型。注意到标量流聚合是一个没有页节点操作符例子中的一个，可以在空输入集时产生一个输入。

 

如何实现其他的简单标量聚合函数，例如MIN、MAX和SUM。一个单一流聚合操作符可以在同一时刻计算多个标量聚合。

 

select MIN(Orderdate), MAX(orderdate) from [Orders]

 

 

下面是使用单一流聚合操作符的查询计划：

 

 

 

注意到SQL Server不需要对MIN和MAX集合转换结果，因为这些集合是基于订单日期列数据类型计算的。

一些集合，例如AVG实际上是从其他两个集合如SUM和COUNT中计算的：

 

select AVG(Freight) from [orders]

 

 

请注意计划中的计算标量操作符是如何从sum和count中计算平均值的：

 

 

 

 

CASE表达式是必须的，用以确定SQL Server不会试图被零整除。

        

 

select SUM(Freight) from [orders]

 

        

　　　尽管本质上SUM不需要计算，但仍然要进行计数：

         以下这个查询计划中的CASE表达式如果应用COUNT来确保SUM在没有行时返回NULL，而不是零。

 

 

Scalar Distinct

         如果对标量聚合添加DISTINCT关键字进行查询，计算以运送的订单的不同城市的数量，查询如下：

 

select COUNT(distinct [shipcity]) from [orders]

 

 

以上查询产生的查询计划如下：

 

 

 

因为查询必须对那些ShipCity列上有唯一值的行计算，SQL Server 增加显示排序操作符消除ShipCity上的重复值，显示排序是SQL Server用来消除排序重复行的常见方法之一。在对输入集进行排序后就很容易去除重复的行，因为重复的行和另外一个行是邻接的。

 

多个 Distinct

 

 

 

select COUNT(distinct [shipaddress]), COUNT(distinct[shipcity]) 

   from [orders]

 

 

SQL Server可以通过消除ShipAddress列上重复值的行来计算COUNT（DISTINCT[ShipAddress]）。类似的，SQL Server可以通过消除ShipCity列上的重复值来计算COUNT（DISTINCT[ShipTity]）。但是，假设给出的这两个行的集合不同，SQL Server如果同时对这两个行进行计算，SQL Server做不到，它必须先计算一个聚合的结果，然后计算另一个，再把这两个结果合并成一个单一的输出：

 

 

嵌套循环连接的两个输入从原始的查询中计算两个数量，其中一个输入去掉重复的，计算ShipAddress列的数量，另一个输入去掉重复的，计算ShipCity列的数量。嵌套循环连接没有连接谓词，它是交叉连接。因为嵌套循环连接的两个输入每一个产生单一的行，且它们的标量集合-交叉连接的结果也会一个单一的行。交叉连接就是把结果的两行“粘贴”为单一的行。

 

流聚合

 

算法         流聚合依赖与获得存储在GROUP BY列中的数据。如同合并连接，如果一个查询包含GROUP BY语句多于一行，流聚合会使用任何包含所有行的排序次序。例如，流聚合会对Col1列和Col2列分组，而数据存储在（Col1，Col2）或（Col2，Col1）上。与合并连接类似，排序次序也会被一个索引或一个显示排序操作符影响。排序次序将确保列GROUP BY有着相同值的行集合彼此相连。

 

例如，为了计算SUM，流聚合会考虑每个输入行。如果输入行属于目前的组（输入行的group by列和以前行的group by列是匹配的），则流聚合可通过从输入行中增加合适的值来更新目前的SUM。如果输入的行属于一个新的组（输入的行group by列和以前行的group by列不匹配），流聚合会输出目前的SUM，把SUM设为零，开始新的组。

 

简单示例  下面的查询将计算对于每个地址运送订单的数量：

 

select [ShipAddress], [ShipCity], COUNT(*)

From [Orders] group by [ShipAddress], [ShipCity]

 

 

下面是这个查询的计划：

 

 

除了SQL Server在聚合前需要对数据排序外，这个计划和之前看到的标量聚合的查询基础一样，可以认为标量聚合是一个包含所有行的一个大组，因此，对于一个标量聚合，没有必要将行排序为不同的组。（未完待续）

SQL Server 2005技术内幕：查询、调整和优化5——用C#实现SQL查询

 

前面几篇文章作为自己的读书笔记（SQL Server 2005技术内幕：查询、调整和优化），基本都是书上的内容，没敢放在首页上。现在学习SQL Server都是理论知识，自己有个习惯，一直看理论看下去不容易吸收，所以看到“聚合”这一节决定写写代码，用来加深对SQL Server执行计划的理解。

首先看看我在在SQL Server中是怎么处理连接查询和分组查询的。

 

代码

--建表
if OBJECT_ID('tableA') is not null
   drop table tableA
Create table tableA(
   ID int identity primary key,
   Name varchar(30)
)
 
if OBJECT_ID('tableB') is not null
   drop table tableB
Create table tableB(
   ID int,
   summary varchar(30)
)
 
--插入测试数据
insert into tableA(Name) select 'A' union all select 'B' union all select 'C'
insert into tableB(ID, summary)
   select 1, 'test-001' union all
   select 1, 'test-002' union all
   select 3, 'test-001' union all
   select 4, 'test-001'
 
 
--左连接查询
select A.ID, A.Name, B.summary from tableA A left join tableB B on(A.ID = B.ID)
 
--根据ID分组查询
select ID, COUNT(1) number from tableB group by ID
 

 

 

上面一段SQL非常简单，创建了两张表，TableA和TableB，字典非常简单，TableB中ID是TableA的外键，这里没有主动建立外键关系。插入几条测试数据，其中实现左连接和分组查询结果如下：

 

下面我们用C#代码是连接查询和分组功能：

 

代码

        class TableA
        {
            public int ID { get; set; }
            public Char Name { get; set; }
        }
 
        class TableB
        {
            public int ID { get; set; }
            public String summary { get; set; }
        }

 

 

 

第一部创建两个实体类与SQL Server中两个实体相对应，然后插入测试数据：

 

       代码

 private IList<TableA> tableA = null;
        private IList<TableB> tableB = null;
 
        public SQLQuery()
        {
            tableA = new List<TableA>()
            {
                new TableA(){ID=1, Name = 'A'},
                new TableA(){ID=2, Name = 'B'},
                new TableA(){ID=3, Name = 'C'}
            };
            tableB = new List<TableB>()
            {
                new TableB(){ID=1, summary = "test-001"},
                new TableB(){ID=1, summary = "test-002"},
                new TableB(){ID=3, summary = "test-001"},
                new TableB(){ID=4, summary = "test-001"},
            };
        }

 

实现连接查询的代码如下：

 

代码

        /// <summary>
        /// 实现SQL中左连接的效果
        /// </summary>
        public void JoinQuery()
        {
            foreach (var item in tableA)
            {
                foreach (var item2 in tableB)
                    if (item.ID == item2.ID)
                        Console.WriteLine("ID: {0}; Name: {1}; Summary: {2}", item.ID, item.Name, item2.summary);
                if ((from s in tableB where s.ID == item.ID select s).Count() == 0)
                    Console.WriteLine("ID: {0}; Name: {1}; Summary: {2}", item.ID, item.Name, null);
            }
        }

 

 

嵌套循环中的两个集合tableA和tableB是数据库中两个表，第二循环中比对连接键，相同则输出，第一个循环中最后的那个if用来检查tableB集合中是存在引用tableA的当前ID，如果没有输出一行有ID，Summary为null的记录，这里是左连接的特例，如果是普通的内连接，这一行可以去掉。

 

实现分组查询的代码如下：

 

代码

/// <summary>
        /// 分组查询
        /// </summary>
        public void GroupByQuery()
        {
            int[] ID = (from s in tableB group tableB by s.ID into groupKey select groupKey.Key).ToArray();
            int IDIndex = 0;
            int count = 0;
            foreach (var item in tableB)
            {
                if (ID[IDIndex] != item.ID)
                {
                    Console.WriteLine("ID: {0}; Number: {1}", ID[IDIndex], count);
                    count = 0;
                    IDIndex++;

                    if (ID[IDIndex] == item.ID)
                        count++;

                }
                else
                    count++;

            }
            Console.WriteLine("ID: {0}; Number: {1}", ID[IDIndex], count);
        }

 

 

这段代码开始对分组键进行去重，这点可以参考我的上篇文章，采用是的流聚合，这里不多加解释。变量IDIndex是用来获取当前ID的索引，Count计算分组情况，也是我们分组查询的目的所在。这里的算法可能有问题，有重复的地方，但功能实现，那位大侠有兴趣可以帮忙改改。

小弟初学SQL Server，之前对数据库的理解仅限于CRUD，这是第一次写文章，写得不对或不好的地方大家帮忙指点下！

SQL Server 2005技术内幕：查询、调整和优化6——子查询

 

子查询是强有力的工具，它可以书写许多表达式和许多复杂的查询。有很多不同类型的子查询和很多使用子查询的不同方式。

子查询是基本的连接。然而，一些子查询产生更复杂的连接使用非常不同寻常的连接特性。

在讨论具体例子之前，可以从不同方面对子查询进行分类。子查询采用3种计划来进行分类。

l  不相关 vs 相关子查询。不相关子查询不依赖外部查询，可以独立于外部查询而被评估，并且为外部查询的每一行返回相同的结果。只能从外部查询的上下文中来被估计，并且可能为外部查询的每一行返回不同的结果。

l  标量 vs 多行子查询。标量子查询返回或期望返回单一行（也就是单行），然而，多行子查询可能返回一个结果集。

l  有子查询出现的外部查询分句。子查询可以被用于几乎所有的上下文，包括SELECT列表和主查询中的FORM、WHERE、ON和HAVING子句。

 

不相关标量子查询

通过一些简单的不相关标量子查询来讨论子查询。下面的查询返回运费超过所有订单运费平均值的订单列表。

SELECT o1.[OrderId], o1.[Freight]

FROM [Orders] o1

where o1.[Freight] >

   (

      select AVG(o2.Freight) from [Orders] o2

   )

注意到我们可以提取平均费用计算子句并作为完全独立的查询来执行。因此，这个子句查询不相关。同时，这个子查询使用了标量汇总，因此，返回正好是一行，这个子查询也是一个标量子查询。下面是这种查询计划：

 

正如你期望的，SQL Server首先计算嵌套循环连接外面的平均运费来执行这个查询。这个查询需要对Order表（alias[O2]）执行一次扫描。因为这个查询计算得到精确的一行，SQL Server然后在内部连接上扫描Order表（alias[O1]）一次。平均的运费计算结果被子查询（存储在[Expr1004]里）用来过滤来自第二次扫描的行。

 

另外，还有一种不相关标量子查询。这次希望找的哦啊那些已经根据名字选择的特定用户的订购订单。

 

SELECT o.[OrderId]

FROM [Orders] o

where o.[CustomerID] >

   (

      select C.[CustomerID] from [Customers] C

      where C.[ContactName] = 'Maria Anders'

   )

 

注意到这次子查询没有标量汇总来保证结果恰好是一行。此外ContactName上没有唯一索引，所以这个子查询实际返回多行是完全可能的。然而，该子查询被用在一个相等谓词上下文，是一个标量子查询，且必须返回单行。如果有两个客户都叫“Maria Anders”（没有这样的名字），这个查询一定失败。SQL Server保证子查询结果最多有一行，这是通过使用流汇总来计算行数的，然后给改计划添加一个断言操作：

 

如果断言操作符发现子查询返回多于一行（也就是说，如果[Expr1005]>(1)是真），将引发错误。

 

当子查询被当用一个表达式时，这是不被允许的。

 

注意到SQL Server使用断言操作符来检测其他条件，例如，约束条件（检查，参照完整性等），普通表达式的最大递归层次，警告重复键插入到建立在IGNORE_DUP_KEY选项的索引上。

 

ANY聚合是一种特殊的内部唯一聚合，返回任何行。因为这种计划如果扫描Customers表结果集多余一行将产生一个错误，对ANY汇总没有实际的影响。这种计划可以简单地使用MIN或MAX聚合得到同样的结果。然而，一些聚合是必须的，因为流聚合期望每一个输出列在聚合里或在GROUP BY子句里。下面的查询因为同样的原因不能被编译：

select COUNT(*), C.[CustomerId]

from [Customers] C

where C.[ContactName] = 'Maria Anders'

选择列表中的列'Customers.CustomerID' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。

 

相关标量子查询

 

SQL Server如何评估简单的不相关子查询，让我们探讨如果有一个相关标量子查询和尝试的第一个子查询相似，但是这次返回的是那些超过所有先前加入订单费用的平均值订单。

 

这次SQL Server不能独立地执行子查询。因为关联到列OrderDate上，子查询为主查询中每一行返回不同的结果。SQL Server首先、评估子查询，然后执行主查询。这次SQL Server首先评估主查询，然后根据主查询中的每一行评估一次子查询。

 

这个计划不是很复杂。Index spool立即在[02]上进行扫描，是一种及时index spool或临近索引轴。在Orders Date列上创建一个临时索引。被叫做及时index spool，因此它“及时”装入整个输入集，并且一被打开就建立临时索引。

这个索引使得子查询随后的评估更加高效，因为在列OrderDate上有一个谓词。流汇总为每一个子查询来计算平均运费。在流汇总上的索引spool是一个延迟的索引spool，它仅仅缓存子查询的结果。如果再次遇到任何OrderDate，将返回保存在缓存里的结果而不是重新计算子查询。它被叫做延迟的spool，因为“以延迟方式”只装载需要的结果。最后，最上面计划的过滤器与子查询结果集（[Expr1004]）的每一个订单进行运费比较，并返回满足要求的行。

 

下面是其他相关标量子查询的例子。假设要找到被同一个客户加入的超过所有订单平均值的订单。

SELECT o1.[OrderId], o1.[Freight]

FROM [Orders] o1

where o1.[Freight] >

   (

      select AVG(o2.[Freight]) from [Orders] o2

      where o2.[CustomerID] = o1.[CustomerID]

   )

这个查询和前面一个非常相似，但却得到一个本质上完全不同的计划：

 

再次这个计划也不复杂。最外层的嵌套循环连接根据列CustomerId对聚集索引扫描的行进行分类。段操作符列CustomerId有相同值的行分成组（或段）。因为这些行都是被排序的，具有相同CustomerId值的行的集合将是连续的。接下来，表spool—一个段spool-读并保存其中一组拥有相同CustomerId值的行。

当spool完成载入一组行时，将为这个组返回单一的行（注意到段spool是唯一一种展示这种行为的spool类型，它不考虑读入多少行，而只显示单一的行）。这是最外层的嵌套循环连接执行内部的输入。两个页级表spools—-从属的spools—-重新执行原始段spool保存的组内的行。

通过使用段 spool，优化器创建一种仅需要扫描表Orders一次的计划。谈到spool时，有这样一个例子，在这种计划里重新计算在不同位置的相同行的集合作为一个普通的子表达式spool。不是所有的普通子查询spools都是段spools。