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