[聚合文章] T-SQL Enhancement in SQL Server 2005[上篇]

较之前一版本， SQL Server 2005 可以说是作出了根本性的革新。对于一般的编程人员来说，最具吸引力的一大特性就是实现了对 CLR 的寄宿，使我们可以使用任意一种 .NET Programming Language 来编写 Stored Procedure 、 Function 、 Trigger 、 User Defined Type 等等。但是并不意味着我们使用多年的 T-SQL 即将被淘汰，而事实上 T-SQL 仍然是我们最为常见的基于 Database 的编程语言。 为了使编程人员更容易地使用 T-SQL 来实现一些较为复杂的功能， SQL Server 2005 在 T-SQL 进行了一系列的改进，这篇文章将概括性地介绍这些 T-SQL Enhancement 。

为了使读者对这些新引入的 T-SQL 特性有一个大概的了解，我先概括性地列出这些特性：

一、APPLY Operator

APPLY 这个操作符被置于一个查询的 FROM 语句中，对于查询出的每条数据行，都去调用一个 Table Value Function （ TVF ），并将 TVF 的数据附加在现有的查询结果上。 APPLY 通常用于这样的场景中：查询的结果一部分包含在一个 Table 或者 View 中 ，另一部分则通过一个 TVF 来获得，通过 TVF 获得的记录是基于 Table 或者 View 中每条记录的某个 Column 的数据，也就是说我们把 Table 或者 View 的某个 Column 的值作为调用 TVF 的参数。这实际上将通过 TVF 获得的 Table 作为现有 Table 或者 View 的 Outer table ，将它们连接（ Join ）在一起，而连接它们的 Key 就是作为 TVF 参数传入的 Column 。

我们知道 Join 分为 Inner Join 和 Outer Join ，他们分别对应着 CROSS APPLY 和 OUTER APPLY 。如果对于某个条记录， TVF 发挥的是一个空的 Rowset ，对于 CROSS APPLY ，该记录将不会出现在最终的结果中，而对于 OUTER APPLY 来说，最终的查询结果将包含该条记录，只是基于 TVF 的 Column 的值为 NULL 。

可能文字描述太过抽象，我们现在通过例子来进一步理解 APPLY Operator 。下面的例子基于的 Database 是 SQL Server 2005 的 Sample Database ： AdventureWorks 。（注：后续的例子如未作特殊的说明，均使用的是该 Database ）。 我们首先创建一个 TVF ： dbo.fn_getproduct 。根据 Product ID 获得产品信息。

   1: IF EXISTS (SELECT * FROM sysobjects WHERE type = 'IF' AND name = 'fn_getproduct')

   2:     BEGIN

   3:         DROP  Function  dbo.fn_getproduct

   4:     END

   5: GO

   7: CREATE Function dbo.fn_getproduct

   9:     @product_id Int

  11: RETURNS TABLE

  13: AS RETURN

  15: SELECT * FROM Production.Product WHERE ProductID = @product_id

  17: GO

然后我们做如下的查询：对P roduction . WorkOrder 作查询，并列出对应的 Product 的信息：

   1: SELECT WorkOrderID,WorkOrder.ProductID,ProductNumber,[Name],OrderQty

   2: FROM Production.WorkOrder WorkOrder

   3: CROSS APPLY dbo.fn_getproduct(WorkOrder.ProductID)

下面是查询结果：

我们可以看到 ProductNumber 和 Name 两个 Column 实际上是来自 TVF 中的，其余才是来自于 Production.WorkOrder 。如果把 TVF 看作一个 Table ，通过查询结果我们可以看出，上面的查询相当于把这个 Table 和 Production.WorkOrder 通过 ProductID 作了一个 Join 。到底是 Inner Join ，还是 Outer Join ？我们对这个 TVF 作如下修改，使其在正常的情况下返回一个空的结果集( WHERE ProductID = @product_id * -1)：

   1: IF EXISTS (SELECT * FROM sysobjects WHERE type = 'IF' AND name = 'fn_getproduct')

   2:     BEGIN

   3:         DROP  Function  dbo.fn_getproduct

   4:     END

   5: GO

   7: CREATE Function dbo.fn_getproduct

   9:     @product_id Int

  11: RETURNS TABLE

  13: AS RETURN

  15: SELECT * FROM Production.Product WHERE ProductID = @product_id * -1

  17: GO

再次运行上面的查询，我们会发现最终返回的结果为空：

看来 CROSS APPLY 使用的是 Inner Join 。 我们现在来试试 OUTER APPLY ：

   1: SELECT WorkOrderID,WorkOrder.ProductID,ProductNumber,[Name],OrderQty

   2: FROM Production.WorkOrder WorkOrder

   3: OUTER APPLY dbo.fn_getproduct(WorkOrder.ProductID)

下面是最终的输出结果，我们发现所有的Order 记录被返回，通过 TVF 获得的 ProductNumber 和 Name 的值为 NULL 。这充分说明了 OUTER APPLY 采用的是 OUTER JOIN

