【Microsoft SQL Server 2008 技术内幕：T-SQL语言基础】三、表表达式

sql2008 t-sql

Sql Server四种表表达式

• 派生表：derived table. from子句中的嵌套子查询
• 视图：view
• 通用表表达式：CTE, common table expression
• 内联表值函数：intline TVF, inline table-valued function

通用表表达式CTE

CTE (Sql2005中引入)

• 使用WITH子句定义，WITH后跟CTE名称（别名）
• 外部查询完成时，生命期结束
• 由于WITH子句有不同意义，容易引入歧义，所以建议CTE语句中明确地使用分号
• 因为没有物化的存在，通常对性能没有任何影响

-- CTE, definition
with C1(orderyear, custid) as ( 
  select year(orderdate), CustomerID
  from SalesLT.SalesOrderHeader
),
C2 as (
  select orderyear, count(DISTINCT custid) as numcusts
  from C1
  group by orderyear
)
select orderyear, numcusts
from C2
where numcusts > 30;

CTE的多引用

• 相对于派生表，因为CTE先于from子句定义，因此可以引用同一CTE的多个实例。
• 避免了像派生表那样需要维护多个子查询的副本

-- CTE, multi-reference
with YearlyCount as (
  select YEAR(orderdate) as orderyear
    , COUNT(distinct customerID) as numcusts
  from SalesLT.SalesOrderHeader
  group by YEAR(OrderDate)
)
select Cur.orderyear
  , Cur.numcusts as CurNumCusts
  , Prv.numcusts as PrvNumCusts
  , Cur.numcusts - Prv.numcusts as Growth
from YearlyCount as Cur
  left join YearlyCount as Prv
    on Cur.orderyear = Prv.orderyear + 1

CTE的递归

• 基本语法同CTE的一般定义。WITH内定义的查询将作为定位成员(anchor member)，它只会被调用一次，返回“第一个”前一个结果集
• WITH定义内部须再添加UNION ALL关键字和一个递归查询成员(recursive member)。递归成员被调用多次，直到递归结束
• 递归成员通过引用CTE名称达到递归的目的
• 两个查询成员必须保持列个数和数据类型的兼容性
• 外部查询中对CTE名称的引用表示对该递归查询结果集的引用
• 为避免递归出现死循环，Sql server设置了最大递归次数为100，超过100时会自动终止。
• 通过在外部查询的最后指定“OPTION MAXRECURSION n”改变该限制(n=0时取消限制)

-- CTE, Recursive
WITH CategoryCTE([ParentProductCategoryID], [ProductCategoryID], [Name]) AS (
  -- anchor member, executed only in the beginning
  SELECT [ParentProductCategoryID], [ProductCategoryID], [Name]
  FROM SalesLT.ProductCategory
  WHERE ParentProductCategoryID IS NULL
  UNION ALL -- to union all recursive query results
  -- recursive member
  SELECT C.[ParentProductCategoryID], C.[ProductCategoryID], C.[Name]
  FROM SalesLT.ProductCategory AS C
  INNER JOIN CategoryCTE AS BC 
    ON BC.ProductCategoryID = C.ParentProductCategoryID
)
SELECT PC.[Name] AS [ParentProductCategoryName]
  , CCTE.[Name] as [ProductCategoryName]
  , CCTE.[ProductCategoryID]  
FROM CategoryCTE AS CCTE
JOIN SalesLT.ProductCategory AS PC 
ON PC.[ProductCategoryID] = CCTE.[ParentProductCategoryID]

视图属性和选项

• ENCRYPTION属性：对视图、存储过程、触发器，用户定义函数(UDF)的定义进行加密，从而用sp_helptext和OBJECT_DEFINITION无法获取元数据
• SCHEMABIDING属性：指定改属性后，视图所引用的对象无法被删除，被引用的列也不可删除或修改
• CHECK OPTION选项：检查以避免通过视图进行的数据修改与当前视图中设置的过滤条件相冲突。

ALTER VIEW [SalesLT].[vEnProduct] 
WITH SCHEMABINDING, encryption  -- view attributes
AS 
SELECT p.[ProductID] 
  ,p.[Name]     
  ,pmx.[Culture] 
  ,pd.[Description] 
FROM [SalesLT].[Product] p     
  INNER JOIN [SalesLT].[ProductModelProductDescription] pmx 
    ON p.[ProductModelID] = pmx.[ProductModelID] 
  INNER JOIN [SalesLT].[ProductDescription] pd 
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
WHERE pmx.Culture = 'en' -- this view only contains products for 'en'
WITH CHECK OPTION;
GO

-- Error sample: conflict with CHECK OPTION

-- Can't be changed to other culture other than 'en'
update P
set P.Culture = 'zh' 
from SalesLT.vEnProduct as P

-- You can't insert a record without 'en' Culture tagged

内联表值函数（参数化视图）
除了支持输入参数以外，其他方面与视图类似，下边是一个例子：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnGetCustomerInformation]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnGetCustomerInformation]
GO
CREATE FUNCTION [dbo].[ufnGetCustomerInformation]
  (@CustomerID int) RETURNS TABLE 
AS 
RETURN (
  SELECT 
    CustomerID, 
    FirstName, 
    LastName
  FROM [SalesLT].[Customer] 
  WHERE [CustomerID] = @CustomerID

APPLY运算符(APPLY opeator)

• Sql2005引入的非标准运算符
• 在FROM子句中使用
• 包括CROSS APPLY和OUTER APPLY两种形式，概念上相似于INNER JOIN和OUTER JOIN。

基本原理如下：

• APPLY运算符以两个输入表为左右参数，其中右表(第二个表)，可以是表表达式，通常是派生表或内联表值函数。
• CROSS APPLY：把右表应用到左表的每一行，再把结果集组合起来，输出统一的表结果。如果右表为空，则对应的左表行不会输出。
• OUTER APPLY：比CROSS APPLY多了一个逻辑处理步骤，标示出让右表为空的左表数据行，输出该行是，右表的列将置为NULL。
• 出于封装的目的，推荐使用内联表值函数取代派生表作为右表

-- Get the first 3 orders of each customer
select C.CustomerID
  , A.SalesOrderID
  , A.OrderDate
from SalesLT.Customer as C
  outer apply (
    select top(3) SalesOrderID, OrderDate  
    from SalesLT.SalesOrderHeader as O
    where O.CustomerID = C.CustomerID
    order by OrderDate desc, SalesOrderID desc
  ) as A
order by C.CustomerID