SqlServer2005T-Sql增强

来源：互联网发布：windows pe和windows7 编辑：程序博客网时间：2024/06/07 04:08

一、大值数据类型... 2

二、TRY…CATCH错误处理... 2

三、快照隔离级别... 3

四、TOP增强... 5

五、OUTPUT子句... 6

六、排名函数... 6

七、APPLY运算符... 7

八、公用表表达式和递归查询... 10

1、公用表达式... 10

2、使用公用表达式实现递归... 10

九、PIVOT和UNPIVOT运算符... 12

十、DDL触发器... 13

正文

一、大值数据类型

● Text、ntext或image的缺陷

◆ 使用updatetext、readtext、writetext的不便

◆ 不能使用大多数常规字符串的功能，比如字符串函数

● Varchar（max）、nvarchar（max）和varbinary（max）可以取代text、ntext或image数据类型

◆ 最多可以保存2G的数据

◆ 可以直接使用常规SQL语句进行操作

◆ 可以进行通常的字符串操作，比如应用字符串函数

示例：

--创建表

create table DemoMax(c1 int , c2 ntext , c3 nvarchar(max))

--插入测试数据

insert into DemoMax(c1,c2,c3) values(1,'Hello ','Hello ')

--查询

select c2+'World' from DemoMax where c1=1 --报错

select c3+'World' from DemoMax where c1=1 --输出Hello World

二、TRY…CATCH错误处理

● 在以前版本的SQL Server中

◆ 需要在每个可能出错的语句后，包含捕获错误和进行错误处理的代码

◆ 可能需要GOTO和Label一起使用

◆ 如果遇到类型转换错误和访问不存在的对象等错误，将导致批处理中止，而不会执

行到错误处理代码

● 使用TRY…CATCH结构

◆ 具有与C#和C++语言中的异常处理类似的错误处理

◆ 需要启用XACT_ABORT ON

◆ 如需捕获错误代码，需要把@@error放置在CATCH块的第一行

◆ 能捕获严重度为10以上的错误

◆ 不能捕获严重度为21以上的错误，比如数据库一致性可疑（23），硬件错误（24）等

示例1：

SET XACT_ABORT ON

BEGIN TRY

select 1/0

END TRY

BEGIN CATCH

select Error_Number() as ErrorNumber, Error_Message() as ErrorMessage

END CATCH

输出结果：

示例2：

--创建表

create table DemoTry(c1 int primary key,c2 char)

--插入数据

insert into DemoTry values(1,'a')

--错误处理代码

set xact_abort on

begin try

begin tran

insert into DemoTry values(1,'a')

commit

end try

begin catch

declare @i as int

set @i=@@error

if @i=2627

select 'primamy key violation'

else

select 'some other errors'

if @@trancount>0

rollback

end catch

输出结果：

三、快照隔离级别

● 打开数据库选项 ALLOW_SNAPSHOT_ISOLATION

● 设置快照隔离级别 SET TRANSACTION ISOLATION LEVEL SNAPSHOT

● 写入程序不会阻碍读取程序

● Tempdb中维护着一个链接列表，保存已更改行的版本

● 更新冲突不太常见的情况下会非常高效

● 其他隔离级别：读未提交、读提交（默认）、可重复读、可串行化

示例1：使用默认的隔离级别（读提交）

准备数据

--创建表

create table DemoSnapshot(c1 int ,c2 char)

--插入数据

insert DemoSnapshot values(1,'a')

第一个事务：

begin tran

select * from demosnapshot

where c1=1

commit

开始这个事务，但先不要提交

第二个事务：

begin tran

update DemoSnapshot set c2='b' where c1=1

commit

执行update，但不提交事务。

这时在第一个事务中查询，将受到阻塞，不能查到结果。

将第二个事务提交后，在事务一中可以查到结果。

示例2：使用快照隔离级别

第一个事务：

--打开数据库选项

alter database Demo

set allow_snapshot_isolation on

--设置隔离级别为快照隔离级别

set transaction isolation level snapshot

begin tran

select * from demosnapshot

where c1=1

commit

打开数据库选项，设置快照隔离级别，开启事务，但先不提交

此时查询到的值为‘a’。

第二个事务：

begin tran

update DemoSnapshot set c2='b' where c1=1

select * from Demosnapshot where c1=1

commit

开启事务，并执行更新将c2更新为‘b’，但不提交事务。

此时，在事务一中查询，查到的值仍为‘a’（tempdb中的旧版本的数据），但不会被阻塞。

四、TOP增强

● TOP运算符得到了增强，可以使用任何数值表达式（如变量名、百分比），而不是仅使用整数来指定该运算符返回的行数

● TOP现在还可以在INSERT、UPDATE和DELETE语句中指定

● 替代SET ROWCOUNT，建议改用TOP关键字，不但可以防止锁升级，还可以提高执行效率

示例：

--创建表

create table DemoTop(c1 int ,c2 char)

