豐富的數(shù)據(jù)類型 Richer Data Types

1、varchar(max)、nvarchar(max)和varbinary(max)數(shù)據(jù)類型最多可以保存2GB的數(shù)據(jù)，可以取代text、ntext或image數(shù)據(jù)類型。CREATE TABLE myTable( id INT, content VARCHAR(MAX))

2、XML數(shù)據(jù)類型XML數(shù)據(jù)類型允許用戶在SQL Server數(shù)據(jù)庫(kù)中保存XML片段或文檔。

錯(cuò)誤處理 Error Handling

1、新的異常處理結(jié)構(gòu)

2、可以捕獲和處理過(guò)去會(huì)導(dǎo)致批處理終止的錯(cuò)誤前提是這些錯(cuò)誤不會(huì)導(dǎo)致連接中斷（通常是嚴(yán)重程度為21以上的錯(cuò)誤，例如，表或數(shù)據(jù)庫(kù)完整性可疑、硬件錯(cuò)誤等等。）。

3、TRY/CATCH 構(gòu)造SET XACT_ABORT ON BEGIN TRY <core logic> END TRY BEGIN CATCH TRAN_ABORT <exception handling logic> END TRY

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

4、演示代碼USE demo GO --創(chuàng)建工作表

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')

--調(diào)用一個(gè)運(yùn)行時(shí)錯(cuò)誤SET XACT_ABORT OFF BEGIN TRAN INSERT INTO score VALUES (101,90) INSERT INTO score VALUES (102,78) INSERT INTO score VALUES (107, 76) /* 外鍵錯(cuò)誤 */ INSERT INTO score VALUES (103,81) INSERT INTO score VALUES (104,65) COMMIT TRAN GO

SELECT * FROM student SELECT * FROM score

--使用TRY...CATCH構(gòu)造，并調(diào)用一個(gè)運(yùn)行時(shí)錯(cuò)誤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) /* 外鍵錯(cuò)誤 */ INSERT INTO score VALUES (103,81) INSERT INTO score VALUES (104,65) COMMIT TRAN PRINT '事務(wù)提交' END TRY BEGIN CATCH ROLLBACK PRINT '事務(wù)回滾' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_MESSAGE() as ErrorMessage; END CATCHGO

SELECT * FROM scoreGO

快照隔離 Snapshot Isolation

1、寫入程序不會(huì)阻礙讀取程序2、Snapshot isolation must be enabled for DB ALTER DATABASE 數(shù)據(jù)庫(kù) SET allow_snapshot_isolation ON3、Snapshot isolation must be enabled for connection Set transaction isolation level snapshot4、UPDATE transactions keep old versions of data in a linked list5、新的隔離級(jí)別提供了以下優(yōu)點(diǎn)：　 1) 提高了只讀應(yīng)用程序的數(shù)據(jù)可用性　 2) 允許在OLTP環(huán)境中執(zhí)行非阻止讀取操作　 3) 可對(duì)寫入事務(wù)進(jìn)行自動(dòng)的強(qiáng)制沖突檢測(cè)6、演示代碼CREATE DATABASE demo2GOUSE 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 demo2BEGIN TRAN UPDATE test SET tname='version3' WHERE tid=2 SELECT * FROM test

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

TOP 增強(qiáng)功能

1、TOP 增強(qiáng)可以指定一個(gè)數(shù)字表達(dá)式，以返回要通過(guò)查詢影響的行數(shù)或百分比，還可以根據(jù)情況使用變量或子查詢。可以在DELETE、UPDATE和INSERT查詢中使用TOP選項(xiàng)。

2、更好地替換SET ROWCOUNT選項(xiàng)，使之更為有效。

OUTPUT

1、SQL Server 2005引入一個(gè)新的OUTPUT子句，以使您可以沖修改語(yǔ)句(INSERT、UPDATE、DELETE)中將數(shù)據(jù)返回到表變量中。

2、新的OUTPUT子局的語(yǔ)法為：OUTPUT <dml_select_list> INTO @table_variable可以通過(guò)引用插入的表或刪除的表來(lái)訪問(wèn)被修改的行的舊/新影象，其方式與訪問(wèn)觸發(fā)器類似。在INSERT語(yǔ)句中，只能訪問(wèn)插入的表。在DELETE語(yǔ)句中，只能訪問(wèn)刪除的表。在UPDATE語(yǔ)句中，可以訪問(wèn)插入的表和刪除的表。

