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

Presentation on theme: "SQL Server2005 中的十个最重要的T-SQL增强功能"— Presentation transcript:

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

2 讲座的总体内容: 关于 Microsoft® SQL Server TM 2005 Transact-SQL 数据库语言中的十个最重要的增强功能的总体介绍和概括 通过清晰的,简捷的景象分析,实例介绍和演示来详细的总结和解析每一个功能的特性和运用

3 参加讲座的前提条件 最好具备关于Microsoft Transact SQL 数据库语言的相关知识和相应的工作经验 Level 200

4 讲座议程 新型超大数据类型 Large-Value Data Types 先进的错误处理 Error Handling
通用表表达式 Common Table Expressions DDL 触发器 DDL Triggers 新的排序函数 Ranking Functions 新的关系运算符 PIVOT/UNPIVOT/APPLY 新的结果集运算符 EXCEPT and INTERSECT OUTPUT在DML中的特别运用 TOP 增强功能 T-SQL和CLR的集成

5 大容量存储数据类型 关于 SQL Server2000 SQL Server2005 提供的 MAX 定义符
任何超过8KB的大容量数据(文档,图片,音像)必须 要存储在 text, ntext, 和 image 数据类型中 任何超过8KB数据面临非常有限的操作功能 SQL Server2005 提供的 MAX 定义符 增强并且延伸了varchar, nvarchar 和 varbinary 传统数据类型的存储能力 varchar(max), nvarchar(max), 和 varbinary(max) 从此成为标准T_SQL的大容量存储数据类型 最多可存储到场2GB的大容量数据 New system defined data types Also capability to define user defined data types using CLR – separate session.

6 大容量存储数据类型 – (继续) 关于大或小容量数据类型的统一的编程模式 当小容量数据增长并且超越8K极限时,整个过度过程十分平滑和简捷
对比 Comparisons 连接 Concatenation 变量 Variables 参数 Parameters 触发器 Triggers 集合 Aggregates 排序和索引 Index Included Columns 当小容量数据增长并且超越8K极限时,整个过度过程十分平滑和简捷 通过 对于小于8K的数据, 相比text, ntext 和 image存取效率明显提高 建议取代对text, ntext 和 image的应用 Similar in behavior to the smaller counterparts Text pointer support will be removed in a future version of SQL Server. We recommend converting your text, ntext and image columns to the new large-value data types.

