SQL Server 2005中的T-SQL增强

视图、派生表和CTE内部的查询的一般形式

1、视图
CREATE VIEW <view_name>(<column_aliases>) AS <view_query>

2、派生表
SELECT * FROM (<derived_table)query>) AS <dericed_table_alias>(<column_aliases>)

3、CTE
WITH <cte_alias>(<column_aliases>)
AS
{
 <cte_query>
)
SELECT * FROM <cte_alias]>
在关键字WITH之后，为CTE提供一个别名，并且为它的结果列提供一个可选的别名列表；编写CTE的主体；然后从外部查询中引用它。

4、演示代码
USE AdventureWorks
GO
WITH SalesCTE(ProductID, SalesOrderID)
AS
(
 SELECT ProductID, COUNT(SalesOrderID)
 FROM Sales.SalesOrderDetail
 GROUP BY ProductID
)
SELECT * FROM SalesCTE

Recursive CTEs 递归的通用表表达式

递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。

--使用递归的通用表表达式
USE demo
GO
CREATE TABLE CarParts
(
 CarID INT NOT NULL,
 Part VARCHAR(15),
 SubPart VARCHAR(15),
 Qty INT
)
GO
INSERT CarParts VALUES (1, ''''Body'''', ''''Door'''', 4)
INSERT CarParts VALUES (1, ''''Body'''', ''''Trunk Lid'''', 1)
INSERT CarParts VALUES (1, ''''Body'''', ''''Car Hood'''', 1)
INSERT CarParts VALUES (1, ''''Door'''', ''''Handle'''', 1)
INSERT CarParts VALUES (1, ''''Door'''', ''''Lock'''', 1)
INSERT CarParts VALUES (1, ''''Door'''', ''''Window'''', 1)
INSERT CarParts VALUES (1, ''''Body'''', ''''Rivets'''', 1000)
INSERT CarParts VALUES (1, ''''Door'''', ''''Rivets'''', 100)
INSERT CarParts VALUES (1, ''''Door'''', ''''Mirror'''', 1)
GO
SELECT * FROM CarParts
GO

WITH CarPartsCTE(SubPart, Qty)
AS
(
 -- 固定成员 (AM):
 -- SELECT查询无需参考CarPartsCTE
 SELECT SubPart, Qty
 FROM CarParts
 WHERE Part = ''''Body''''
 UNION ALL
 -- 递归成员 (RM):
 -- SELECT查询参考CarPartsCTE
 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
 FROM CarPartsCTE
 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
 WHERE CarParts.CarID = 1
)
-- 外部查询
SELECT SubPart, SUM(Qty) AS TotalNUM
FROM CarPartsCTE
GROUP BY SubPart  

新的关系运算符 PIVOT/UNPIVOT/APPLY

1、PIVOT
PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。

2、UNPIVOT
UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。

3、APPLY
APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式：CROSS APPLY和OUTER APPLY。

演示：

USE demo
GO

CREATE TABLE orders
(
 Customer VARCHAR(10) NOT NULL,
 product VARCHAR(20) NOT NULL,
 quantity INT NOT NULL
)
GO
INSERT orders VALUES(''''Mike'''', ''''Bike'''',3)
INSERT orders VALUES(''''Mike'''',''''Chain'''',2)
INSERT orders VALUES(''''Mike'''',''''Bike'''',5)
INSERT orders VALUES(''''Lisa'''',''''Bike'''',3)
INSERT orders VALUES(''''Lisa'''',''''Chain'''',3)
INSERT orders VALUES(''''Lisa'''',''''Chain'''',4)
INSERT orders VALUES(''''Lisa'''',''''Bike'''',2)

SELECT * FROM orders

SELECT * FROM orders
PIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a
USE demo
GO
CREATE TABLE SALES1
(
  [Year] INT,
  Quarter CHAR(2),
  Amount FLOAT
)
GO
INSERT INTO SALES1 VALUES (2001, ''''Q1'''', 80)
INSERT INTO SALES1 VALUES (2001, ''''Q2'''', 70)
INSERT INTO SALES1 VALUES (2001, ''''Q3'''', 55)
INSERT INTO SALES1 VALUES (2001, ''''Q3'''', 110)
INSERT INTO SALES1 VALUES (2001, ''''Q4'''', 90)
INSERT INTO SALES1 VALUES (2002, ''''Q1'''', 200)
INSERT INTO SALES1 VALUES (2002, ''''Q2'''', 150)
INSERT INTO SALES1 VALUES (2002, ''''Q2'''', 40)
INSERT INTO SALES1 VALUES (2002, ''''Q2'''', 60)
INSERT INTO SALES1 VALUES (2002, ''''Q3'''', 120)
INSERT INTO SALES1 VALUES (2002, ''''Q3'''', 110)
INSERT INTO SALES1 VALUES (2002, ''''Q4'''', 180)
GO

SELECT * FROM SALES1
PIVOT
(SUM (Amount) --使用SUM聚合数量列
FOR [Quarter] --PIVOT Quarter 列
IN (Q1, Q2, Q3, Q4)) --使用季节
AS P
GO

SELECT * INTO temp1 FROM orders
PIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a

SELECT * FROM temp1

SELECT customer, product,quantity
FROM temp1
UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a
----------------------------------------------------
USE demo
GO
CREATE TABLE Arrays
(
  aid INT NOT NULL IDENTITY PRIMARY KEY,
  array VARCHAR(7999) NOT NULL
)
GO
INSERT INTO Arrays VALUES('''''''')
INSERT INTO Arrays VALUES(''''10'''')
INSERT INTO Arrays VALUES(''''20,40,30'''')
INSERT INTO Arrays VALUES(''''-1,-3,-5'''')
GO
CREATE FUNCTION  function1(@arr AS VARCHAR(7999))
  RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
  DECLARE @end AS INT, @start AS INT, @pos AS INT
  SELECT @arr = @arr + '''','''', @pos = 1,
    @start = 1, @end = CHARINDEX('''','''', @arr, @start)
  WHILE @end > 1
  BEGIN
    INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))

    SELECT @pos = @pos + 1,
      @start = @end + 1, @end = CHARINDEX('''','''', @arr, @start)
  END
  RETURN
END

--测试
SELECT * FROM function1(''''200,400,300'''')
GO

SELECT A.aid, F.*
FROM Arrays AS A
  CROSS APPLY function1(array) AS F
GO
SELECT A.aid, F.*
FROM Arrays AS A
  OUTER APPLY function1(array) AS F
GO

DDL触发器 DDL Triggers

SQL Server 2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。也可以为单个DDL语句（例如：CREAT_TABLE、DROP_TABLE等）或者为一组语句（例如：指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库所有DDL事件）定义DDL触发器。

在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。

DDL触发器特别有用的方案包括DDL更改的完整性检查、审核方案以及其他方案。

代码演示：

USE demo
GO
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
AS
RAISERROR(''''没有删除表的权限.'''', 10, 1)
PRINT ''''尝试在数据库'''' + DB_NAME() + ''''中删除表.''''
PRINT CONVERT (nvarchar (1000),EventData())
ROLLBACK
GO
-- 测试
CREATE TABLE TestDROP(col1 INT)
GO
INSERT INTO TestDROP VALUES(1)

DROP TABLE testdrop

-- Server
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
  FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
PRINT ''''发生DDL LOGIN.''''
PRINT CONVERT (nvarchar (1000),EventData())
GO

-- 测试
CREATE LOGIN login1 WITH PASSWORD = ''''123''''
ALTER LOGIN login1 WITH PASSWORD = ''''xyz''''
DROP LOGIN login1

总结

SQL Server 2005中的Transaction-SQL增强功能提高了用户在编写查询时的表达能力，使用户可以改善代码的性能，并且扩充了错误处理能力。

SQL Server 2005 在Transaction-SQL上所做的改进反映了其更好地满足了ANSI-99 SQL规范的要求以及客户的需求。

在Transaction-SQL和托管代码之间的选择。

上一页  [1] [2] 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18