--插入数据

insert DemoTop values(1,'a')

insert DemoTop values(2,'b')

insert DemoTop values(3,'c')

insert DemoTop values(4,'d')

insert DemoTop values(5,'e')

insert DemoTop values(6,'f')

insert DemoTop values(7,'g')

insert DemoTop values(8,'h')

insert DemoTop values(9,'i')

insert DemoTop values(10,'j')

--查询

select top(3) * from DemoTop

declare @i as int

set @i=30

select top(@i) percent * from DemoTop

update top(30) percent DemoTop set c2='a'

delete top(50) percent from DemoTop

select * from DemoTop

五、OUTPUT子句

● INSERT、UPDATE和DELETE现在支持OUTPUT子句，它们根据语句修改的行来返回数据

● 可引用INSERTED和DELETED表，与在触发器中引用它们的方式类似

● 可以指定一条INTO子句并将输出导入到一个表变量中

示例：

--建表

create table DemoOutput (c1 int,c2 char)

--插入数据

insert DemoOutput values(1,'a')

insert DemoOutput values(2,'b')

insert DemoOutput values(3,'c')

insert DemoOutput values(4,'d')

insert DemoOutput values(5,'e')

--使用output

delete from DemoOutput

output deleted.*

where c1>3

六、排名函数

排名函数为分区中的每一行返回一个排名值

示例：

select * from

(

select row_number() over(order by linkTime) rowNumber,*

from tlinkrecord

) as a

where a.rowNumber between 11 and 20

七、APPLY运算符

APPLY运算符为查询操作的外部表返回的每个行调用表值函数

CROSS APPLY，如果表值函数返回一个空集合，不返回外部表的行

OUTER APPLY，如果表值函数返回一个空集合，返回一个NULL值的行

示例：

--第一步，创建员工表并插入数据

--------------------------------------------------------------------------

--创建员工表Employees：员工ID，主管ID，姓名，薪水

if exists(select * from sysobjects where name='Employees' and type='u')

drop table Employees

CREATE TABLE Employees

(

EmpID int primary key,

ParentID int NULL,

EmpName varchar(25) NOT NULL,

Salary money NOT NULL

)

--插入数据

INSERT INTO Employees VALUES(1 , NULL, '宋福源', $10000.00)

INSERT INTO Employees VALUES(2 , 1, '王成', $5000.00)

INSERT INTO Employees VALUES(3 , 1, '李炳超', $5000.00)

INSERT INTO Employees VALUES(4 , 2, '孟晓波', $2500.00)

INSERT INTO Employees VALUES(5 , 2, '刘东', $2500.00)

INSERT INTO Employees VALUES(6 , 2, '贾川川', $2500.00)

INSERT INTO Employees VALUES(7 , 3, '鲁佳', $2500.00)

INSERT INTO Employees VALUES(8 , 3, '孟晓丽', $2500.00)

--第二步，创建部门表并插入数据

--------------------------------------------------------------------------

--创建部门表Departments：部门ID，部门名称，部门主管ID（外键）

if exists(select * from sysobjects where name='Departments' and type='u')

drop table Departments

CREATE TABLE Departments

(

DepartID INT NOT NULL PRIMARY KEY,

DepartName VARCHAR(25) NOT NULL,

DepartManagerID INT NULL

)

--插入数据

INSERT INTO Departments VALUES(1, '开发部',2)

INSERT INTO Departments VALUES(2, '市场部',3)

--第三步，创建表值函数

--------------------------------------------------------------------------

--表值函数：根据主管ID，查询该主管下属员工

if exists(select * from sysobjects where name='Fn_GetSubTree' and type='tf')

drop function Fn_GetSubTree

CREATE FUNCTION Fn_GetSubTree(@EmpID AS INT) RETURNS @TREE TABLE

(

EmpID INT NOT NULL,

EmpName VARCHAR(25) NOT NULL,

ParentID INT NULL,

[Level] INT NOT NULL

)

BEGIN

WITH Employees_Subtree(EmpID, EmpName, ParentID, [Level])

(

SELECT EmpID, EmpName, ParentID, 0

FROM Employees

WHERE EmpID = @EmpID

UNION all

SELECT e.EmpID, e.EmpName, e.ParentID, es.[Level]+1

FROM Employees AS e

JOIN Employees_SubTree AS es

ON e.ParentID = es.EmpID

)

INSERT INTO @TREE

SELECT * FROM Employees_Subtree

RETURN

END

--第四步，查询

--1 查询表中的数据

select * from employees

select * from departments

select * from Fn_GetSubTree(2)

--2 使用apply查询

SELECT * FROM Departments AS D

CROSS APPLY fn_getsubtree(D.DepartManagerID) AS ST

SELECT * FROM Departments AS D

OUTER APPLY fn_getsubtree(D.DepartManagerID) AS ST

--3 使用sqlserver2000中的方式（游标）查询