7 大容量存储数据类型 实例演示 CREATE TABLE MyPublications (PublicationID int,
Abstract nvarchar(max), Publication varbinary(max)); UPDATE MyPublications SET Abstract .WRITE (N‘author', 20, 6) WHERE PublicationID = 1; Use to update the word with ‘author’ in the Abstract column New system defined data types Also capability to define user defined data types using CLR – separate session.

8 关于错误处理的提高 在 SQL Server2000 在SQL Server2005中的丰富的异常处理框架
返回最后一个执行的T-SQL语句的错误代码 的值会随着每一个T-SQL语句而被更新 在SQL Server2005中的丰富的异常处理框架 TRY…CATCH 配置 提供捕获所有SQL SERVER异常或错误的功能 可以捕获和处理过去会导致批处理终止的错误,从而阻止批处理的中断 提供处理和登录异常或错误的功能 当错误发生时,阻止T-SQL交易环境的丢失 可以对错误的具体内容进行读取

9 TRY…CATCH 语法和定义 任何在TRY模块中产生的错误会将控制的流程转移到CATCH模块中
BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH END CATCH [ ; ] 任何在TRY模块中产生的错误会将控制的流程转移到CATCH模块中 TRY…CATCH 配置是可以被包含和兼容的

10 TRY…CATCH – (继续) 可处理所有付值给@@ERROR的T-SQL运行过程中的错误 不处理以下的情况:
任何严重性在0-10范围的警告和报告性的信息 任何严重性在20-25范围的中断数据库连接的错误 注意事项 KILL语句 RAISERROR 可以用来自行生成错误 控制流程会转移到最接近的CATCH模块中

11 错误信息的各类函数 错误信息可通过运用以下函数而在CATCH模块中被获取 交易信息 ERROR_NUMBER()
ERROR_SEVERITY() ERROR_STATE() ERROR_LINE() ERROR_PROCEDURE() ERROR_MESSAGE() 交易信息 任何交易中断的错误都会最终导致成一个未成功交易 XACT_STATE() 总是返回任何交易的状态(1, 0, -1) 1 = Active Committable Transaction 0 = No Active Transactions -1 = Active Uncommittable Transaction

12 错误处理 实例演示 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; ErrNumber ErrSeverity ErrState ErrProc ErrLine ErrMessage 8134 16 1 NULL 3 Divide by zero error encountered.

13 错误处理 实例演示 – (继续) 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; 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;

14 通用表表达式样 (CTE) 通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集; 可视为类似于视图和派生表混合功能的改进版本
它可以被定义在任何一个SELECT, INSERT, UPDATE, DELETE, 或 CREATE VIEW的T-SQL语句中 它可以被自己引用并在查询中多次被引用 用途: 递归查询 替代那些不需要存储在元数据中的视图 聚合派生表生成的表列 可以在同一个T-SQL语句中多次引用结果集 A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

15 通用表表达式样 (CTE) 语法和定义 WITH <cte_alias>(<column_aliases>) AS ( <cte_query_definition> ) SELECT * FROM <cte_alias> WITH子句和SELECT/INSERT/DELETE/UPDATE结合在一起形成一个单独的T-SQL语句 在一个单独的T-SQL语句中, 可以有多个CTE被定义在一个单独的WITH子句里

16 通用表表达式样 (CTE) 实例演示 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;

17 通用表表达式样 (CTE)和递归查询 递归查询会直到递归成员不在返回行时才会结止 任何一个引用它自己的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> A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. 递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。

18 通用表表达式样 (CTE)和递归查询 实例演示
-- 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

19 DDL 触发器 允许为所有发生的T-SQL的数据定义语言(DDL)事件定义触发器 DDL触发器可以被定义在:
CREATE_TABLE, ALTER_PROCEDURE, DROP_LOGIN, etc 一组语句: DDL_DATABASE_LEVEL_EVENTS, DDL_DATABASE_SECURITY_EVENTS, etc DDL触发器可被规划在数据库和服务器的范围内 EventData() 函数 在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。

20 DDL 触发器 实例演示 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; ;

21 新排序函数 返回和提供任何一个结果行在分区里的排序值 用途 ROW_NUMBER RANK DENSE_RANK NTILE
给结果行制定连续的整数排序值 数据分析和采集: Paging, Scoring, Histograms ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. NTIILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. Ties work differently in different functions ROW_NUMBER – always unique RANK – ties produce space and duplicates in ranking DENSE_RANK – duplicates but not ties NTILE – divided into n approximately equal tiles

22 新排序函数 ROW_NUMBER RANK DENSE_RANK NTILE
对于在一个结果集的一个分区里的一个结果行,返回一个连续的整数排序值 RANK 对于在一个结果集的一个分区里的结果行,返回行的等级 每一个结果行的等级取决于当前行与先前结果行之间就排序列值的比较结果;含相同排序列值的结果行会计算在当前行的等级值里 DENSE_RANK 对于在一个结果集的一个分区里的结果行,返回不间断的行的等级 每一个结果行的等级取决于当前行与先前结果行之间就排序列值的比较结果;含相同排序列值的结果行不会计算在当前行的等级值里 NTILE 将在一个排序分区里的结果行分布成特定的小组 对于每一个结果行,NTILE会返回这个结果行所属的小组的号码 ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. NTIILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. Ties work differently in different functions ROW_NUMBER – always unique RANK – ties produce space and duplicates in ranking DENSE_RANK – duplicates but not ties NTILE – divided into n approximately equal tiles

23 新排序函数 实例演示 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 The TVF acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

24 新排序函数 实例演示: 结果 1 1 1 1 1 Leverling Janet Kirkland
Row_Number Rank Part_Rank Dense_Rank NTile_4 LastName City FirstName Leverling Janet Kirkland Buchanan Steven London Dodsworth Anne London King Robert London Suyama Michael London Peacock Margaret Redmond Callahan Laura Seattle Davolio Nancy Seattle Fuller Andrew Tacoma The TVF acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

25 新的关系运算符 PIVOT and UNPIVOT
可将一个table-valued的表达式转换成一个表 可被定义在FROM子句里 PIVOT 将行旋转为列 可能同时执行聚合 用途: 处理 open-schema 景象 生成跨越表格的报表以便更好的统计数据 UNPIVOT 与PIVOT相反的操作; 将列旋转为行 更多的用来转换已经经过PIVOT处理的数据 Perform some manipulation on an input table-valued expression and produce an output table as a result PIVOT provides syntax that is simpler and more readable than what may otherwise be specified in a complex series of SELECT...CASE statements.

26 PIVOT 实例演示 Make Year Sales Honda 1990 2000 1000 Acura 500 1991 3000
600 1992 800 SELECT * FROM CarSales PIVOT ( SUM(Sales) FOR Year IN ([1990], [1991]) ) AS PVT Perform some manipulation on an input table-valued expression and produce an output table as a result PIVOT provides syntax that is simpler and more readable than what may otherwise be specified in a complex series of SELECT...CASE statements. Make 1990 1991 Honda 3000 Acura 500 900

27 UNPIVOT 实例演示 Make Year Sales Acura 1990 500 1991 900 Honda 3000
SELECT Make, Year, Sales FROM CarSalesPivot UNPIVOT ( Sales FOR Year IN ([1990], [1991]) ) AS UNPVT Perform some manipulation on an input table-valued expression and produce an output table as a result PIVOT provides syntax that is simpler and more readable than what may otherwise be specified in a complex series of SELECT...CASE statements. Make 1990 1991 Honda 3000 Acura 500 900

28 新的关系运算符 APPLY APPLY关系运算符允许您对外部表的每个行调用指定的表值函数(TVF)一次 可被定义在FROM子句里
CROSS APPLY 对于从表值函数中生成结果集的外部表, CROSS APPLY从中返回相应的行 和内部链接(INNER JOIN)效果相同 OUTER APPLY OUTER APPLY从外部表中返回所有的行 用NULL替代空结果集里的表值函数列 和外部链接(left outer join)效果相同 The TVF acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

29 APPLY 实例和演示 -- Returns nothing or a table with a single row
CREATE FUNCTION fn_greater AS AS FLOAT) RETURNS TABLE AS RETURN AS Val GO -- Returns rows in CarSales where Sales > 1000 SELECT * FROM CarSales CROSS APPLY fn_greater (Sales, 1000); -- Returns all rows in CarSales OUTER APPLY fn_greater(Sales, 1000); The TVF acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

30 新的结果集运算符 EXCEPT and INTERSECT
从另外两个结果集产生出一个新的结果集 EXCEPT 返回所有属于第一个结果集但不属于第二个结果集的行 INTERSECT返回所有共同属于两个结果集的行 -- Get authors without books SELECT au_id FROM authors EXCEPT SELECT au_id FROM titleauthor -- Get authors with books SELECT au_id FROM authors INTERSECT SELECT au_id FROM titleauthor

31 DML 与 OUTPUT 新的OUTPUT子句可以从INSERT/UPDATE/DELETE T-SQL语句中返回数据
从OUTPUT INTO返回的数据会直接被输入到表或表变量中 可以用来对”被输入”和”被删除”的数据进行被更改前后的数据值 用途: 队列操作, 掌握中间结果集 OUTPUT <dml_select_list> OUTPUT <dml _select_list> INTO | output_table> DELETE Sales.ShoppingCartItem OUTPUT DELETED.*;

32 TOP增强功能 指定只有第一组结果行将会从查询结果中返回 在 SQL Server2000 在SQL Server2005
SELECT TOP <const> 只可用在SELECT语句中 在SQL Server2005 SELECT [TOP (<表达式>) [PERCENT] [WITH TIES]] <表达式> 可以是变量, 子查询(SUB-QUERY)或常量 还可用在INSERT, UPDATE, DELETE语句中 TOP … WITH TIES 只能用在SELECT语句中

