Sql Server 2005新增T-sql特性

Sql Server 2005新增T-sql特性

[@more@]

豐富的資料型別 Richer Data Types

1、varchar(max)、nvarchar(max)和varbinary(max)資料型別最多可以儲存2GB的資料，可以取代text、ntext或image資料型別。
CREATE TABLE myTable
(
id INT,
content VARCHAR(MAX)
)

2、XML資料型別
XML資料型別允許使用者在SQL Server資料庫中儲存XML片段或文件。

錯誤處理 Error Handling

1、新的異常處理結構

2、可以捕獲和處理過去會導致批處理終止的錯誤前提是這些錯誤不會導致連線中斷（通常是嚴重程度為21以上的錯誤，例如，表或資料庫完整性可疑、硬體錯誤等等。）。

3、TRY/CATCH 構造
SET XACT_ABORT ON
BEGIN TRY

END TRY
BEGIN CATCH TRAN_ABORT

END TRY

@@error may be quired as first statement in CATCH block

4、演示程式碼
USE demo
GO
--建立工作表

CREATE TABLE student
(
stuid INT NOT NULL PRIMARY KEY,
stuname VARCHAR(50)
)

CREATE TABLE score
(
stuid INT NOT NULL REFERENCES student(stuid),
score INT
)
GO

INSERT INTO student VALUES (101,'zhangsan')
INSERT INTO student VALUES (102,'wangwu')
INSERT INTO student VALUES (103,'lishi')
INSERT INTO student VALUES (104,'maliu')

--呼叫一個執行時錯誤
SET XACT_ABORT OFF
BEGIN TRAN
INSERT INTO score VALUES (101,90)
INSERT INTO score VALUES (102,78)
INSERT INTO score VALUES (107, 76) /* 外來鍵錯誤 */
INSERT INTO score VALUES (103,81)
INSERT INTO score VALUES (104,65)
COMMIT TRAN
GO

SELECT * FROM student
SELECT * FROM score

--使用TRY...CATCH構造，並呼叫一個執行時錯誤
SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRAN
INSERT INTO score VALUES (101,90)
INSERT INTO score VALUES (102,78)
INSERT INTO score VALUES (107, 76) /* 外來鍵錯誤 */
INSERT INTO score VALUES (103,81)
INSERT INTO score VALUES (104,65)
COMMIT TRAN
PRINT '事務提交'
END TRY
BEGIN CATCH
ROLLBACK
PRINT '事務回滾'
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
GO

SELECT * FROM score
GO

快照隔離 Snapshot Isolation

1、寫入程式不會阻礙讀取程式
2、Snapshot isolation must be enabled for DB
ALTER DATABASE 資料庫 SET allow_snapshot_isolation ON
3、Snapshot isolation must be enabled for connection
Set transaction isolation level snapshot
4、UPDATE transactions keep old versions of data in a linked list
5、新的隔離級別提供了以下優點：　 1) 提高了只讀應用程式的資料可用性　 2) 允許在OLTP環境中執行非阻止讀取操作　 3) 可對寫入事務進行自動的強制衝突檢測
6、演示程式碼
CREATE DATABASE demo2
GO
USE demo2
ALTER DATABASE demo2 SET allow_snapshot_isolation ON
CREATE TABLE test
(
tid INT NOT NULL primary key,
tname VARCHAR(50) NOT NULL
)
INSERT INTO test VALUES(1,'version1')
INSERT INTO test VALUES(2,'version2')

--連線一

USE demo2
BEGIN TRAN
UPDATE test SET tname='version3' WHERE tid=2
SELECT * FROM test

--連線二
USE demo2
SET transaction isolation level snapshot
SELECT * FROM test

TOP 增強功能

1、TOP 增強可以指定一個數字表示式，以返回要透過查詢影響的行數或百分比，還可以根據情況使用變數或子查詢。可以在DELETE、UPDATE和INSERT查詢中使用TOP選項。

2、更好地替換SET ROWCOUNT選項，使之更為有效。

OUTPUT

1、SQL Server 2005引入一個新的OUTPUT子句，以使您可以衝修改語句(INSERT、UPDATE、DELETE)中將資料返回到表變數中。

