SQL SERVER 2005 T_SQL新的特性以及解决并发

一.修改语句的增强
UPDATE  test  SET  tname.WRITE( ' one hundred and two ' ,  9 ,  3 )  WHERE  tid  =   102

在2005中增强了update方法，这是修改test表的列tname WRITE方法是把tname这个列中从字符串9开始 把3个长的字符串改为one hundred  and  two

二、异常的捕获
可以捕获过去会导致批处理终止和事务的错误，但是不能处理连接中断错误和硬件错误等
-- Using the try..catch.. construct and invoking a run-time error
SET  XACT_ABORT  of    这个打开捕获异常的开关 ，默认是关闭的
BEGIN  TRY
    BEGIN   TRAN
         INSERT   INTO  score  VALUES  ( 102 , 78 ) 
     INSERT   INTO  score  VALUES  ( 107 ,  76 )  /**/ /* Foreign Key Error */  
     INSERT   INTO  score  VALUES  ( 103 , 81 ) 
    COMMIT   TRAN
     PRINT   ' Transaction committed '
END  TRY
BEGIN  CATCH
     ROLLBACK
     PRINT   ' Transaction rolled back '
         SELECT  ERROR_NUMBER()  AS  ErrorNumber,
        ERROR_SEVERITY()  AS  ErrorSeverity,
        ERROR_STATE()  as  ErrorState,
        ERROR_MESSAGE()  as  ErrorMessage;
END  CATCH
GO
三、快照隔离
写入程序不会影响读取程序，可对事务冲突的检测
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 ' )

-- connection 1

use  demo2
begin   tran
  update  test  set  tname = ' version3 '   where  tid = 2
  select   *   from  test

-- connection 2
use  demo2
set   transaction   isolation   level  snapshot
select   *   from  test

-- it will ok, you can see it 
四 、top语句的增强
可以是数字表达式，一返回要通过通过查询影响的行输或者百分比还可以是更具情况使用变量和子查询
可以在delete、update和insert中使用top选项，更好的代替set rowcount选项，使之更为有效。
-- create a table and insert some data
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 ' )

-- declare 3 variables
DECLARE   @a   INT
DECLARE   @b   INT
DECLARE   @c   INT
-- set values
SET   @a   =   10
SET   @b   =   5
SELECT   @c   =   @a / @b
-- use the calculated expression
SELECT   TOP ( @c )  *   FROM  toptest
-- use a SELECT statement as expression
SELECT   TOP ( SELECT   COUNT ( * )  FROM  toptest2)  *  
FROM  toptest

-- DML top
DELETE   TOP ( 2 ) toptest  where  column1 > ' t6 '
-- this sets 't1' and 't2' to 'hi'
UPDATE   TOP ( 2 ) toptest  SET  column1  =   ' hi '   where  column1 <= ' t2 '

SELECT   *   FROM  toptest
五、output
引入一个新的output字句 可以使您从修改语句（elete、update和insert）中将数据返回到表变量中
语法：output  < dml_select_list >   into   @table_variable
可以通过应用插入的表和删除的表来访问被修改的行的旧 / 新映象，起方式于访问触发器类似，在insert语句中，只能访问插入的表，update和delete也一样。访问临时表
-- create table and insert data
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 ' )

-- make a table variable to hold the results of the OUTPUT clause
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

六、函数
ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序
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
七、通用表达式 cte 临时命名的结果集
USE  AdventureWorks
GO
WITH  SalesCTE(ProductID, SalesOrderID)
AS  
(
     SELECT  ProductID,  COUNT (SalesOrderID) 
     FROM  Sales.SalesOrderDetail 
     GROUP   BY  ProductID
)
SELECT   *   FROM  SalesCTE
-- --


--  Using CTEs Recursively
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
(
     --  Anchor Member (AM):
     --  SELECT query that doesn’t refer back to CarPartsCTE
     SELECT  SubPart, Qty
     FROM  CarParts
     WHERE  Part  =   ' Body '
     UNION   ALL
     --  Recursive Member (RM):
     --  SELECT query that refers back to CarPartsCTE
     SELECT  CarParts.SubPart, CarPartsCTE.Qty  *  CarParts.Qty
     FROM  CarPartsCTE 
     INNER   JOIN  CarParts  ON  CarPartsCTE.SubPart  =  CarParts.Part
     WHERE  CarParts.CarID  =   1
)
-- outer query
SELECT  SubPart,  SUM (Qty)  AS  TotalNUM
FROM  CarPartsCTE
GROUP   BY  SubPart
八、新的关系运算符
pivot   把行转回为列
unpivot 把列转回为行
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)  -- Aggregate the Amount column using SUM
FOR   [ Quarter ]   -- Pivot the Quarter column into column headings
IN  (Q1, Q2, Q3, Q4))  -- use these quarters
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
froam temp1
unpivot(quantity  for  product  in  ( [ Bike ] , [ Chain ] ))  as  a
九、ddl触发器
use  demo
go
CREATE   TRIGGER  prevent_drop_table  ON   DATABASE   FOR  DROP_TABLE
AS
RAISERROR ( ' Not allowed to drop tables. ' ,  10 ,  1 )
PRINT   ' DROP TABLE attempt in database  '   +   DB_NAME ()  +   ' . '
PRINT   CONVERT  ( nvarchar  ( 1000 ),EventData())
ROLLBACK
GO
--  test
CREATE   TABLE  TestDROP(col1  INT )
go
INSERT   INTO  TestDROP  VALUES ( 1 )

drop  talbe testdrop

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

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

丰富的数据类型 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
      < 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
   -- 创建工作表

   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

-- 使用TRYCATCH构造，并调用一个运行时错误
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  < dml_select_list >   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   < 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上所做的改进反映了其更好地满足了ANSI - 99  SQL规范的要求以及客户的需求。

create       proc   [ dbo ] . [ Name_Add ]
  @Name   varchar ( 50 )
as
begin

begin   tran
insert  Names (Name)
select  ( @Name )  where   not   exists  ( select  NameId  from  Names  with ( HOLDLOCK )  where  Name  =   @Name )
commit   tran

select  NameId,Name  from  Names  with (nolock)  where  Name  =   @Name
end

 要点：检查，加锁，插入值在一句sql中完成.这样再大的并发也不怕了.