3、代碼演示USE demoGOCREATE 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 ttOUTPUT DELETED.id, DELETED.c1 INTO @delWHERE id < 3SELECT * FROM @delGO-----------------------------------------------USE demoGOCREATE TABLE toptest (column1 VARCHAR(150))GOINSERT 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 toptestGO

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

--聲明3個(gè)變量DECLARE @a INTDECLARE @b INTDECLARE @c INT

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

--使用計(jì)算表達(dá)式SELECT TOP(@c) * FROM toptest

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

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

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

SELECT * FROM toptest

排序函數(shù) Ranking Functions

1、SQL Server引入幾個(gè)新的排序函數(shù)：如ROW_NUMBER、RANK、DENSE_RANK等。這些新函數(shù)使您可以有效地分析數(shù)據(jù)以及向查詢的結(jié)果行提供排序值。

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

3、ROW_NUMBERROW_NUMBER是結(jié)果集的順序, 而不是數(shù)據(jù)庫(kù)中紀(jì)錄存放的原始順序USE demoGOCREATE TABLE rankorder(orderid INT,qty INT)GOINSERT 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)GOSELECT 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 rankorderORDER BY qty

通用表表達(dá)式 Common Table Expressions;

通用表表達(dá)式（CTE）是一個(gè)可以由定義語(yǔ)句引用的臨時(shí)表命名的結(jié)果集。在他們的簡(jiǎn)單形式中，您可以將CTE視為類似于視圖和派生表混合功能的改進(jìn)版本。在查詢的FROM子句中引用CTE的方式類似于引用派生表和視圖的方式。只須定義CTE一次，即可在查詢中多次引用它。在CTE的定義中，可以引用在同一批處理中定義的變量。但是CTE的真正威力在于它們的遞歸功能，即CTE可以包含對(duì)它們自身的引用。

視圖、派生表和CTE內(nèi)部的查詢的一般形式

1、視圖CREATE VIEW <view_name>(<column_aliases>) AS <view_query>

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

3、CTEWITH <cte_alias>(<column_aliases>)AS{<cte_query>)SELECT * FROM <cte_alias]>在關(guān)鍵字WITH之后，為CTE提供一個(gè)別名，并且為它的結(jié)果列提供一個(gè)可選的別名列表；編寫CTE的主體；然后從外部查詢中引用它。

4、演示代碼USE AdventureWorksGOWITH SalesCTE(ProductID, SalesOrderID)AS (SELECT ProductID, COUNT(SalesOrderID) FROM Sales.SalesOrderDetail GROUP BY ProductID)SELECT * FROM SalesCTE

Recursive CTEs 遞歸的通用表表達(dá)式

遞歸的CTE是根據(jù)至少兩個(gè)查詢（或者稱為兩個(gè)成員）構(gòu)建的，一個(gè)是非遞歸查詢，也成為固定成員，只能調(diào)用一次，另外一個(gè)是遞歸查詢，也成為遞歸成員（RM），可以反復(fù)調(diào)用，直到查詢不再返回行。查詢由UNION ALL運(yùn)算符連接為一個(gè)單獨(dú)的CTE。

--使用遞歸的通用表表達(dá)式USE demoGOCREATE TABLE CarParts(CarID INT NOT NULL,Part VARCHAR(15),SubPart VARCHAR(15),Qty INT)GOINSERT 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)GOSELECT * FROM CarPartsGO

WITH CarPartsCTE(SubPart, Qty) AS(-- 固定成員 (AM):-- SELECT查詢無(wú)需參考CarPartsCTESELECT SubPart, QtyFROM CarPartsWHERE Part = 'Body'UNION ALL-- 遞歸成員 (RM):-- SELECT查詢參考CarPartsCTESELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.QtyFROM CarPartsCTE INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.PartWHERE CarParts.CarID = 1)-- 外部查詢SELECT SubPart, SUM(Qty) AS TotalNUMFROM CarPartsCTEGROUP BY SubPart

新的關(guān)系運(yùn)算符 PIVOT/UNPIVOT/APPLY

1、PIVOTPIVOT運(yùn)算符將行旋轉(zhuǎn)為列，并且可能同時(shí)執(zhí)行聚合。使用PIVOT運(yùn)算符時(shí)要注意的重要一點(diǎn)是，需要為它提供一個(gè)查詢表達(dá)式，表達(dá)式使用視圖、派生表或者是CTE只返回所關(guān)注的列。