2、新的OUTPUT子局的語法為：
OUTPUT INTO @table_variable可以透過引用插入的表或刪除的表來訪問被修改的行的舊/新影像，其方式與訪問觸發器類似。在INSERT語句中，只能訪問插入的表。在DELETE語句中，只能訪問刪除的表。在UPDATE語句中，可以訪問插入的表和刪除的表。

3、程式碼演示
USE demo
GO
CREATE TABLE tt
(
id INT IDENTITY,
c1 VARCHAR(15)
)
GO

INSERT INTO tt VALUES ('r1')
INSERT INTO tt VALUES ('r2')
INSERT INTO tt VALUES ('r5')
INSERT INTO tt VALUES ('r6')
INSERT INTO tt VALUES ('r7')
INSERT INTO tt VALUES ('r8')
INSERT INTO tt VALUES ('r9')
INSERT INTO tt VALUES ('r10')

DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
DELETE tt
OUTPUT DELETED.id, DELETED.c1 INTO @del
WHERE id < 3
SELECT * FROM @del
GO
-----------------------------------------------
USE demo
GO
CREATE TABLE toptest (column1 VARCHAR(150))
GO
INSERT INTO toptest VALUES('t1')
INSERT INTO toptest VALUES('t2')
INSERT INTO toptest VALUES('t3')
INSERT INTO toptest VALUES('t4')
INSERT INTO toptest VALUES('t5')
INSERT INTO toptest VALUES('t6')
INSERT INTO toptest VALUES('t7')
INSERT INTO toptest VALUES('t8')
SELECT * FROM toptest
GO

CREATE TABLE toptest2 (column2 VARCHAR(150))
GO
INSERT INTO toptest2 VALUES('c1')
INSERT INTO toptest2 VALUES('c2')

--宣告3個變數
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT

--賦值
SET @a = 10
SET @b = 5
SELECT @c = @a/@b

--使用計算表示式
SELECT TOP(@c) * FROM toptest

--使用SELECT語句作為條件
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
FROM toptest

--指出top
DELETE TOP(2) toptest where column1>'t6'

--更新top
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'

SELECT * FROM toptest

排序函式 Ranking Functions

1、SQL Server引入幾個新的排序函式：如ROW_NUMBER、RANK、DENSE_RANK等。這些新函式使您可以有效地分析資料以及向查詢的結果行提供排序值。

2、排序函式都遵循類似的語法模式：
()OVER
([PARTITION BY]
ORDER BY)該函式只能在查詢的兩個子句中指定 - 在SELECT子句或ORDER BY子句中。以下詳細討論不同的函式。

3、ROW_NUMBER
ROW_NUMBER是結果集的順序, 而不是資料庫中紀錄存放的原始順序
USE demo
GO
CREATE TABLE rankorder
(
orderid INT,
qty INT
)
GO
INSERT rankorder VALUES(30001,10)
INSERT rankorder VALUES(10001,10)
INSERT rankorder VALUES(10006,10)
INSERT rankorder VALUES(40005,10)
INSERT rankorder VALUES(30003,15)
INSERT rankorder VALUES(30004,20)
INSERT rankorder VALUES(20002,20)
INSERT rankorder VALUES(20001,20)
INSERT rankorder VALUES(10005,30)
INSERT rankorder VALUES(30007,30)
INSERT rankorder VALUES(40001,40)
GO
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS rank,
DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty

通用表表示式 Common Table Expressions

通用表表示式（CTE）是一個可以由定義語句引用的臨時表命名的結果集。在他們的簡單形式中，您可以將CTE視為類似於檢視和派生表混合功能的改進版本。在查詢的FROM子句中引用CTE的方式類似於引用派生表和檢視的方式。只須定義CTE一次，即可在查詢中多次引用它。在CTE的定義中，可以引用 在同一批處理中定義的變數。但是CTE的真正威力在於它們的遞迴功能，即CTE可以包含對它們自身的引用。

檢視、派生表和CTE內部的查詢的一般形式

1、檢視
CREATE VIEW () AS

2、派生表
SELECT * FROM () AS ()

3、CTE
WITH ()
AS
{

)
SELECT * FROM 在關鍵字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和託管程式碼之間的選擇。