DAT328 SQLCLR 与 T-SQL:数据库开发最佳实践 2019年7月17日11时52分 DAT328 SQLCLR 与 T-SQL:数据库开发最佳实践 孙巍 技术总监 北京中达金桥技术服务有限公司 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

基本概念 SQL:关系型数据库的查询语言 T-SQL:Microsoft SQL Server在SQL上的扩展程序语言 XP_SP:复杂过程的代码 通常使用C++开发 sp_OA:OLE自动过程 通过T-SQL调用COM对象 SQLCLR:SQL Server使用Microsoft.NET

XPs 和 OLE Automation XP_SP使用ODS OLE automation 托管代码 最初是为了满足软件集成商的需求 今后将不再推荐 OLE automation 使用XP_SP做为包装 迁移到SQLCLR 今后我们可以使用SQL\T-SQL\SQLCLR

使用SQL 对于集合操作SQL是最佳选择 最快速的集合查询 在任何语言中游标访问速度都不快 通过统计信息优化 快速执行 硬编码的执行计划 额外的存储开销 而外的数据获取开销

选择什么语言 解释型的T-SQL 变异型的SQLCLR 对于大量的程序代码没有做优化 字符串处理没有优化 对于数据中心应用程序有很好的函数库 处理代码可以有数量级的性能提升 对于过程代码- 代码越多，可以获得更快的性能 .NET BCL are “built-ins”

过程示例 假设你想编写 正则表达式 XSLT SQLCLR是最佳选择 SQL查询优化器没有帮助 处理速度有数量级的提升

2019年7月17日11时52分 正则表达式 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

CLR和UDFs 对于UDFs首选CLR 因为T-SQL所以UDF性能慢 UDF通常是用于计算 如果没有数据，没有数据访问开销 2019年7月17日11时52分 CLR和UDFs 对于UDFs首选CLR UDF通常是用于计算 如果没有数据，没有数据访问开销 DataAccessKind SystemDataAccessKind 因为T-SQL所以UDF性能慢 因为解释型所以慢 SQL Server 2005中的T-SQL UDF比SQL Server 2000中的快 但是还是比SQLCLR UDF慢 DataAccess=DataAccessKind.None (不需要数据访问功能) DataAccess=DataAccessKind.Read SystemDataAccess=SystemDataAccessKind.None(不需要访问系统数据) SystemDataAccess=SystemDataAccessKind.Read © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

表值函数 SQLCLR可以对产生的结果数据进行流式处理 Better for multi-statement TVFs 2019年7月17日11时52分 表值函数 SQLCLR可以对产生的结果数据进行流式处理 Better for multi-statement TVFs T-SQL表值函数将临时结果存储在工作表中 容易创建 IEnumerator or IEnumerable © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

2019年7月17日11时52分 表值函数 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

外部资源 对于访问外部资源SQLCLR是最佳选择 外部资源包括 对于非托管存储过程，SQLCLR是首选 文件系统 事件日志 Web Service客户端 注册表

2019年7月17日11时52分 访问外部资源 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

T-SQL和数据访问 T-SQL调用SQL更加有效 SQLCLR添加额外的层次 和SQL共享缓存 本机代码到托管代码的转换开销 使用SqlContext封装数据访问

参数指向 T-SQL支持 输入参数 OUT参数 SQLCLR支持 ByVal=T-SQL输入参数 ByRef=T-SQL OUT参数

SQLCLR和数据类型 本地.NET类型-没有数据库中的null .NET无法表现的数据类型 Int,string等等，除了null Use “RETURNS NULL on NULL INPUT” UDF only 使用System.Data.SqlTypes 注意SQL DECIMAL vs. .NET Decimal .NET无法表现的数据类型 Varchar TIMESTAMP

2019年7月17日11时52分 SqlTypes © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

SQLCLR 结果的传递途径 SQL在完成后才能显示SQL结果 非SQL不是这种工作方式 SQLCLR SqlPipe已经优化过 直到查询完成才能显示第一行结果 非SQL不是这种工作方式 立即构造结果集 传递结果 SQLCLR SqlPipe已经优化过 SendResultsStart SendResultRow SendResultEnd 对于SQL查询没有优化作用

SQLCLR 数据访问 SQLCLR使用ADO.NET 通过SqlClient共享类库 服务器特殊的类 Microsoft.SqlServer.Server SqlContext SqlPipe WindowsIdentity SqlTriggerContext

使客户端代码吗？ SQLCLR代码使用数据库资源 如果是数据库在本地 在服务器上 大量数据 远程客户端 使用数据库资源

规则 使用SQL 使用T-SQL 使用SQLCLR 所有基于数据集的操作 包装SQL操作 用户自定义函数 计算敏感型存储过程 访问SQL Server 外部资源 T-SQL没有提供的功能

资源 http://www.microsoft.com/china/msdn/library/data/sqlserver/sqlclrguidance.mspx

Question SQLCLR的如何向客户端返回结果集？ 在SQLCLR如何返回标值函数？ 那些操作不适合使用T-SQL实现，举3个例子？