Sql Server 2005 T-SQL 增强示例

1、MAX数据类型，结合Update.write方法进行更新(If the target LOB is NULL,an update that uses WRITE will fail)
CREATE TABLE test
(
  tid INT  NOT NULL PRIMARY KEY,
  tname  VARCHAR(MAX)   NULL
)
INSERT INTO test(tid,tname) VALUES(102, 'Customer 102 text data')
select * from test
UPDATE test SET tname.WRITE('one hundred and two', 9, 3) WHERE tid = 102
select * from test


2、Top增强
--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



3、Output语句
--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




4、Try and Catch 功能
USE demo
GO
--creating worktables

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

--Invoking a run-time error
SET XACT_ABORT OFF
BEGIN TRAN
INSERT INTO score  VALUES (101,90)
        INSERT INTO score VALUES (102,78)
INSERT INTO score VALUES (107, 76) /* Foreign Key Error */
INSERT INTO score VALUES (103,81)
        INSERT INTO score VALUES (104,65)
COMMIT TRAN
go
select * from student
select * from score

--Using the try..catch.. construct and invoking a run-time error
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) /* Foreign Key Error */
INSERT INTO score VALUES (103,81)
        INSERT INTO score VALUES (104,65)
   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

SELECT * FROM score
GO


5、Apply语句
使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。
例子：
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
--test
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

例子2 ：为每个消费者返回最新的两个订单
select c.customerid,city,orderid from customers c outer apply (select top 2 orderid,customerid from orders as o where o.customerid=c.customerid) as CA


6、数据库级触发器
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



7、ROW_NUMBER()、RANK()、DENSE_RANK()函数
ROW_NUMBER：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
RANK：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
DENSE_RANK：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
NTILE(integer_expression):将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。


例子：
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 ,
  ntile(2) over(order by qty) as ntile1
FROM rankorder
ORDER BY qty



8、快照隔离
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




9、Top字句功能增强
--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
--在 TOP 中使用变量
SELECT TOP(@c) * FROM toptest
--在 TOP 中select 表达式
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'

--在 TOP 中使用 PERCENT 和 WITH TIES
以下示例获取所有雇员中薪水最高的 10% 的员工，并根据基本薪金按降序返回。
指定 WITH TIES 可确保结果集中同时包含其薪水与返回的最低薪水相同的所有雇员，
即使这样返回的雇员数会超过总数的 10% 也是这样。

USE AdventureWorks ;
GO
SELECT TOP(10) PERCENT WITH TIES
EmployeeID, Title, DepartmentID, Gender, BaseRate
FROM HumanResources.Employee
ORDER BY BaseRate DESC;


10、行列转换 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    --in 里面的用[],而不是用''

select * from temp1

select customer, product,quantity
froam temp1
unpivot(quantity for product in ([Bike],[Chain])) as a