declare @tem table(

Empid int,

ParentID int,

Empname varchar(25),

[Level] int,

DepartID INT,

DepartName VARCHAR(25),

DepartManagerID INT

)

DECLARE @ids int

DECLARE test_cursor CURSOR FOR

select DepartManagerID FROM Departments

OPEN test_cursor

FETCH NEXT FROM test_cursor

INTO @ids

WHILE @@FETCH_STATUS = 0

begin

insert into @tem select empid, ParentID, empname, [level], parentID, departName, DepartManagerID from dbo.fn_getsubtree(@ids) left join Departments on DepartManagerID=@ids

FETCH NEXT FROM test_cursor

INTO @ids

end

CLOSE test_cursor

DEALLOCATE test_cursor

select * from @tem

八、公用表表达式和递归查询

1、公用表达式

● 公用表表达式（CTE）可以认为是在单个SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句的执行范围内定义的临时结果集

● CTE与派生表类似，具体表现在不存储为对象，并且只在查询期内有效。与派生表的不同之处在于，CTE可自引用，还可以在同一查询中引用多次

● 使用CTE可以获得提高可读性和轻松维护复杂查询的优点

● 可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义CTE

示例1：

with test_cte(a,b,c)

(

select 'a','b','c'

)

select * from test_cte

示例2：

WITH Sales_CTE(SalesPersonID,NumberOrders,MaxDate)

(

select SalesPersonID,count(*),max(OrderDate)

from sales.salesOrderHeader

group by SalesPersonID

)

select * from Sales_CTE

2、使用公用表达式实现递归

● 递归CTE结构必须至少包含一个定位点成员和一个递归成员

● 递归执行的语义如下：

◆ 将CTE表达式拆分为定位点成员和递归成员

◆ 运行定位点成员，创建第一个调用或基准结果集（T0）

◆ 运行递归成员，将Ti作为输入，将Ti+1作为输出

◆ 重复步骤3，直到返回空集

◆ 返回结果集。这是对T0到Tn执行UNION ALL的结果

示例：

--创建表

if exists(select * from sysobjects where name='Employees' and type ='u')

drop table Employees

create table Employees(EmpID int , EmpName nvarchar(50), parentID int)

--插入数据

insert Employees values(1,'宋福源',0)

insert Employees values(2,'王成',1)

insert Employees values(3,'李炳超',1)

insert Employees values(4,'孟晓波',2)

insert Employees values(5,'刘东',2)

insert Employees values(6,'贾川川',2)

insert Employees values(7,'鲁佳',3)

insert Employees values(8,'孟晓丽',3)

insert Employees values(9,'张丽',4)

--查询‘王成’所主管的员工

WITH Employees_Subtree(EmpID, EmpName, ParentID, [Level])

(

SELECT EmpID, EmpName, ParentID, 0

FROM Employees

WHERE EmpID=2

UNION all

SELECT e.EmpID, e.EmpName, e.ParentID, es.[Level]+1

FROM Employees AS e

JOIN Employees_SubTree AS es

ON e.ParentID = es.EmpID

)

select * from Employees_Subtree

表中的所有数据：

执行上述查询结果：--查询‘王成’所主管的员工

九、PIVOT和UNPIVOT运算符

PIVOT运算符将行转为列，并执行汇总运算（如SUM,AVG等）。

UNPIVOT运算符和PIVOT相反。

示例：

--创建表

if exists(select * from sysobjects where name='DemoPivot' and type='u')

drop table DemoPivot

create table DemoPivot

(EmpName nvarchar(50),ProductName nvarchar(50),salesCount int)

--插入测试数据

insert DemoPivot values('王成','轮胎',2)

insert DemoPivot values('王成','坐垫',1)

insert DemoPivot values('王成','坐垫',2)

insert DemoPivot values('张婷','轮胎',10)

insert DemoPivot values('张婷','轮胎',4)

insert DemoPivot values('张婷','坐垫',5)

--查询所有记录

select * from DemoPivot

--使用pivot查询

select * from DemoPivot

pivot (sum(salesCount) for ProductName

in([轮胎],[坐垫])) pvt

--sqlserver2000中的查询

select EmpName,

sum(case ProductName when '轮胎' then salesCount else 0 end) as '轮胎',

sum(case ProductName when '坐垫' then salesCount else 0 end) as '坐垫'

from DemoPivot

group by EmpName

所有记录：

Pivot查询结果：

Sqlserver2000中的查询结果：

十、DDL触发器

● 除了对修改数据的数据操作语言语句（INSERT、UPDATE和DELETE）所激发的触发器提供支持外，数据库引擎还包括一类新的触发器，由修改数据库对象的DDL语句（如以CREATE、ALTER或DROP开头的语句）激发

● DDL触发器可用于审核或控制对数据库架构的更改

示例：

create trigger safety on database

for drop_table

print '不能执行drop table操作，除非禁用该触发器'

rollback

create table test(c1 int,c2 char)

drop table test

select * from test