sql server 2005几个新功能

 

 

1、Snapshot Isolation
     Snapshot isolation must be enabled for DB
      ALTER DATABASE {DBNAME} SET allow_snapshot_isolation ON
      Set transaction isolation level snapshot
      UPDATE transactions keep old versions of data in a linked list
     新的隔离级别提供了以下优点：
　 1) 提高了只读应用程序的数据可用性
　 2) 允许在OLTP环境中执行非阻止读取操作
　 3) 可对写入事务进行自动的强制冲突检测

 1USE master
 2   ALTER DATABASE T2target  SET allow_snapshot_isolation ON
 3   CREATE TABLE test
 4   ( 
 5     tid INT NOT NULL primary key,
 6     tname VARCHAR(50) NOT NULL
 7   )
 8  INSERT INTO test VALUES(1,'version1')
 9  INSERT INTO test VALUES(2,'version2')
10
11--连接一
12
13USE demo2
14BEGIN TRAN
15   UPDATE test SET tname='version4' WHERE tid=2
16   SELECT * FROM test
17 commit
18--连接二
19USE demo2
20   SET transaction isolation level snapshot
21   SELECT * FROM test 
22

2、OUTPUT的使用
     SQL Server 2005引入一个新的OUTPUT子句，以使您可以冲修改语句(INSERT、UPDATE、DELETE)中将数据返回到表变量中。
   新的OUTPUT子局的语法为：
OUTPUT <dml_select_list> INTO @table_variable
可以通过引用插入的表或删除的表来访问被修改的行的旧/新影象，其方式与访问触发器类似。在INSERT语句中，只能访问插入的表。在DELETE语句中，只能访问删除的表。在UPDATE语句中，可以访问插入的表和删除的表。

 1USE demo
 2GO
 3CREATE TABLE tt 
 4(
 5  id INT IDENTITY, 
 6  c1 VARCHAR(15)
 7)
 8GO
 9
10INSERT INTO tt VALUES ('r1')
11INSERT INTO tt VALUES ('r2')
12INSERT INTO tt VALUES ('r5')
13INSERT INTO tt VALUES ('r6')
14INSERT INTO tt VALUES ('r7')
15INSERT INTO tt VALUES ('r8')
16INSERT INTO tt VALUES ('r9')
17INSERT INTO tt VALUES ('r10')
18
19DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
20DELETE tt
21OUTPUT DELETED.id, DELETED.c1 INTO @del
22WHERE id < 3
23SELECT * FROM @del
24SELECT * FROM tt
25

3、Top增强

可以指定一个数字表达式，返回要通过查询影响的行数或百分比，还可以根据情况使用变量或子查询。
可以在DELETE、UPDATE和INSERT查询中使用TOP，更好地替换SET ROWCOUNT选项，使之更为有效。

 1CREATE TABLE toptest (column1 VARCHAR(150))
 2GO
 3INSERT INTO toptest VALUES('t1')
 4INSERT INTO toptest VALUES('t2')
 5INSERT INTO toptest VALUES('t3')
 6INSERT INTO toptest VALUES('t4')
 7INSERT INTO toptest VALUES('t5')
 8INSERT INTO toptest VALUES('t6')
 9INSERT INTO toptest VALUES('t7')
10INSERT INTO toptest VALUES('t8')
11SELECT * FROM toptest
12GO
13
14CREATE TABLE toptest2 (column2 VARCHAR(150))
15GO
16INSERT INTO toptest2 VALUES('c1')
17INSERT INTO toptest2 VALUES('c2')
18
19--声明3个变量
20DECLARE @a INT
21DECLARE @b INT
22DECLARE @c INT
23
24--赋值
25SET @a = 10
26SET @b = 5
27SELECT @c = @a/@b
28
29--使用计算表达式
30SELECT TOP(@c) * FROM toptest
31
32--使用SELECT语句作为条件
33SELECT TOP(SELECT COUNT(*) FROM toptest2) * 
34FROM toptest
35
36--指出top
37DELETE TOP(2) toptest where column1>'t6'
38
39--更新top
40UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'
41
42SELECT * FROM toptest
43

