一．新型超大数据类型 Large-Value Data Types
关于 SQL Server2000
任何超过8KB的大容量数据(文档,图片,音像)必须 要存储在 text, ntext, 和 image 数据类型中
任何超过8KB数据面临非常有限的操作功能
SQL Server2005 提供的 MAX 定义符
增强并且延伸了varchar, nvarchar 和 varbinary 传统数据类型的存储能力
varchar(max), nvarchar(max), 和 varbinary(max) 从此成为标准T_SQL的大容量存储数据类型
最多可存储到场2GB的大容量数据
关于大或小容量数据类型的统一的编程模式
对比 Comparisons
连接 Concatenation
变量 Variables
参数 Parameters
触发器 Triggers
集合 Aggregates
排序和索引 Index Included Columns
当小容量数据增长并且超越8K极限时,整个过度过程十分平滑和简捷
通过
.WRITE (
expression
,
@Offset
,
@Length)可对大小容量数据实行局部或正体的直接更改
对于小于8K的数据, 相比text, ntext 和 image存取效率明显提高
建议取代对text, ntext 和 image的应用
CREATE TABLE MyPublications
(PublicationID int,
Abstract
nvarchar(max),
Publication varbinary(max));
Use .WRITE (expression, @Offset, @Length) to update the word
‘writer
’ (@Offset=20, @Length=6) with
‘author
’ in the Abstract column
UPDATE MyPublications
SET Abstract .WRITE (N
‘author', 20, 6)
WHERE PublicationID = 1;
二．先进的错误处理 Error Handling
在 SQL Server2000
@@ERROR 返回最后一个执行的T-SQL语句的错误代码
@@ERROR 的值会随着每一个T-SQL语句而被更新
在SQL Server2005中的丰富的异常处理框架
TRY…CATCH 配置
提供捕获所有SQL SERVER异常或错误的功能
可以捕获和处理过去会导致批处理终止的错误,从而阻止批处理的中断
提供处理和登录异常或错误的功能
当错误发生时,阻止T-SQL交易环境的丢失
可以对错误的具体内容进行读取
语法和定义：
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
{ sql_statement | statement_block }
END CATCH
[ ; ]
任何在TRY模块中产生的错误会将控制的流程转移到CATCH模块中
TRY…CATCH 配置是可以被包含和兼容的
可处理所有付值给@@ERROR的T-SQL运行过程中的错误
T-SQL语句中断错误
T-SQL水平中断错误
T-SQL批处理中断错误
T-SQL交易中断错误
不处理以下的情况:
任何严重性在0-10范围的警告和报告性的信息
任何严重性在20-25范围的中断数据库连接的错误
注意事项
KILL语句
RAISERROR 可以用来自行生成错误
控制流程会转移到最接近的CATCH模块中
错误信息可通过运用以下函数而在CATCH模块中被获取
ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_LINE()
ERROR_PROCEDURE()
ERROR_MESSAGE()
交易信息
任何交易中断的错误都会最终导致成一个未成功交易
XACT_STATE() 总是返回任何交易的状态(1, 0, -1)
实例演示
一：
BEGIN TRY
-- Divide-by-zero error shifts control flow to the CATCH block
SELECT 1/0;
END TRY
BEGIN CATCH
-- Retrieve error information
SELECT ERROR_NUMBER() AS ErrNumber,
ERROR_SEVERITY() AS ErrSeverity,
ERROR_STATE() AS ErrState,
ERROR_PROCEDURE() AS ErrProc,
ERROR_LINE() AS ErrLine,
ERROR_MESSAGE() AS ErrMessage;
END CATCH;
二：
USE AdventureWorks;
GO
CREATE PROCEDURE usp_GetErrorInfo AS
SELECT ERROR_NUMBER() AS ErrNumber,
ERROR_SEVERITY() AS ErrSeverity,
ERROR_STATE() as ErrState,
ERROR_LINE() as ErrLine,
ERROR_PROCEDURE() as ErrProc,
ERROR_MESSAGE() as ErrMessage;
GO
BEGIN TRY
BEGIN TRANSACTION;
-- Generate a constraint violation error
DELETE FROM Production.Product WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
EXECUTE usp_GetErrorInfo;
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
三．通用表表达式 Common Table Expressions
通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集; 可视为类似于视图和派生表混合功能的改进版本
它可以被定义在任何一个SELECT, INSERT, UPDATE, DELETE, 或 CREATE VIEW的T-SQL语句中
它可以被自己引用并在查询中多次被引用
用途:
递归查询
替代那些不需要存储在元数据中的视图
聚合派生表生成的表列
可以在同一个T-SQL语句中多次引用结果集
语法和定义：
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query_definition>
)
SELECT *
FROM <cte_alias>
WITH子句和SELECT/INSERT/DELETE/UPDATE结合在一起形成一个单独的T-SQL语句
在一个单独的T-SQL语句中, 可以有多个CTE被定义在一个单独的WITH子句里
实例演示
WITH mid_cte
AS
(
SELECT ((MAX(value)
– MIN(value)) / 2 ) AS midval
FROM Invoices
)
SELECT
CASE
WHEN value > mid_cte.midval THEN 0
ELSE 1
END AS half,
Invoices.*
FROM Invoices, mid_cte
ORDER BY half;
任何一个引用它自己的CTE可以被认为是递归的
包含一个固定成员和递归成员;递归成员可以被反复调用
递归查询会直到递归成员不在返回行时才会结止
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query_definition> -- Anchor member is defined
UNION ALL
<cte_query_definition> -- Recursive member is defined
-- referencing cte_alias
)
SELECT *
FROM <cte_alias>
实例演示
-- Returns all employees reporting to Employee with EmployeeID=109
WITH EmpCTE (EmployeeID, ManagerID, Title)
AS
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE EmployeeID = ‘109’
UNION ALL
SELECT E.EmployeeID, E.ManagerID, E.Title
FROM HumanResources.Employee AS E
JOIN EmpCTE AS M
ON E.ManagerID = M.EmployeeID
)
SELECT * FROM EmpCTE
四．DDL 触发器 DDL Triggers
允许为所有发生的T-SQL的数据定义语言(DDL)事件定义触发器
DDL触发器可以被定义在:
单个DDL语句:
CREATE_TABLE, ALTER_PROCEDURE, DROP_LOGIN, etc
一组语句:
DDL_DATABASE_LEVEL_EVENTS, DDL_DATABASE_SECURITY_EVENTS, etc
DDL触发器可被规划在数据库和服务器的范围内
EventData() 函数
在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。
实例演示
CREATE TRIGGER trg_disallow_create_table
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'CREATE TABLE Issued.
’;
SELECT EVENTDATA().value('(/EVENT_INSTANCE/ TSQLCommand/CommandText)[1]','nvarchar(max)');
RAISERROR ('New tables cannot be created in this database.', 16, 1);
ROLLBACK;
;
五．新的排序函数 Ranking Functions
返回和提供任何一个结果行在分区里的排序值
ROW_NUMBER
RANK
DENSE_RANK
NTILE
用途
给结果行制定连续的整数排序值
数据分析和采集: Paging, Scoring, Histograms
ROW_NUMBER
对于在一个结果集的一个分区里的一个结果行,返回一个连续的整数排序值
RANK
对于在一个结果集的一个分区里的结果行,返回行的等级
每一个结果行的等级取决于当前行与先前结果行之间就排序列值的比较结果;含相同排序列值的结果行会计算在当前行的等级值里
DENSE_RANK
对于在一个结果集的一个分区里的结果行,返回不间断的行的等级
每一个结果行的等级取决于当前行与先前结果行之间就排序列值的比较结果;含相同排序列值的结果行不会计算在当前行的等级值里
NTILE
将在一个排序分区里的结果行分布成特定的小组
对于每一个结果行,NTILE会返回这个结果行所属的小组的号码
实例演示
SELECT
ROW_NUMBER() OVER(ORDER BY City) AS Row_Num,
RANK() OVER(ORDER BY City) AS Rank,
RANK() OVER(PARTITION BY City
ORDER BY LastName) AS Part_Rank,
DENSE_RANK() OVER(ORDER BY City) AS Dense_Rank,
NTILE(4) OVER(ORDER BY City, ) AS NTile_4,
LastName, FirstName, City
FROM Employees
ORDER BY City, LastName
六．新的关系运算符 PIVOT/UNPIVOT/APPLY
可将一个table-valued的表达式转换成一个表
可被定义在FROM子句里
PIVOT
将行旋转为列
可能同时执行聚合
用途:
处理 open-schema 景象
生成跨越表格的报表以便更好的统计数据
UNPIVOT
与PIVOT相反的操作; 将列旋转为行
更多的用来转换已经经过PIVOT处理的数据
实例演示
APPLY
APPLY关系运算符允许您对外部表的每个行调用指定的表值函数(TVF)一次
可被定义在FROM子句里
APPLY的两种形式:
CROSS APPLY
对于从表值函数中生成结果集的外部表, CROSS APPLY从中返回相应的行
和内部链接(INNER JOIN)效果相同
OUTER APPLY
OUTER APPLY从外部表中返回所有的行
用NULL替代空结果集里的表值函数列
和外部链接(left outer join)效果相同
七．新的结果集运算符 EXCEPT and INTERSECT
从另外两个结果集产生出一个新的结果集
EXCEPT 返回所有属于第一个结果集但不属于第二个结果集的行
INTERSECT返回所有共同属于两个结果集的行
八．OUTPUT在DML中的特别运用
新的OUTPUT子句可以从INSERT/UPDATE/DELETE T-SQL语句中返回数据
从OUTPUT返回的数据会直接传到调用的应用程序中
从OUTPUT INTO返回的数据会直接被输入到表或表变量中
可以用来对”被输入”和”被删除”的数据进行被更改前后的数据值
用途: 队列操作, 掌握中间结果集
九．TOP 增强功能
指定只有第一组结果行将会从查询结果中返回
在 SQL Server2000
SELECT TOP <const>
只可用在SELECT语句中
在SQL Server2005
SELECT [TOP (<表达式>) [PERCENT] [WITH TIES]]
<表达式> 可以是变量, 子查询(SUB-QUERY)或常量
还可用在INSERT, UPDATE, DELETE语句中
TOP … WITH TIES 只能用在SELECT语句中
指定只有第一组结果行将会从查询结果中返回
在 SQL Server2000
SELECT TOP <const>
只可用在SELECT语句中
在SQL Server2005
SELECT [TOP (<表达式>) [PERCENT] [WITH TIES]]
<表达式> 可以是变量, 子查询(SUB-QUERY)或常量
还可用在INSERT, UPDATE, DELETE语句中
TOP … WITH TIES 只能用在SELECT语句中
十．T-SQL和CLR的集成
SQL Server2000现有编程模型
TransacT-SQL(T-SQL)
扩展存储过程
(XP)
SQL Server2005新增加的
CLR集成编程模型
用任何CLR托管代码编写存储过程、触发器和函数
可创建自定义的数据类型和聚合函数
CLR是WINDOWS .NET架构C#,VB.NET等编程语言的运行环境
程序可被编译成托管代码并支持以下功能:
自动内存管理
通用数据类型系统
代码校验和读取安全
丰富的代码库
独立的应用域
支持反汇编
什么是SQLCLR?
用.NET编程语言编写以下类型数据库程序并在SQL SERVER中运行
用户自定义函数(UDF)
存储过程(SP)
计算和逻辑密集的代码
用户自定义数据类型(UDT)
触发器
SSIS 软件包
报表系统中的表达式和数据供应源
SQLCLR概述
注册和执行数据库中CLR托管代码
编写.NET程序,编译成托管代码,并生成程序集
程序集上载到SQL Server2005
用Create Assembly数据定义语言(DDL)将其存储到系统目录(部署)
创建T-SQL对象,并将其绑定到已经上载的程序集的入口点，
用Create Procedure/Function/Trigger/Type/Aggregate
应用程序可以象T-SQL例程一样的调用
构建和部署
VS.NET 2005提供的‘SQL Server项目’的代码模板
部署过程自动创建程序集中定义的例程、类型和聚合
调试
不受客户端到服务器连接类型的影响
跨语言无缝调试，例如从T-SQL过程进入CLR过程
注册和执行数据库中CLR托管代码
编写.NET程序,编译成托管代码,并生成程序集
程序集上载到SQL Server2005
用Create Assembly数据定义语言(DDL)将其存储到系统目录(部署)
创建T-SQL对象,并将其绑定到已经上载的程序集的入口点，
用Create Procedure/Function/Trigger/Type/Aggregate
应用程序可以象T-SQL例程一样的调用
构建和部署
VS.NET 2005提供的‘SQL Server项目’的代码模板
部署过程自动创建程序集中定义的例程、类型和聚合
调试
不受客户端到服务器连接类型的影响
跨语言无缝调试，例如从T-SQL过程进入CLR过程
