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 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')
   2:     BEGIN
   3:         DROP  Function  dbo.fn_getproduct
   4:     END
   5: GO
   6:  
   7: CREATE Function dbo.fn_getproduct 
   8: (
   9:     @product_id Int
  10: )
  11: RETURNS TABLE
  12:  
  13: AS RETURN 
  14:  
  15: SELECT * FROM Production.Product WHERE ProductID = @product_id
  16:  
  17: GO

然后我们做如下的查询:对Production.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
   6:  
   7: CREATE Function dbo.fn_getproduct 
   8: (
   9:     @product_id Int
  10: )
  11: RETURNS TABLE
  12:  
  13: AS RETURN 
  14:  
  15: SELECT * FROM Production.Product WHERE ProductID = @product_id * -1
  16:  
  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
   3: (
   4:      query
   5: )

E.G.

   1: WITH CTE_Black_Product
   2: AS
   3: (
   4:     SELECT * FROM Production.Product WHERE Color = 'Black'
   5: )
   6:  
   7: SELECT * FROM CTE_Black_Product

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

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

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

   1: WITH CTE_SalesOrder_Count
   2: AS
   3: (
   4:     SELECT CustomerID, Count(*) As OrderCount
   5:     FROM Sales.SalesOrderHeader
   6:     GROUP BY CustomerID
   7: )
   8:  
   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
   4: (
   5:     SELECT MIN(PRODUCT_ID)
   6:     FROM dbo.PRODUCT p
   7:     WHERE dbo.PRODUCT.PRODUCT_NAME = p.PRODUCT_NAME
   8: )

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

   1: WITH CTE_PRODUCT(PRODUCT_ID,PRODUCT_NAME)
   2: AS
   3: (
   4:     SELECT MIN(PRODUCT_ID) AS PRODUCT_ID,PRODUCT_NAME
   5:     FROM dbo.PRODUCT
   6:     GROUP BY PRODUCT_NAME
   7:     HAVING COUNT(*)>1
   8: )
   9:  
  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

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

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

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

   1: WITH CTE_EMPLOYEE(EMPLOYEE_ID, EMPLOYEE_NAME,REPORT_TO)
   2: AS
   3: (
   4:     SELECT * 
   5:     FROM dbo.EMPLOYEE
   6:     WHERE EMPLOYEE_NAME = 'A'
   7:     
   8:     UNION ALL
   9:     
  10:     SELECT dbo.EMPLOYEE.*
  11:     FROM dbo.EMPLOYEE
  12:     JOIN CTE_EMPLOYEE
  13:     ON dbo.EMPLOYEE.REPORT_TO = CTE_EMPLOYEE.EMPLOYEE_ID
  14: )
  15:  
  16: SELECT * 
  17: FROM CTE_EMPLOYEE
  18: WHERE EMPLOYEE_NAME > 'A'
  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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Pythonista

Mysql基础

数据库(database,DB)是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储,具有较小的冗余,较高的数据...

2082
来自专栏青玉伏案

Oracle之PL/SQL学习笔记

  自己在学习Oracle是做的笔记及实验代码记录,内容挺全的,也挺详细,发篇博文分享给需要的朋友,共有1w多字的学习笔记吧。是以前做的,一直在压箱底,今天拿出...

2018
来自专栏数据库

mysql数据操作语句

?imageMogr2/blur/1x0/quality/75|watermark/1/image/aHR0cDovL29zNzhmNGhueS5ia3QuY2...

1965
来自专栏互联网开发者交流社区

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

2843
来自专栏极客慕白的成长之路

知识点、SQL语句学习及详细总结

1992
来自专栏文渊之博

探索SQL Server元数据(三):索引元数据

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,...

2091
来自专栏令仔很忙

SQL JOIN

在机房收费系统个人重构的时候,很多的功能都需要根据数据库中的一个表中的信息,去查询另一个表中相匹配的信息,我们用到了视图,但是你有没有注意到下面的SQL语句呢...

971
来自专栏java学习

数据库_mysq单表操作

1.1 排序 通过order by语句,可以将查询出的结果进行排序。放置在select语句的最后。 格式: SELECT * FROM 表名 ORDER ...

3285
来自专栏学习有记

T-SQL进阶:超越基础 Level 2:编写子查询

1061
来自专栏抠抠空间

MySQL之表的约束

一 介绍 约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY (PK) 标识该字段为该表的...

2896

扫码关注云+社区

领取腾讯云代金券