MSSQL网页编程之利用 SQL Server 2005中的 CLR 集成

Archive非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差　　本文形貌了数据库使用程序开辟职员和架构师怎样使用SQLServer2005中的CLR集乐成能。本文对基于CLR的编程体例与SQLServer中撑持的现有编程模子（如TransacT-SQL和扩大存储历程）举行了对照，而且夸大了各自绝对的优弱点。还供应了一组选择符合的编程替换办法的初级引导，和一些示例和代码示例。
　　1、简介
　　Microsoft经由过程宿主Microsoft.NETFramework2.0大众言语运转库(CLR)，SQLServer2005明显地加强了数据库编程模子的功效。它撑持用任何CLR言语（出格是MicrosoftVisualC#.NET、MicrosoftVisualBasic.NET和MicrosoftVisualC++）编写历程、触发器和函数。同时，它还供应了扩大数据库体系中的范例体系和聚合函数的功效，办法是同意使用程序创立它们本人的数据范例和聚合函数。
　　本白皮书从数据库使用程序开辟职员的角度形貌了怎样最无效天时用这项手艺。它还将CLR与SQLServer中撑持的现有编程言语（即TransacT-SQL(T-SQL)和扩大存储历程(XP)）举行了对照。本白皮书不盘算供应这些功效的基础参考材料，SQLServer2005Beta2BooksOnline对其举行了具体先容。
　　本白皮书的方针读者包含数据库使用程序开辟职员、架构师和数据库办理员。本白皮书假定读者熟习基于.NETFramework的编程和数据库编程。
　　2、CLR集成概述
　　上面扼要概述了CLR集成实行的SQLServer功效，和VisualStudio2005终极公布版本怎样撑持这些功效。
　　注册和实行数据库中的托管代码包含以下步骤：
•　　开辟职员将托管程序编写为一组类界说。将SQLServer内旨在用作存储历程、函数或触发器（上面统称为例程）的代码编写为类的static（或MicrosoftVisualBasic.NET中的shared）办法。将旨在用感化户界说的范例和聚合的代码编写为一个整类。开辟职员编译该程序并创立一个程序集。
•　　然后，将此程序集上载到SQLServer数据库，在个中利用CREATEASSEMBLY数据界说言语(DDL)将它存储到体系目次。
•　　接着，创立TransacT-SQL工具，比方，例程（函数、历程和触发器）、范例和聚合，并将其绑定到已上载的程序会合的出口点（对例程来讲是办法，对范例和聚合来讲是类）。利用CREATEPROCEDURE/FUNCTION/TRIGGER/TYPE/AGGREGATE语句来完成这一步。
•　　在创立了这些例程以后，使用程序就能够像利用T-SQL例程一样利用它们。比方，能够从T-SQL查询中挪用CLR函数，从客户端使用程序或从T-SQL批处置中挪用CLR历程，就仿佛它们是T-SQL历程一样。
　　VisualStudio2005Beta1撑持在SQLServer2005中开辟、部署和调试托管代码。有一种新的项目范例（称为SQLServer项目），它同意开辟职员在SQLServer中开辟、部署和调试例程（函数、历程和触发器）、范例和聚合。
　　构建和部署
　　SQLServer项目供应了代码模板，这使得开辟职员可以轻松地入手下手为基于CLR的数据库例程、范例和聚合编写代码。该项目还同意增加对数据库中其他的程序集的援用。在构建项目时，能够将其编译成一个程序集。部署此程序集能够将程序集的二进制文件上载到与该项目相干联的SQLServer数据库中。部署操纵还主动创立在数据库的程序会合界说的例程、范例和聚合，办法是利用在代码中界说的自界说属性（SqlProcedure、SqlFunction和SqlTrigger等等）。它还上载与该程序集相干联的源代码和.pdb文件（调试标记）。
　　调试
　　关于任何平台来讲，调试都是开辟职员体验的基础部分。SQLServer2005和VisualStudio2005为数据库编程职员供应了这些功效。调试SQLServer2005工具的关头部分在于其易于安装和利用。调试到运转SQLServer的盘算机的毗连在很年夜水平上同调试传统操纵体系中运转的历程的体例一样。调试器的功效不会遭到客户端到服务器的毗连范例的影响。如许就能够调试表格数据流(TDS)和HTTP毗连。并且，还能够跨言语举行无缝调试。因而，假如有一个挪用CLR存储历程的T-SQL存储历程，调试会同意您从T-SQL历程进进到CLR历程。
　　3、CLR与TransacT-SQL
　　如今我们进进本文的关头部分：对CLR集成和SQLServer中撑持的现有编程言语举行对照。
　　TransacT-SQL(T-SQL)是SQLServer撑持的本机编程言语。和年夜多半SQLServer版本一样，它包括数据处置功效和数据界说功效。数据处置功效一样平常能够分为两类：查询言语（由SELECT/INSERT/UPDATE/DELETE语句构成）和历程言语（WHILE、赋值、触发器、光标等）。一样平常来讲，SQLServer中的CLR撑持为历程言语供应了T-SQL的替换办法。
　　即便在SQLServer中引进CLR撑持之前，数据库使用程序应当尽量多地利用查询言语，这一直被以为是很主要的。数据库使用程序应当使用面向集的查询处置器，而且只在查询言语没法暗示逻辑时才转向历程编程。关于SQLServer中的CLR撑持，这仍旧是准确的。不该该利用CLR来编写能够用复杂的SELECT语句暗示的历程代码。在SQLServer2005中增添了很多主要的功效来进步T-SQL查询言语的表达才能。
•　　递回查询：遍历表中的递回条理的才能
•　　剖析函数：RANK和ROW_NUMBER同意分列了局会合的行
•　　新的联系关系操纵：APPLY、PIVOT和UNPIVOT
　　试图利用CLR功效的开辟职员应当确保他们充实天时用查询言语，包含SQLServer2005中的扩大在内。关于在查询言语中没法以声明体例暗示的逻辑，他们应当思索将CLR作为无效的替换举措。
　　如今让我们看一些计划，个中基于CLR的编程可以增补T-SQL查询言语的表达才能。一般，必要在查询（可称为函数）内嵌进历程逻辑。这同意很多计划，比方：
•　　依据数据库表中存储的值，对每行举行庞大的盘算（必需用历程逻辑来暗示）。这能够包含将这些盘算的了局发送给客户端，大概利用盘算来过滤发送给客户真个行集，如以下示例中所示：
SELECT<complex-calculation>(<column-name>,...)
FROM<table>
WHERE<complex-calculation>(<column-name>,...)=...&bull;　　&bull;利用历程逻辑来评价表格了局，然后在SELECT或DML语句的FROM子句中举行查询。
　　SQLServer2000引进了撑持这些计划的T-SQL函数（标量和表值）。有了SQLServer2005，就能够用CLR言语更简单地编写如许的函数，而且会极年夜地进步它们的功能。之以是编写这些函数十分简单，是由于现实上编写CLR代码的开辟职员能够使用.NETFrameworkAPI中存在的大批有效函数和类。这个类/函数库比TransacT-SQL中撑持的内置函数要丰厚很多。别的，CLR编程言语供应了T-SQL中所没有的丰厚机关（比方数组和列表等）。与T-SQL（它是一种注释言语）比拟，CLR编程言语之以是具有更好的功能，是由于托管代码是已编译的。关于触及算术盘算、字符串处置、前提逻辑等的操纵，托管代码的功能大概要优于T-SQL一个数目级。
　　注：关于函数，几近没有需要从函数中会见数据库。内部查询已从数据库中检索到数据值，而且将其作为参数传送给函数。这是CLR的上风，在盘算性义务上比T-SQL更胜一筹。
　　从CLR中会见数据
　　如今让我们从编程模子和功能这两个方面，看看怎样用CLR来编写会见数据库的营业逻辑。
　　编程模子
　　利用T-SQL，只是在历程代码外部嵌进查询言语语句(SELECT/INSERT/UPDATE/DELETE)。经由过程托管代码，可使用SQLServer托管供应程序来完成MicrosoftADO.NET数据会见API（也称为in-procADO.NET）。利用这类办法，能够将查询言语语句（SELECT和DML语句）作为静态字符串嵌进，而且将其作为参数传送给ADO.NETAPI。与T-SQL的静态办法比拟，历程代码内嵌进的SQL语句的基于静态API的特征是它们在编程模子上的次要区分。倒霉的是，in-procADO.NET模子会发生比T-SQL更冗杂的代码。别的，由于SQL语句是静态字符串，以是在实行之前不在语法或语义长进行编译和考证。有益的是，带有ADO.NET的数据库编程模子与客户端或两头层中利用的模子类似，因此更简单在各层之间挪动代码和使用现有的手艺。
　　别的，在基于T-SQL和基于CLR的编程模子中利用的都是统一SQL查询言语，分歧的地方在于历程部分，注重到这一点长短常主要的。
　　功能
　　正如已提到的，在谈及历程代码、盘算等方面时，与T-SQL比拟，托管代码在功能方面具有决意性的上风。但是，关于数据会见方面，T-SQL在功能方面一般会更好。因而，通用划定规矩是用CLR编写盘算和逻辑麋集的代码要比数据会见麋集的代码好。不外这值得更具体地思索。
　　让我们看看数据会见编程中的一些典范的基元和形式，和在这些情形下怎样利用ADO.NET举行T-SQL和托管编程。
　　将了局发送到客户端
　　这包含将一组行发送到客户端，而没有在服务器中“消耗”它们（即没有在例程内导飞行）。利用T-SQL，只需在T-SQLproc中嵌进一个SELECT语句就能够将SELECT发生的行发送到客户端。经由过程托管代码，可使用SqlPipe工具将了局发送到客户端。T-SQL和in-procADO.NET平台在这类情形下的感化是一样的。
　　提交SQL语句
　　这包含来自历程代码的SQL语句的实行往复。在这类情形下，T-SQL具有很年夜的上风（比in-procADO.NET快两倍多）。
　　此处必要重点注重的是，之以是在CLR中呈现功能下降，是由于增添了分外的代码层，包含未来自托管代码的T-SQL语句提交给原生SQL代码。在将该语句提交给查询处置器以后，基于语句源（T-SQL或in-procADO.NET）的功能方面就没有甚么分歧了。
　　注：典范的数据会见麋集的存储历程大概触及提交一系列的SQL语句。假如SQL语句复杂，而且不必要消费大批的工夫实行，则来自托管代码的挪用开支大概占用年夜部分实行工夫，如许的历程用T-SQL编写将实行得更好。
　　只进、只读行导航
　　这包含以只进、只读体例一次导航一个由SQL语句发生的行。在T-SQL中，这是经由过程只进、只读光标完成的。在CLR中，这是经由过程SqlDataReader完成的。一般，每条语句都触及一些处置。假如疏忽了与每行相干联的处置，则导飞行在CLR中就比在T-SQL光标中稍慢。但是，假如您体贴为每行实行的处置，则CLR会更有上风，由于CLR在这类处置上比T-SQL做得好。
　　带有更新的行导航
　　假如必要依据光标中确当前地位更新行，则没有相干的功能对照，由于in-procADO.NET不撑持此功效，而应当经由过程T-SQL可更新光标来举行此操纵。
　　注在任何大概的情形下，最好利用UPDATE语句来批量更新行，只要在如许的修正没法用单一的UPDATE语句举行暗示时，才应利用基于光标导航的UPDATE。
　　以下示例申明在特定情形下怎样断定T-SQL和CLR将实行：
　　1.
　　思索如许一个历程，它实行一系列（或在最复杂的情形下仅一个）INSERT/UPDATE/DELETE/SELECT语句，带有几个大概不带前往到客户真个行，而且不导航SELECT发生的行。假如将如许的历程编写成T-SQL历程大概实行得更好。
　　2.
　　思索如许一个历程，它实行单一的SELECT语句，而且利用存储历程内的行，办法是，一次导航一行并举行一些不触及对每行举行更多的数据会见的处置。这个历程大概在带有in-procADO.NET的CLR中实行得更好，出格是假如每行都有一些大批处置的开支时（由于如许的操纵在CLR中比在T-SQL中加倍高效）。
　　上面是一些复杂的引导准绳，能够用来在CLR和T-SQL之间举行选择：
&bull;　　尽量利用带有T-SQLSELECT、INSERT、UPDATE和DELETE语句的基于集的处置。只要在没法利用基于集的DML语句之一暗示逻辑时，才应当利用历程和基于行的处置。
&bull;　　假如历程仅仅是一个经由过程封装基础INSERT/UPDATE/DELETE/SELECT操纵会见基表的包装，则应当用T-SQL举行编写。
&bull;　　假如历程次要包含了局会合的只进、只读行导航，和一些触及每行的处置，则用CLR编写大概更无效。
&bull;　　假如历程包含大批的数据会见和盘算和逻辑，则能够思索将历程代码分开为CLR来挪用T-SQL历程，以举行年夜部分的数据会见（反之亦然）。另外一个替换办法是，利用单一的T-SQL批处置，它包含从托管代码实行一次的一组查询，以削减从托管代码提交T-SQL语句的往复次数。
　　前面的部分将更深切地会商在处置了局集时什么时候及怎样得当地利用T-SQL和CLR。
　　CLR与XP
　　在SQLServer之前的版本中，扩大存储历程(XP)为数据库程序开辟职员供应了独一可用的机制来编写服务器端逻辑，这要末难于暗示，要末不成能用T-SQL编写。CLR集成供应了一种更强健的替换办法来编写这类存储历程。别的，利用CLR集成，已往以存储历程情势编写的逻辑一般能够更好地暗示为表值函数，由于它们同意它们同意将该函数机关的了局放在SELECT语句中举行查询（经由过程将这些了局嵌进到FROM子句中）。
　　以下是利用CLR历程或函数与XP比拟的上风：
&bull;　　粒度把持：很少能够把持XP能做甚么大概不克不及做甚么。利用代码会见平安模子，SQLServer办理员能够分派三种权限之一：SAFE、EXTERNAL_ACCESS或UNSAFE，从而对托管代码同意举行的操纵集举行分歧程序的把持。
&bull;　　牢靠性：托管代码（出格是在SAFE和EXTERNAL_ACCESS权限会合）供应了比XP更平安、更牢靠的编程模子。可考证的托管代码确保了一切对工具的会见都是经由过程强范例化的接话柄现的，从而下降了程序会见或损坏属于SQLServer的内存缓冲的大概性。
&bull;　　数据会见：利用XP

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

总感觉自己还是不会SQL

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关