33 TOP增强功能 实例演示 -- Use TOP with variable to return the n most recent invoices AS INT; = 2; SELECT TOP * FROM Invoices ORDER BY InvoiceDate DESC; -- Use TOP with subquery to calculate the average number of -- monthly invoices and return that many most recent invoices SELECT TOP (SELECT COUNT (*) / DATEDIFF (month, MIN(InvoiceDate), MAX(InvoiceDate)) FROM Invoices) * The TVF acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

34 T-SQL和CLR集成 SQL Server2000现有编程模型 SQL Server2005新增加的CLR集成编程模型
TransacT-SQL(T-SQL) 扩展存储过程(XP) SQL Server2005新增加的CLR集成编程模型 用任何CLR托管代码编写存储过程、触发器和函数 可创建自定义的数据类型和聚合函数

35 CLR基本概念 CLR是WINDOWS .NET架构C#,VB.NET等编程语言的运行环境 程序可被编译成托管代码并支持以下功能:
自动内存管理 通用数据类型系统 代码校验和读取安全 丰富的代码库 独立的应用域 支持反汇编

36 什么是SQLCLR? 用.NET编程语言编写以下类型数据库程序并在SQL SERVER中运行 计算和逻辑密集的代码 用户自定义函数(UDF)
存储过程(SP) 计算和逻辑密集的代码 用户自定义数据类型(UDT) 触发器 SSIS 软件包 报表系统中的表达式和数据供应源

37 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过程

38 Assembly: “TaxLib.dll”
SQLCLR概述 Build VB, C#, … Assembly: “TaxLib.dll” VS 2005 Project deploy SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … Runtime hosted inside SQL SQL Server SQL Queries: select sum(tax(sal,state) ) from Emp where county = ‘King’ This picture summarizes my talk in one slide. A SQL Server developer will write business logic using any .NET FRAMEWORK LANGUAGE. The build process will generate an ASSEMBLY. An assembly represents the unit of deployment, versioning, code security, and self-describing metadata in the .NET FRAMEWORK. Once an assembly has been created, in this case the developer created a library containing some tax functions called “TaxLib.dll” The server hosts the .NET common language runtime inside the SQL Server process. SQL DDL statements enable the registration of the assembly and all selected entry points of that assembly. Eventually these functions, procedures, triggers an types will be used by other SQL commands.

39 SQLCLR用户自定义函数(UDFs) 支持常用和表值函数 遵循和T-SQL相同的规则,没有付作用 比T-SQL更加灵活
运用.NET的基本数据结构 运用用户自定义的.NET的数据结构 可以运行虚拟T-SQL 运行效率会同于或好于T-SQL

40 SQLCLR存储过程 可以完成任何T-SQL存储过程能做到的 可以共享其它SQLCLR模块的功能: 最适用于: 多结果集 输出参数 信息
返回代码 可以共享其它SQLCLR模块的功能: C# or VB.net 代码 最适用于: 外部数据源 多个从外部数据源过来的输出参数 复杂的数学和逻辑计算

41 SQLCLR用户定义数据类型(UDT) 通过.Net CLR在VB.Net或C#中定义 将应用程序的逻辑和数据嵌入的数据库中 好处:
灵活的编程能力 保持长期的一贯性

42 建立CLR UDT的总体步鄹 通过.Net language (VB or C#)建立 Class
通过CREATE ASSEMBLY建立数据库模块 通过CREATE TYPE 数据定义语言(DDL)定义数据类型 CREATE Assembly myAssembly FROM ‘\\server\share\myAssembly.dll Go CREATE TYPE myType EXTERNAL NAME myAssembly.[myAssembly.myType] go

43 相关资源 http://www.microsoft.com/sql
Transact-SQL Enhancements in SQL Server 2005 SQL Server 2005 Books Online SQL Server 2005 Developer Documentation SQL Server Developer Center Programmability Resources

44 问题解答?

45