功能强大的全新 T-SQL 语法极大地提升了 SQL Server 的可编程性

SELECT B.ProductID, P.Name, SUM(B.Qty) AS TotalQty FROM BOMCTE AS B JOIN Product AS P ON P.ProductID = B.ProductID GROUP BY B.ProductID, P.Name ORDER BY B.ProductID;

如果您怀疑其中存在循环，想要限制递归调用的数量，可以在外部查询之后马上指定 MAXRECURSION 选项：www.iTbulo.com-ZO8xOW1

WITH... outer_query OPTION(MAXRECURSION 30)

此选项将在 CTE 超过指定限制的时候，使 SQL Server 引发一个错误。如果没有指定这个选项，SQL Server 中的默认值是 100。如果不想有限制的话，必须指定 0。请注意您可以编写自定义代码检测循环关系，但是这超出了本文的范围。www.iTbulo.com-ZO8xOW1

返回页首 PIVOT 运算符

SQL Server Yukon 中新的 PIVOT 运算符允许您编写交叉表查询将行转为列。UNPIVOT 运算符则刚好相反 — 处理已旋转数据，将列转为行。图 11 给出了想要返回每个销售人员的年总销售定单值，且每年的值在不同列中显示时在数据库中使用 PIVOT 运算符的结果。www.iTbulo.com-ZO8xOW1

当使用 PIVOT 运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者 CTE 只返回所关注的列。原因在于，PIVOT 在幕后实际是对运算符没有显式引用的所有列进行一个隐式的 GROUP BY 操作。在这里，需要的是销售人员 ID、定单年份和定单值：www.iTbulo.com-ZO8xOW1

USE AdventureWorks SELECT SOH.SalesPersonID, YEAR(SOH.OrderDate) AS OrderYear, SOD.OrderQty * SOD.UnitPrice AS OrderValue FROM SalesOrderHeader AS SOH JOIN SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderID

SQL Server 会明白“GROUP BY”列的列表应该是输入表中没有被聚合函数或者 IN 子句里 PIVOT 运算符显式引用的列列表。因此如果您不想获取隐式 GROUP BY 列列表中不需要的列，需要为聚合函数、IN 子句和隐式 GROUP BY 给 PIVOT 运算符提供一个只包含所关注列的输入表。这可以通过使用一个 CTE 或者一个派生表（包含只返回所关注列的以前查询）实现。www.iTbulo.com-ZO8xOW1

图 12 中的代码说明了如何在 CTE 内使用这个查询，并让外部查询对 CTE 的结果发出一个 PIVOT 操作。SUM(OrderValue) 告诉 PIVOT 要填充已旋转列的单元格应该计算哪个聚合。FOR 子句告诉 PIVOT 哪个源列包含了旋转为结果列的值。IN 子句包含着要显示为结果列名称的值列表。www.iTbulo.com-ZO8xOW1

SQL Server 要求显式地在 IN 子句中指定要旋转为结果列的值列表。不能在使用静态查询的同时让 SQL Server 找出 OrderYear 中的所有不同值。为了达到这一目的，必须使用动态执行动态地构造查询字符串，如图 13 中的代码所示。www.iTbulo.com-ZO8xOW1

为了看到 UNPIVOT 运算符的作用，首先创建 SalesPivoted 表，这通过运行图 12 中的查询，在 FROM 子句之前加上“SELECT INTO SalesPivoted”实现（参见图 14）。UNPIVOT 运算符的参数与 PIVOT 的参数非常类似。但是这时需要指定结果列的名称，结果列将在一列中包含所有已旋转的单元格的值。在 FOR 子句之后，指定结果列的名称，该结果列存储已旋转列的名称作为列值。在 IN 子句后的括号中，指定想要取消旋转的已旋转列的列表：www.iTbulo.com-ZO8xOW1

SELECT * FROM SalesPivoted UNPIVOT(OrderValue FOR OrderYear IN([2001], [2002], [2003], [2004])) AS U

UNPIVOT 并不为包含 NULL 值的单元格返回行。为了清除数据库中我建立的多余的表和索引，运行以下代码：www.iTbulo.com-ZO8xOW1

DROP INDEX SalesOrderHeader.idx_nc_OrderDate DROP TABLE SalesPivoted 返回页首 触发器和通知

SQL Server Yukon Beta 1 引入了对数据定义语言 (DDL) 触发器的支持，允许您捕获 DDL 操作并对其做出反应，可选地回滚操作。多个 DDL 触发器是同步工作的，紧跟在触发器事件之后，与以前版本的 SQL Server 中触发器工作方式类似。SQL Server 还支持一种可以使用通知的异步事件使用机制，允许您订阅以在某些事件发生的时候获得通知。www.iTbulo.com-ZO8xOW1

以下触发器是在数据库一级创建的，可以捕获 DROP TABLE 的尝试：www.iTbulo.com-ZO8xOW1

CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE AS RAISERROR('Not allowed to drop tables.', 10, 1) ROLLBACK -- For debug PRINT 'DROP TABLE attempt in database ' + DB_NAME() + '.' PRINT EventData() GO

您可以定义触发器来触发特定的 DDL 事件，如 CREATE_TABLE、DROP_TABLE、ALTER_TABLE、CREATE_VIEW，等等，或者如果想要触发器触发数据库中所有 DDL 事件，您也可以指定 DDL_DATABASE_LEVEL_EVENTS。在触发器内，可以调用 EventData 函数返回有关触发了触发器的进程和操作的信息。可以对函数返回的 XML 进行研究，并相应地做出反应。www.iTbulo.com-ZO8xOW1

为了测试触发器，首先创建表 TestDrop 并通过运行以下代码在其中插入一行：www.iTbulo.com-ZO8xOW1

CREATE TABLE TestDROP(col1 INT) INSERT INTO TestDROP VALUES(1)

接下来，尝试除去表：www.iTbulo.com-ZO8xOW1

DROP TABLE TestDROP

DROP 尝试被捕获了，并输出了一条消息，指示不允许除去表。此外，EventData 函数的返回值用 XML 格式输出，以用于调试目的。（实际上，在触发器内您可以查看 XML 数据，它包含了许多有用的信息，可以从中确定什么样的操作最符合您的需要。例如，您可以防止在一天的特定时间里除去某些表。）触发器回滚操作，这样表就不会从数据库中除去。要除去触发器，需要发出以下代码语句：www.iTbulo.com-ZO8xOW1

上一页 1 2 3 4 56 下一页

上一篇：SQL Server 2005 数据转换服务的常见设计问题   下一篇：托管 UDT 使您能够扩展 SQL Server 的类型系统

用户名(必填) 新注册

密码(必填)匿名评论