T-SQL Enhancement in SQL Server 2005[上篇]

发布时间：2020-12-16 09:07:38 所属栏目：asp.Net 来源：网络整理

导读：较之前一版本， SQL Server 2005 可以说是作出了根本性的革新。对于一般的编程人员来说，最具吸引力的一大特性就是实现了对 CLR 的寄宿，使我们可以使用任意一种 .NET Programming Language 来编写 Stored Procedure 、 Function 、 Trigger 、 User Defined

较之前一版本，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获得产品信息。

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

   3:         DROP  Function  dbo.fn_getproduct

   5: GO

   7: CREATE Function dbo.fn_getproduct

   9:     @product_id Int

  11: RETURNS TABLE

  13: AS RETURN

  15: FROM Production.Product WHERE ProductID = @product_id

  17: GO

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

2: FROM Production.WorkOrder WorkOrder

WHERE ProductID = @product_id * -1

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的语法结构：

   4:      query

      AS

      WHERE Color = 'Black'

         6:? 

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

        

          
            AS

            SELECT CustomerID,Count(*) As OrderCount

               6:     GROUP BY CustomerID

               8:? 

              10: FROM Sales.Customer INNER JOIN CTE_SalesOrder_Count

            SELECT * 

    WHERE PRODUCT_ID NOT IN

    SELECT MIN(PRODUCT_ID)

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

            AS

        MIN(PRODUCT_ID) AS PRODUCT_ID,PRODUCT_NAME

        BY PRODUCT_NAME

           8: )

        SELECT dbo.PRODUCT.PRODUCT_ID,dbo.PRODUCT.PRODUCT_NAME

          12: JOIN CTE_PRODUCT

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

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

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

  SELECT * 

    WHERE EMPLOYEE_NAME = 'A'

       8:     UNION ALL

      10:     SELECT dbo.EMPLOYEE.*

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

      15:? 

      18: WHERE EMPLOYEE_NAME >   19: OR EMPLOYEE_NAME < 'A'

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

Reference: 《Programming Microsoft SQL Server 2005》 By Andrew J. Brust & Stephen Forte

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!