2、UNPIVOTUNPIVOT運(yùn)算符執(zhí)行與PIVOT運(yùn)算符相反的操作；他將列旋轉(zhuǎn)為行了。

3、APPLYAPPLY關(guān)系運(yùn)算符允許您對(duì)外部表的每個(gè)行調(diào)用指定的表值函數(shù)一次。您可以在查詢的FROM子句中指定APPLY，其方式與使用JOIN關(guān)系運(yùn)算符類似。APPLY具有兩種形式：CROSS APPLY和OUTER APPLY。

演示：

USE demoGO

CREATE TABLE orders(Customer VARCHAR(10) NOT NULL,product VARCHAR(20) NOT NULL,quantity INT NOT NULL)GOINSERT 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 ordersPIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS aUSE demoGOCREATE TABLE SALES1( [Year] INT, Quarter CHAR(2), Amount FLOAT)GOINSERT 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 SALES1PIVOT(SUM (Amount) --使用SUM聚合數(shù)量列FOR [Quarter] --PIVOT Quarter 列IN (Q1, Q2, Q3, Q4)) --使用季節(jié)AS PGO

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

SELECT * FROM temp1

SELECT customer, product,quantityFROM temp1UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a----------------------------------------------------USE demoGOCREATE TABLE Arrays( aid INT NOT NULL IDENTITY PRIMARY KEY, array VARCHAR(7999) NOT NULL)GOINSERT INTO Arrays VALUES('')INSERT INTO Arrays VALUES('10')INSERT INTO Arrays VALUES('20,40,30')INSERT INTO Arrays VALUES('-1,-3,-5')GOCREATE FUNCTION; function1(@arr AS VARCHAR(7999)) RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)ASBEGIN 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 RETURNEND

--測(cè)試SELECT * FROM function1('200,400,300')GO

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

DDL觸發(fā)器 DDL Triggers

SQL Server 2005可以就整個(gè)服務(wù)器或數(shù)據(jù)庫(kù)的某個(gè)范圍為DDL事件定義觸發(fā)器。也可以為單個(gè)DDL語(yǔ)句（例如：CREAT_TABLE、DROP_TABLE等）或者為一組語(yǔ)句（例如：指定DDL_DATABASE_LEVEL_EVENTS想要觸發(fā)器觸發(fā)數(shù)據(jù)庫(kù)所有DDL事件）定義DDL觸發(fā)器。

在DDL觸發(fā)器內(nèi)部，可以通過(guò)訪問(wèn)eventdata()函數(shù)獲得與激發(fā)該觸發(fā)器的事件有關(guān)的數(shù)據(jù)。該eventdata()函數(shù)返回有關(guān)事件的xml數(shù)據(jù)。

DDL觸發(fā)器特別有用的方案包括DDL更改的完整性檢查、審核方案以及其他方案。

代碼演示：

USE demoGOCREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLEASRAISERROR('沒(méi)有刪除表的權(quán)限.', 10, 1)PRINT '嘗試在數(shù)據(jù)庫(kù)' + DB_NAME() + '中刪除表.'PRINT CONVERT (nvarchar (1000),EventData())ROLLBACKGO-- 測(cè)試CREATE TABLE TestDROP(col1 INT)GOINSERT INTO TestDROP VALUES(1)

DROP TABLE testdrop

-- ServerCREATE TRIGGER audit_ddl_logins ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGINASPRINT '發(fā)生DDL LOGIN.'PRINT CONVERT (nvarchar (1000),EventData())GO

-- 測(cè)試CREATE LOGIN login1 WITH PASSWORD = '123'ALTER LOGIN login1 WITH PASSWORD = 'xyz'DROP LOGIN login1

總結(jié)

SQL Server 2005中的Transaction-SQL增強(qiáng)功能提高了用戶在編寫查詢時(shí)的表達(dá)能力，使用戶可以改善代碼的性能，并且擴(kuò)充了錯(cuò)誤處理能力。

SQL Server 2005 在Transaction-SQL上所做的改進(jìn)反映了其更好地滿足了ANSI-99 SQL規(guī)范的要求以及客戶的需求。

在Transaction-SQL和托管代碼之間的選擇。