TSQL Enhancement in SQL Server 2005[上篇]

http://www.cnblogs.com/artech/archive/2007/08/23/867412.html

较之前一版本，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
• Common Table Expression
• PIVOT Operator
• TOP Clause Enhancement
• Ranking
• DDL Trigger
• Others

一、            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获得产品信息。 

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'IF' AND name = 'fn_getproduct')
    BEGIN
        DROP  Function  dbo.fn_getproduct
    END
GO

CREATE Function dbo.fn_getproduct 
(
    @product_id Int
)
RETURNS TABLE

AS RETURN 

SELECT * FROM Production.Product WHERE ProductID = @product_id

GO

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

SELECT WorkOrderID,WorkOrder.ProductID,ProductNumber,[Name],OrderQty
FROM Production.WorkOrder WorkOrder
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)：

 

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'IF' AND name = 'fn_getproduct')
    BEGIN
        DROP  Function  dbo.fn_getproduct
    END
GO

CREATE Function dbo.fn_getproduct 
(
    @product_id Int
)
RETURNS TABLE

AS RETURN 

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

GO

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

看来CROSS APPLY使用的是Inner Join。

我们现在来试试OUTER APPLY： 

SELECT WorkOrderID,WorkOrder.ProductID,ProductNumber,[Name],OrderQty
FROM Production.WorkOrder WorkOrder
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的语法结构：

WITH cte_name(column name list)
AS
(
     query
)

E.G.

WITH CTE_Black_Product
AS
(
    SELECT * FROM Production.Product WHERE Color = 'Black'
)

SELECT * FROM CTE_Black_Product

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

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

如果我们现在需要统计每个客户发出的订单数量（相关数据存储于Sales.SalesOrderHeader

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

WITH CTE_SalesOrder_Count
AS
(
    SELECT CustomerID, Count(*) As OrderCount
    FROM Sales.SalesOrderHeader
    GROUP BY CustomerID
)

SELECT Sales.Customer.CustomerID, AccountNumber,OrderCount
FROM Sales.Customer INNER JOIN CTE_SalesOrder_Count
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，不能立即理解。

SELECT * 
FROM dbo.PRODUCT
WHERE PRODUCT_ID NOT IN
(
    SELECT MIN(PRODUCT_ID)
    FROM dbo.PRODUCT p
    WHERE dbo.PRODUCT.PRODUCT_NAME = p.PRODUCT_NAME
)

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

WITH CTE_PRODUCT(PRODUCT_ID,PRODUCT_NAME)
AS
(
    SELECT MIN(PRODUCT_ID) AS PRODUCT_ID,PRODUCT_NAME
    FROM dbo.PRODUCT
    GROUP BY PRODUCT_NAME
    HAVING COUNT(*)>1
)

SELECT dbo.PRODUCT.PRODUCT_ID, dbo.PRODUCT.PRODUCT_NAME
FROM dbo.PRODUCT 
INNER JOIN CTE_PRODUCT
ON CTE_PRODUCT.PRODUCT_NAME = dbo.PRODUCT.PRODUCT_NAME
AND dbo.PRODUCT.PRODUCT_ID > CTE_PRODUCT.PRODUCT_ID

3．          用于具有层次结构记录的递归查询

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

我们现在的需求是：列出员工A的所有下级。

为了实现这样的一个功能，我们需要以一种特殊的结构来创建CTE：

WITH CTE_EMPLOYEE(EMPLOYEE_ID, EMPLOYEE_NAME,REPORT_TO)
AS
(
    SELECT * 
    FROM dbo.EMPLOYEE
    WHERE EMPLOYEE_NAME = 'A'
    
    UNION ALL
    
    SELECT dbo.EMPLOYEE.*
    FROM dbo.EMPLOYEE
    JOIN CTE_EMPLOYEE
    ON dbo.EMPLOYEE.REPORT_TO = CTE_EMPLOYEE.EMPLOYEE_ID
)

SELECT * 
FROM CTE_EMPLOYEE
WHERE EMPLOYEE_NAME > 'A'
OR EMPLOYEE_NAME < 'A'

我们发现CTE中主体部分由两个SELECT语句组成，我们把第一个叫做Anchor Member（AM），AM不会递归，只会执行一次，本例中筛选出了级别最高的A；另一个SELECT语句叫做Recursive Member（RM），RM通过CTE本身和EMPLOYEE表建立连接，所以RM会采用递归的方式执行。