。

二、Common Table Expression

Common Table Expression （ CTE ）可以看成是一个临时创建的 View ，他的生命周期仅仅限于当前 Context 。一旦 CTE 被创建，你可以将它当成一般的 Table ，大部分基于 Table 的操作都可以运用于 CTE 。下面是创建 CTE 的语法结构：

   1: WITH cte_name(column name list)

   2: AS

   4:      query

E.G.

   1: WITH CTE_Black_Product

   2: AS

   4:     SELECT * FROM Production.Product WHERE Color = 'Black'

   7: SELECT * FROM CTE_Black_Product

CTE 具有广泛的运用，他往往具有将问题化繁为简的魔力。下面介绍几个典型的运用：

1、 将复 杂的 Aggregate 置于 CTE 中，将复杂的问题分解为多个步骤。

如果我们现在需要统计每个客户发出的订单数量（相关数据存储于 Sales.SalesOrderHeader 中），同时输出客户的个人信息（相关数据存储于 Sales.Customer 中）。虽然这样的功能很简单，但他体现了一种思想，把一部完成略显复杂的功能进程分解成多个简单的步骤。

   1: WITH CTE_SalesOrder_Count

   2: AS

   4:     SELECT CustomerID, Count(*) As OrderCount

   5:     FROM Sales.SalesOrderHeader

   6:     GROUP BY CustomerID

   9: SELECT Sales.Customer.CustomerID, AccountNumber,OrderCount

  10: FROM Sales.Customer INNER JOIN CTE_SalesOrder_Count

  11: ON CTE_SalesOrder_Count.CustomerID = Sales.Customer.CustomerID

2、 使用 CTE 代替自连接，以便更易于理解。

假设我们有一个 Product 表用于存储每个 Product 的信息，每个 Product 有一个唯一标识 Product_ID 和一个不唯一的 Product_Name 。由于不同的 Product 可能重名，倘若我们有这样的一个需求：需要将重名的记录（除了具有最小 ID 的那个）删除，从而保证其名称的唯一性。我们来看看如何保这些需要上出的记录筛选出来。 Product 表的记录如下， ID 为 1 和 4 的两条记录重名，现在我们的目的是把 ID 为 4 的记录筛选出来。

在不考虑 CTE 的情况下，我们通过下面的 SQL 实现这个功能，这个 SQL 采用了自连接。虽然 SQL 看起来很简洁，但是相信有一些人第一次看到这样一个 SQL ，不能立即理解。

   1: SELECT *

   2: FROM dbo.PRODUCT

   3: WHERE PRODUCT_ID NOT IN

   5:     SELECT MIN(PRODUCT_ID)

   6:     FROM dbo.PRODUCT p

   7:     WHERE dbo.PRODUCT.PRODUCT_NAME = p.PRODUCT_NAME

但是如果我们采用了CTE ，通过下面一段 SQL 来实现，虽然代码多了点，但是从语义上看要易于理解一点：首先把重名的选出来，在和 Product 作一次连接。

   1: WITH CTE_PRODUCT(PRODUCT_ID,PRODUCT_NAME)

   2: AS

   4:     SELECT MIN(PRODUCT_ID) AS PRODUCT_ID,PRODUCT_NAME

   5:     FROM dbo.PRODUCT

   6:     GROUP BY PRODUCT_NAME

   7:     HAVING COUNT(*)>1

  10: SELECT dbo.PRODUCT.PRODUCT_ID, dbo.PRODUCT.PRODUCT_NAME

  11: FROM dbo.PRODUCT

  12: INNER JOIN CTE_PRODUCT

  13: ON CTE_PRODUCT.PRODUCT_NAME = dbo.PRODUCT.PRODUCT_NAME

  14: AND dbo.PRODUCT.PRODUCT_ID > CTE_PRODUCT.PRODUCT_ID

比如一个公司的员工体系就是一个包含上下级关系的具有层次化的树形结构。假设我们有如下一个 EMPLOYEE 表，通过 REPORT_TO 体现每个员工的上下级关 系（假设Empoyee_Name具有唯一性）。

我们现在的需求是：列出员工 A 的所有下级。 为了实现这样的一个功能，我们需要以一种特殊的结构来创建 CTE ：

   1: WITH CTE_EMPLOYEE(EMPLOYEE_ID, EMPLOYEE_NAME,REPORT_TO)

   2: AS

   4:     SELECT *

   5:     FROM dbo.EMPLOYEE

   6:     WHERE EMPLOYEE_NAME = 'A'

   8:     UNION ALL

  10:     SELECT dbo.EMPLOYEE.*

  11:     FROM dbo.EMPLOYEE

  12:     JOIN CTE_EMPLOYEE

  13:     ON dbo.EMPLOYEE.REPORT_TO = CTE_EMPLOYEE.EMPLOYEE_ID

  16: SELECT *

  17: FROM CTE_EMPLOYEE

  18: WHERE EMPLOYEE_NAME > 'A'

  19: OR EMPLOYEE_NAME < 'A'