SQL SERVER技术内幕之5 表表达式

    表表达式是一种命名的查询表达式，代表一个有效的关系表。可以像其他表一样，在数据处理语句中使用表表达式。SQL Server支持4种类型的表表达式:派生表(derived table)、公用表表达式(CTE,common table expression)、视图以及内联表值函数(inline TVF,inline table-valued function)。

    表表达式并不是物理上真实存在的什么对象，它们是虚拟的。对于表表达式的查询在数据库引擎内部都将转换为对底层对象的查询。使用表表达式的好处通常体现在代码的逻辑方面，而不是性能方面。

    1.派生表

    派生表(也称为表子查询)是在外部查询的FROM子句中定义的，派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表也就不存在了。 定义派生表的查询语句要写在一对圆括号内，后面跟着AS子句和派生表的名称。例如，以下代码定义了一个名为USACusts的派生表，它是一个返回所有美国客户的查询:

SELECT *
FROM (SELECT custid, companyname
      FROM Sales.Customers
      WHERE country = N'USA') AS USACusts;

    要有效地定义任何类型的表表达式，查询语句必须满足三个要求:

    (1).不保证有一定的顺序。表表达式代表的是一个表，而关系表中的行是没有固定顺序的。ANSI SQL不允许在用于定义表表达表的查询语句中有ORDER BY子句，T-SQL大体上遵守了这一限制，只有一个例外情况--当在语句中指定了TOP。在带有TOP选项的查询语句中，ORDER BY子句的逻辑目的只有一个:为TOP选项定义要筛选出哪些行。

    (2).所有的列必须有名称。

    (3).所有的列名必须是唯一的。

    1.1 分配列别名

    (1)用内联别名的形式对派生表进行查询

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
      FROM Sales.Orders) AS D
GROUP BY orderyear;

    (2)用外部命名格式对派生表进行查询

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
      FROM Sales.Orders) AS D(orderyear, custid)
GROUP BY orderyear;

 

    2.公用表表达式(CTE)

    以下代码定义了一个名为USACusts的CTE,它的内部查询返回所有来自美国的客户，外部查询则选择了CTE中的所有行，示例如下:

WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts;

    2.1 分配列别名

    CTE也支持两种格式的列别名命名方式--内联格式和外部格式。对于内联格式，要指定<expression> AS <column_alias>;对于外部格式，在CTE名称后面的一对圆括号中指定目标列的列表。示例如下:

-- Inline column aliasing
WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)

-- External column aliasing
WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)

    2.2 定义多个CTE

    从表面来看，派生表和CTE之间的区别可能只是语义方面。不过CTE具有几个重要优势。优势之一就是:如果须要在一个CTE中引用另一个CTE,不须要像派生表那样进行嵌套，相反，只要简单地在同一WITH子句中定义多个CTE,并用逗号把它们分隔开。每个CTE可以引用在它前面定义的所有CTE，而外部查询则可以引用所有CTE。

WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70

    2.3 CTE的多引用

    CTE是先定义再查询，就外部查询的FROM子句来说，CTE是已经存在的，因此，可以引用同一个CTE的多个实例。

    2.4 递归CTE

    定义一个递归CTE至少需要两个查询，第一个查询称为定位点成员(anchor member),第二个查询称为递归成员(recursive member)。定位点成员查询只会被调用一次。递归成员是一个引用了CTE名称的查询。对CTE名称的引用代表的是在一个执行序列中逻辑上的"前一个结果集"。第一次调 用递归成员时，"前一个结果集"代表由定位成员返回的结果集，之后每次调用递归成员时，对CTE名称的引用代表对递归成员的前一次调用所返回的结果集。在 查询返回的结果上，两个成员查询必须在列的个数和相应列的数据类型上保持兼容。示例如下所示:

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 2(定位点成员)
  
  UNION ALL
  
  SELECT C.empid, C.mgrid, C.firstname, C.lastname
  FROM EmpsCTE AS P
    JOIN HR.Employees AS C
      ON C.mgrid = P.empid(递归成员)
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

    定位点成员对HR.Employees表进行查询，只返回雇员等于2的记录行。第一次调用递归成员，返回雇员2的直接下属，比如3和5。第二次调用递归成员，返回雇员3和5的直接下属，以此类推，直到递归成员返回一个空的结果集。

 

    3.视图

    到目前为止，我们讨论了两种类型的表表达式(派生表和CTE)，它们的作用范围都非常有限，仅限于在单个语句的范围内使用。只要包含这些表表达式的外部查询完成操作，它们便会随之消失。而视图和内联表值函数(inline TVF)是两种可重用的表表达式，它们的定义储存在一个数据库对象中，一旦创建，就些对象就是数据库的永久部分。示例如下所示:

USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
  DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';

    一般推荐在和视图在关的应用上下文中应用避免使用SELECT *语句，列是在编译视图时进行枚举的，新加的列可能不会自动添加到视图中。如果在底层表中添加了列，而且在视图中需要这些新加的列，则可以使用ALTER VIEW语句对视图进行相应的修改。

    3.1 视图选项

    (1)ENCRYPTION选项

    在创建或修改视图、存储过程、触发器用用户定义函数时，都可以使用ENCRYPTION选项。如果指定ENCRYPTION选项，SQL Server在内部会对定义对象的文本进行混淆处理，普通用户通过任何目录对象都无法直接看到这种经过混淆处理的文本，只有特权用户通过特殊手段才能访问创建对象的文本。以下代码可以返回对象的创建文本。

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));
GO

    但是当修改视图定义，并指定ENCRYPTION选项时，视图定义的文本则返回NULL

ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS

 

    (2)SCHEMABINDING选项

    一旦指定了这个选项，被引用的对象就不能删除，被引用的列也不能删除或修改。

ALTER VIEW Sales.USACusts WITH SCHEMABINDING

 

    (3)CHECK OPTION

    选项的目的是为了防止通过视图执行的数据修改与视图中设置的过滤条件发生冲突，如果没有设置CHECK OPTION选项，则可以通过USACusts视图插入一条UK的客户，这样的更新会传递到底层的数据表中。但是，修改后的客户将不能在视图中显示出来，因为它已经不再满足视图的查询过滤条件。如果想防止这种与视图的查询过滤条件相冲突的修改，只须在定义视图的查询语句末尾加上WITH CHECK OPTION即可。

-- Add CHECK OPTION to the View
ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO

 

    4.内联表值函数

    内联表值函数是一种可重要的表表达式，能够支持输入参数，支持输入参数以外，内联表值函数在其他方面都与视图类似。示例如下所示:

-- Creating fn_GetCustOrders function
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
  DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
  (@cid AS INT) RETURNS TABLE
AS
RETURN
  SELECT orderid, custid, empid, orderdate, requireddate,
    shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
    shipregion, shippostalcode, shipcountry
  FROM Sales.Orders
  WHERE custid = @cid;
GO

-- Test Function
SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;

   

    5.APPLY运算符

    APPLY运算符支持两种形式:CROSS APPLY和OUTER APPLY。APPLY运算符对两个输入表进行操作，其中第二个可以是一个表表达式，将它们分别称为左表和右表。CROSS APPLY实现了一个逻辑处理步骤：将右表表达式应用到左表中的每一行，再将结果集组合起来，生成一个统一的结果表。示例如下所示：

    以下代码使用CROSS APPLY返回每个客户最新的三个订单：

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  CROSS APPLY
    (SELECT TOP(3) orderid, empid, orderdate, requireddate 
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC) AS A;

    如果右表表达式返回的是一个空集，CROSS APPLY则不会返回相应左边的数据行。如果要在右表表达式返回空集时也照样返回相应左表中的行，则可以用OUTER APPLY运算符代替CROSS APPLY。

   

    总结

    借助表表达式可以简化代码，提高代码的可维护性，还可以封装查询逻辑。当需要使用表表达式，而且不计划重用它们的定义时，可以使用派生表或CTE。与派生表相比，CTE具有两个优点:CTE不用像派生表那样嵌套使用，此外，还可以引用同一CTE的多个实例，也派生表不能这么用。

    当需要定义可重用的表表达式时，可以使用视图和内联表值函数。如果不须要支持输入参数，则使用视图，相反则使用内联表值函数