SQL Server2005 中的十个最重要的T-SQL增强功能

 
 
一.新型超大数据类型 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过程
 
 
阅读更多
个人分类: SQL SERVER
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