4、排序函数 Ranking Functions
SQL Server引入几个新的排序函数：如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。
排序函数都遵循类似的语法模式：()OVER([PARTITION BY] ORDER BY)
该函数只能在查询的两个子句中指定 - 在SELECT子句或ORDER BY子句中。
ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序。

 1CREATE TABLE rankorder
 2(
 3 orderid INT,
 4 qty INT
 5)
 6GO
 7INSERT rankorder VALUES(30001,10)
 8INSERT rankorder VALUES(10001,10)
 9INSERT rankorder VALUES(10006,10)
10INSERT rankorder VALUES(40005,10)
11INSERT rankorder VALUES(30003,15)
12INSERT rankorder VALUES(30004,20)
13INSERT rankorder VALUES(20002,20)
14INSERT rankorder VALUES(20001,20)
15INSERT rankorder VALUES(10005,30)
16INSERT rankorder VALUES(30007,30)
17INSERT rankorder VALUES(40001,40)
18GO
19SELECT orderid,qty,
20  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
21  RANK()       OVER(ORDER BY qty) AS rank,
22  DENSE_RANK() OVER(ORDER BY qty) AS denserank 
23FROM rankorder
24ORDER BY qty

5、Common Table Expressions 

通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义中，可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能，即CTE可以包含对它们自身的引用。

 1USE AdventureWorks
 2GO
 3WITH SalesCTE(ProductID, SalesOrderID)
 4AS 
 5(
 6 SELECT ProductID, COUNT(SalesOrderID) 
 7 FROM Sales.SalesOrderDetail 
 8 GROUP BY ProductID
 9)
10SELECT * FROM SalesCTE

Recursive CTEs 递归的通用表表达式

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

 

 1--使用递归的通用表表达式
 2USE demo
 3GO
 4CREATE TABLE CarParts
 5(
 6 CarID INT NOT NULL,
 7 Part VARCHAR(15),
 8 SubPart VARCHAR(15),
 9 Qty INT
10)
11GO
12INSERT CarParts VALUES (1, 'Body', 'Door', 4)
13INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
14INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
15INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
16INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
17INSERT CarParts VALUES (1, 'Door', 'Window', 1)
18INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
19INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
20INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
21GO
22SELECT * FROM CarParts
23GO
24
25WITH CarPartsCTE(SubPart, Qty) 
26AS
27(
28 -- 固定成员 (AM):
29 -- SELECT查询无需参考CarPartsCTE
30 SELECT SubPart, Qty
31 FROM CarParts
32 WHERE Part = 'Body'
33 UNION ALL
34 -- 递归成员 (RM):
35 -- SELECT查询参考CarPartsCTE
36 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
37 FROM CarPartsCTE 
38 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
39 WHERE CarParts.CarID = 1
40)
41-- 外部查询
42SELECT SubPart, SUM(Qty) AS TotalNUM
43FROM CarPartsCTE
44GROUP BY SubPart   

