SQL Server 2005中的T-SQL增强

  1<core logic="">   
  2END TRY   
  3BEGIN CATCH TRAN_ABORT   
  4<exception handling="" logic="">   
  5END TRY 
  6
  7@@error may be quired as first statement in CATCH block 
  8
  94、演示代码   
 10USE demo   
 11GO   
 12\--创建工作表 
 13
 14CREATE TABLE student   
 15(   
 16stuid INT NOT NULL PRIMARY KEY,   
 17stuname VARCHAR(50)   
 18)   
 19  
 20CREATE TABLE score   
 21(   
 22stuid INT NOT NULL REFERENCES student(stuid),   
 23score INT   
 24)   
 25GO 
 26
 27INSERT INTO student VALUES (101,'zhangsan')   
 28INSERT INTO student VALUES (102,'wangwu')   
 29INSERT INTO student VALUES (103,'lishi')   
 30INSERT INTO student VALUES (104,'maliu') 
 31
 32\--调用一个运行时错误   
 33SET XACT_ABORT OFF   
 34BEGIN TRAN   
 35INSERT INTO score VALUES (101,90)   
 36INSERT INTO score VALUES (102,78)   
 37INSERT INTO score VALUES (107, 76) /* 外键错误 */   
 38INSERT INTO score VALUES (103,81)   
 39INSERT INTO score VALUES (104,65)   
 40COMMIT TRAN   
 41GO   
 42  
 43SELECT * FROM student   
 44SELECT * FROM score 
 45
 46\--使用TRY...CATCH构造，并调用一个运行时错误   
 47SET XACT_ABORT OFF   
 48BEGIN TRY   
 49BEGIN TRAN   
 50INSERT INTO score VALUES (101,90)   
 51INSERT INTO score VALUES (102,78)   
 52INSERT INTO score VALUES (107, 76) /* 外键错误 */   
 53INSERT INTO score VALUES (103,81)   
 54INSERT INTO score VALUES (104,65)   
 55COMMIT TRAN   
 56PRINT '事务提交'   
 57END TRY   
 58BEGIN CATCH   
 59ROLLBACK   
 60PRINT '事务回滚'   
 61SELECT ERROR_NUMBER() AS ErrorNumber,   
 62ERROR_SEVERITY() AS ErrorSeverity,   
 63ERROR_STATE() as ErrorState,   
 64ERROR_MESSAGE() as ErrorMessage;   
 65END CATCH   
 66GO   
 67  
 68SELECT * FROM score   
 69GO 
 70
 71** 快照隔离 Snapshot Isolation  **   
 72  
 731、写入程序不会阻碍读取程序   
 742、Snapshot isolation must be enabled for DB   
 75ALTER DATABASE 数据库 SET allow_snapshot_isolation ON   
 763、Snapshot isolation must be enabled for connection   
 77Set transaction isolation level snapshot   
 784、UPDATE transactions keep old versions of data in a linked list   
 795、新的隔离级别提供了以下优点：   
 801) 提高了只读应用程序的数据可用性   
 812) 允许在OLTP环境中执行非阻止读取操作   
 823) 可对写入事务进行自动的强制冲突检测   
 836、演示代码   
 84CREATE DATABASE demo2   
 85GO   
 86USE demo2   
 87ALTER DATABASE demo2 SET allow_snapshot_isolation ON   
 88CREATE TABLE test   
 89(   
 90tid INT NOT NULL primary key,   
 91tname VARCHAR(50) NOT NULL   
 92)   
 93INSERT INTO test VALUES(1,'version1')   
 94INSERT INTO test VALUES(2,'version2') 
 95
 96\--连接一 
 97
 98USE demo2   
 99BEGIN TRAN   
100UPDATE test SET tname='version3' WHERE tid=2   
101SELECT * FROM test 
102
103\--连接二   
104USE demo2   
105SET transaction isolation level snapshot   
106SELECT * FROM test 
107
108**TOP 增强功能**
109
1101、TOP 增强   
111可以指定一个数字表达式，以返回要通过查询影响的行数或百分比，还可以根据情况使用变量或子查询。   
112可以在DELETE、UPDATE和INSERT查询中使用TOP选项。   
113  
1142、更好地替换SET ROWCOUNT选项，使之更为有效。 
115
116** OUTPUT  **
117
1181、SQL Server 2005引入一个新的OUTPUT子句，以使您可以冲修改语句(INSERT、UPDATE、DELETE)中将数据返回到表变量中。   
119  
1202、新的OUTPUT子局的语法为：   
121OUTPUT <dml_select_list> INTO @table_variable   
122可以通过引用插入的表或删除的表来访问被修改的行的旧/新影象，其方式与访问触发器类似。在INSERT语句中，只能访问插入的表。在DELETE语句中，只能访问删除的表。在UPDATE语句中，可以访问插入的表和删除的表。   
123  
1243、代码演示   
125USE demo   
126GO   
127CREATE TABLE tt   
128(   
129id INT IDENTITY,   
130c1 VARCHAR(15)   
131)   
132GO   
133  
134INSERT INTO tt VALUES ('r1')   
135INSERT INTO tt VALUES ('r2')   
136INSERT INTO tt VALUES ('r5')   
137INSERT INTO tt VALUES ('r6')   
138INSERT INTO tt VALUES ('r7')   
139INSERT INTO tt VALUES ('r8')   
140INSERT INTO tt VALUES ('r9')   
141INSERT INTO tt VALUES ('r10') 
142
143DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))   
144DELETE tt   
145OUTPUT DELETED.id, DELETED.c1 INTO @del   
146WHERE id &lt; 3   
147SELECT * FROM @del   
148GO   
149\-----------------------------------------------   
150USE demo   
151GO   
152CREATE TABLE toptest (column1 VARCHAR(150))   
153GO   
154INSERT INTO toptest VALUES('t1')   
155INSERT INTO toptest VALUES('t2')   
156INSERT INTO toptest VALUES('t3')   
157INSERT INTO toptest VALUES('t4')   
158INSERT INTO toptest VALUES('t5')   
159INSERT INTO toptest VALUES('t6')   
160INSERT INTO toptest VALUES('t7')   
161INSERT INTO toptest VALUES('t8')   
162SELECT * FROM toptest   
163GO 
164
165CREATE TABLE toptest2 (column2 VARCHAR(150))   
166GO   
167INSERT INTO toptest2 VALUES('c1')   
168INSERT INTO toptest2 VALUES('c2')   
169  
170\--声明3个变量   
171DECLARE @a INT   
172DECLARE @b INT   
173DECLARE @c INT   
174  
175\--赋值   
176SET @a = 10   
177SET @b = 5   
178SELECT @c = @a/@b   
179  
180\--使用计算表达式   
181SELECT TOP(@c) * FROM toptest 
182
183\--使用SELECT语句作为条件   
184SELECT TOP(SELECT COUNT(*) FROM toptest2) *   
185FROM toptest   
186  
187\--指出top   
188DELETE TOP(2) toptest where column1&gt;'t6'   
189  
190\--更新top   
191UPDATE TOP(2) toptest SET column1 = 'hi' where column1&lt;='t2'   
192  
193SELECT * FROM toptest   
194  
195排序函数 Ranking Functions   
196  
1971、SQL Server引入几个新的排序函数：如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。   
198  
1992、排序函数都遵循类似的语法模式：   
200()OVER   
201([PARTITION BY]   
202ORDER BY)   
203该函数只能在查询的两个子句中指定 - 在SELECT子句或ORDER BY子句中。以下详细讨论不同的函数。   
204  
2053、ROW_NUMBER   
206ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序   
207USE demo   
208GO   
209CREATE TABLE rankorder   
210(   
211orderid INT,   
212qty INT   
213)   
214GO   
215INSERT rankorder VALUES(30001,10)   
216INSERT rankorder VALUES(10001,10)   
217INSERT rankorder VALUES(10006,10)   
218INSERT rankorder VALUES(40005,10)   
219INSERT rankorder VALUES(30003,15)   
220INSERT rankorder VALUES(30004,20)   
221INSERT rankorder VALUES(20002,20)   
222INSERT rankorder VALUES(20001,20)   
223INSERT rankorder VALUES(10005,30)   
224INSERT rankorder VALUES(30007,30)   
225INSERT rankorder VALUES(40001,40)   
226GO   
227SELECT orderid,qty,   
228ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,   
229RANK() OVER(ORDER BY qty) AS rank,   
230DENSE_RANK() OVER(ORDER BY qty) AS denserank   
231FROM rankorder   
232ORDER BY qty 
233
234** 通用表表达式 Common Table Expressions  **   
235  
236通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义中，可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能，即CTE可以包含对它们自身的引用。 
237
238视图、派生表和CTE内部的查询的一般形式 
239
2401、视图   
241CREATE VIEW <view_name>(<column_aliases>) AS <view_query>   
242  
2432、派生表   
244SELECT * FROM (<derived_table)query>) AS <dericed_table_alias>(<column_aliases>)   
245  
2463、CTE   
247WITH <cte_alias>(<column_aliases>)   
248AS   
249{   
250<cte_query>   
251)   
252SELECT * FROM <cte_alias]>   
253在关键字WITH之后，为CTE提供一个别名，并且为它的结果列提供一个可选的别名列表；编写CTE的主体；然后从外部查询中引用它。   
254  
2554、演示代码   
256USE AdventureWorks   
257GO   
258WITH SalesCTE(ProductID, SalesOrderID)   
259AS   
260(   
261SELECT ProductID, COUNT(SalesOrderID)   
262FROM Sales.SalesOrderDetail   
263GROUP BY ProductID   
264)   
265SELECT * FROM SalesCTE 
266
267**Recursive CTEs 递归的通用表表达式**
268
269递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。 
270
271\--使用递归的通用表表达式   
272USE demo   
273GO   
274CREATE TABLE CarParts   
275(   
276CarID INT NOT NULL,   
277Part VARCHAR(15),   
278SubPart VARCHAR(15),   
279Qty INT   
280)   
281GO   
282INSERT CarParts VALUES (1, 'Body', 'Door', 4)   
283INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)   
284INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)   
285INSERT CarParts VALUES (1, 'Door', 'Handle', 1)   
286INSERT CarParts VALUES (1, 'Door', 'Lock', 1)   
287INSERT CarParts VALUES (1, 'Door', 'Window', 1)   
288INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)   
289INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)   
290INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)   
291GO   
292SELECT * FROM CarParts   
293GO 
294
295WITH CarPartsCTE(SubPart, Qty)   
296AS   
297(   
298\-- 固定成员 (AM):   
299\-- SELECT查询无需参考CarPartsCTE   
300SELECT SubPart, Qty   
301FROM CarParts   
302WHERE Part = 'Body'   
303UNION ALL   
304\-- 递归成员 (RM):   
305\-- SELECT查询参考CarPartsCTE   
306SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty   
307FROM CarPartsCTE   
308INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part   
309WHERE CarParts.CarID = 1   
310)   
311\-- 外部查询   
312SELECT SubPart, SUM(Qty) AS TotalNUM   
313FROM CarPartsCTE   
314GROUP BY SubPart 
315
316** 新的关系运算符 PIVOT/UNPIVOT/APPLY  **
317
3181、PIVOT   
319PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。   
320  
3212、UNPIVOT   
322UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。   
323  
3243、APPLY   
325APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式：CROSS APPLY和OUTER APPLY。 
326
327演示： 
328
329USE demo   
330GO 
331
332CREATE TABLE orders   
333(   
334Customer VARCHAR(10) NOT NULL,   
335product VARCHAR(20) NOT NULL,   
336quantity INT NOT NULL   
337)   
338GO   
339INSERT orders VALUES('Mike', 'Bike',3)   
340INSERT orders VALUES('Mike','Chain',2)   
341INSERT orders VALUES('Mike','Bike',5)   
342INSERT orders VALUES('Lisa','Bike',3)   
343INSERT orders VALUES('Lisa','Chain',3)   
344INSERT orders VALUES('Lisa','Chain',4)   
345INSERT orders VALUES('Lisa','Bike',2) 
346
347SELECT * FROM orders 
348
349SELECT * FROM orders   
350PIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a   
351USE demo   
352GO   
353CREATE TABLE SALES1   
354(   
355[Year] INT,   
356Quarter CHAR(2),   
357Amount FLOAT   
358)   
359GO   
360INSERT INTO SALES1 VALUES (2001, 'Q1', 80)   
361INSERT INTO SALES1 VALUES (2001, 'Q2', 70)   
362INSERT INTO SALES1 VALUES (2001, 'Q3', 55)   
363INSERT INTO SALES1 VALUES (2001, 'Q3', 110)   
364INSERT INTO SALES1 VALUES (2001, 'Q4', 90)   
365INSERT INTO SALES1 VALUES (2002, 'Q1', 200)   
366INSERT INTO SALES1 VALUES (2002, 'Q2', 150)   
367INSERT INTO SALES1 VALUES (2002, 'Q2', 40)   
368INSERT INTO SALES1 VALUES (2002, 'Q2', 60)   
369INSERT INTO SALES1 VALUES (2002, 'Q3', 120)   
370INSERT INTO SALES1 VALUES (2002, 'Q3', 110)   
371INSERT INTO SALES1 VALUES (2002, 'Q4', 180)   
372GO 
373
374SELECT * FROM SALES1   
375PIVOT   
376(SUM (Amount) --使用SUM聚合数量列   
377FOR [Quarter] --PIVOT Quarter 列   
378IN (Q1, Q2, Q3, Q4)) --使用季节   
379AS P   
380GO 
381
382SELECT * INTO temp1 FROM orders   
383PIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a 
384
385SELECT * FROM temp1 
386
387SELECT customer, product,quantity   
388FROM temp1   
389UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a   
390\----------------------------------------------------   
391USE demo   
392GO   
393CREATE TABLE Arrays   
394(   
395aid INT NOT NULL IDENTITY PRIMARY KEY,   
396array VARCHAR(7999) NOT NULL   
397)   
398GO   
399INSERT INTO Arrays VALUES('')   
400INSERT INTO Arrays VALUES('10')   
401INSERT INTO Arrays VALUES('20,40,30')   
402INSERT INTO Arrays VALUES('-1,-3,-5')   
403GO   
404CREATE FUNCTION function1(@arr AS VARCHAR(7999))   
405RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)   
406AS   
407BEGIN   
408DECLARE @end AS INT, @start AS INT, @pos AS INT   
409SELECT @arr = @arr + ',', @pos = 1,   
410@start = 1, @end = CHARINDEX(',', @arr, @start)   
411WHILE @end &gt; 1   
412BEGIN   
413INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start)) 
414
415SELECT @pos = @pos + 1,   
416@start = @end + 1, @end = CHARINDEX(',', @arr, @start)   
417END   
418RETURN   
419END   
420  
421\--测试   
422SELECT * FROM function1('200,400,300')   
423GO   
424  
425SELECT A.aid, F.*   
426FROM Arrays AS A   
427CROSS APPLY function1(array) AS F   
428GO   
429SELECT A.aid, F.*   
430FROM Arrays AS A   
431OUTER APPLY function1(array) AS F   
432GO 
433
434** DDL触发器 DDL Triggers  **
435
436SQL Server 2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。也可以为单个DDL语句（例如：CREAT_TABLE、DROP_TABLE等）或者为一组语句（例如：指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库所有DDL事件）定义DDL触发器。 
437
438在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。 
439
440DDL触发器特别有用的方案包括DDL更改的完整性检查、审核方案以及其他方案。 
441
442代码演示： 
443
444USE demo   
445GO   
446CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE   
447AS   
448RAISERROR('没有删除表的权限.', 10, 1)   
449PRINT '尝试在数据库' + DB_NAME() + '中删除表.'   
450PRINT CONVERT (nvarchar (1000),EventData())   
451ROLLBACK   
452GO   
453\-- 测试   
454CREATE TABLE TestDROP(col1 INT)   
455GO   
456INSERT INTO TestDROP VALUES(1) 
457
458DROP TABLE testdrop 
459
460\-- Server   
461CREATE TRIGGER audit_ddl_logins ON ALL SERVER   
462FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN   
463AS   
464PRINT '发生DDL LOGIN.'   
465PRINT CONVERT (nvarchar (1000),EventData())   
466GO 
467
468\-- 测试   
469CREATE LOGIN login1 WITH PASSWORD = '123'   
470ALTER LOGIN login1 WITH PASSWORD = 'xyz'   
471DROP LOGIN login1 
472
473** 总结  **
474
475SQL Server 2005中的Transaction-SQL增强功能提高了用户在编写查询时的表达能力，使用户可以改善代码的性能，并且扩充了错误处理能力。 
476
477SQL Server 2005 在Transaction-SQL上所做的改进反映了其更好地满足了ANSI-99 SQL规范的要求以及客户的需求。 
478
479在Transaction-SQL和托管代码之间的选择。</cte_alias]></cte_query></column_aliases></cte_alias></column_aliases></dericed_table_alias></derived_table)query></view_query></column_aliases></view_name></dml_select_list></exception></core>