6、新的关系运算符 PIVOT/UNPIVOT/APPLY
PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式：CROSS APPLY和OUTER APPLY。

  1CREATE TABLE orders
  2(
  3 Customer VARCHAR(10) NOT NULL,
  4 product VARCHAR(20) NOT NULL,
  5 quantity INT NOT NULL
  6)
  7GO
  8INSERT orders VALUES('Mike', 'Bike',3)
  9INSERT orders VALUES('Mike','Chain',2)
 10INSERT orders VALUES('Mike','Bike',5)
 11INSERT orders VALUES('Lisa','Bike',3)
 12INSERT orders VALUES('Lisa','Chain',3)
 13INSERT orders VALUES('Lisa','Chain',4)
 14INSERT orders VALUES('Lisa','Bike',2)
 15
 16SELECT * FROM orders
 17
 18SELECT * FROM orders
 19PIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a
 20USE demo
 21GO
 22CREATE TABLE SALES1
 23(
 24  [Year] INT,
 25  Quarter CHAR(2),
 26  Amount FLOAT
 27)
 28GO
 29INSERT INTO SALES1 VALUES (2001, 'Q1', 80)
 30INSERT INTO SALES1 VALUES (2001, 'Q2', 70)
 31INSERT INTO SALES1 VALUES (2001, 'Q3', 55)
 32INSERT INTO SALES1 VALUES (2001, 'Q3', 110)
 33INSERT INTO SALES1 VALUES (2001, 'Q4', 90)
 34INSERT INTO SALES1 VALUES (2002, 'Q1', 200)
 35INSERT INTO SALES1 VALUES (2002, 'Q2', 150)
 36INSERT INTO SALES1 VALUES (2002, 'Q2', 40)
 37INSERT INTO SALES1 VALUES (2002, 'Q2', 60)
 38INSERT INTO SALES1 VALUES (2002, 'Q3', 120)
 39INSERT INTO SALES1 VALUES (2002, 'Q3', 110)
 40INSERT INTO SALES1 VALUES (2002, 'Q4', 180)
 41GO
 42
 43SELECT * FROM SALES1
 44PIVOT
 45(SUM (Amount) --使用SUM聚合数量列
 46FOR [Quarter] --PIVOT Quarter 列
 47IN (Q1, Q2, Q3, Q4)) --使用季节
 48AS P
 49GO
 50
 51SELECT * INTO temp1 FROM orders
 52PIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a
 53
 54SELECT * FROM temp1
 55
 56SELECT customer, product,quantity
 57FROM temp1
 58UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a
 59----------------------------------------------------
 60USE demo
 61GO
 62CREATE TABLE Arrays
 63(
 64  aid INT NOT NULL IDENTITY PRIMARY KEY,
 65  array VARCHAR(7999) NOT NULL
 66)
 67GO
 68INSERT INTO Arrays VALUES('')
 69INSERT INTO Arrays VALUES('10')
 70INSERT INTO Arrays VALUES('20,40,30')
 71INSERT INTO Arrays VALUES('-1,-3,-5')
 72GO
 73CREATE FUNCTION  function1(@arr AS VARCHAR(7999))
 74  RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
 75AS
 76BEGIN
 77  DECLARE @end AS INT, @start AS INT, @pos AS INT
 78  SELECT @arr = @arr + ',', @pos = 1,
 79    @start = 1, @end = CHARINDEX(',', @arr, @start)
 80  WHILE @end > 1
 81  BEGIN
 82    INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))
 83
 84    SELECT @pos = @pos + 1,
 85      @start = @end + 1, @end = CHARINDEX(',', @arr, @start)
 86  END
 87  RETURN
 88END
 89
 90--测试
 91SELECT * FROM function1('200,400,300')
 92GO
 93
 94SELECT A.aid, F.*
 95FROM Arrays AS A
 96  CROSS APPLY function1(array) AS F
 97GO
 98SELECT A.aid, F.*
 99FROM Arrays AS A
100  OUTER APPLY function1(array) AS F
101GO
102

7、DDL触发器
SQL Server 2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。也可以为单个DDL语句（例如：CREAT_TABLE、DROP_TABLE等）或者为一组语句（例如：指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库所有DDL事件）定义DDL触发器。在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。DDL触发器特别有用的方案包括DDL更改的完整性检查、审核方案以及其他方案。

CREATE TRIGGER [ddlDatabaseTriggerLog] 
ON DATABASE 
FOR CREATE_ASSEMBLY,
 ALTER_ASSEMBLY,
 DROP_ASSEMBLY,
CREATE_FUNCTION,
 ALTER_FUNCTION,
 DROP_FUNCTION,
CREATE_PROCEDURE,
 ALTER_PROCEDURE,
 DROP_PROCEDURE,
CREATE_TABLE,
 ALTER_TABLE,
DROP_TABLE, 
CREATE_TRIGGER,
 ALTER_TRIGGER,
 DROP_TRIGGER,
CREATE_VIEW,
 ALTER_VIEW,
 DROP_VIEW
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[AppDatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent],
        HostName,
        Version
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data,HOST_NAME(),'1.3'
        
        );
END;

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE