使用 SQL Server 2005中的 CLR 集成（1）

　　簡介
　　
　　Microsoft 通過宿主 Microsoft NET Framework 公共語言運行庫 (CLR)SQL Server 顯著地增強了數據庫編程模型的功能它支持用任何 CLR 語言（特別是 Microsoft Visual C# NETMicrosoft Visual Basic NET 和 Microsoft Visual C++）編寫過程觸發器和函數同時它還提供了擴展數據庫系統中的類型系統和聚合函數的功能方法是允許應用程序創建它們自己的數據類型和聚合函數
　　
　　本白皮書從數據庫應用程序開發人員的角度描述了如何最有效地利用這項技術它還將 CLR 與 SQL Server 中支持的現有編程語言（即 TransacTSQL (TSQL) 和擴展存儲過程 (XP)）進行了比較本白皮書不打算提供這些功能的基本參考資料SQL Server Beta Books Online 對其進行了詳細介紹
　　
　　本白皮書的目標讀者包括數據庫應用程序開發人員架構師和數據庫管理員本白皮書假設讀者熟悉基於 NET Framework 的編程和數據庫編程
　　
　　CLR 集成概述
　　
　　下面簡要概述了 CLR 集成執行的 SQL Server 功能以及 Visual Studio 最終發布版本如何支持這些功能
　　
　　注冊和執行數據庫中的托管代碼包括以下步驟
　　
　　開發人員將托管程序編寫為一組類定義將 SQL Server 內旨在用作存儲過程函數或觸發器（下面統稱為例程）的代碼編寫為類的 static（或 Microsoft Visual Basic NET 中的 shared）方法將旨在用作用戶定義的類型和聚合的代碼編寫為一個整類開發人員編譯該程序並創建一個程序集
　　
　　然後將此程序集上載到 SQL Server 數據庫在其中使用 CREATE ASSEMBLY 數據定義語言 (DDL) 將它存儲到系統目錄
　　
　　接著創建 TransacTSQL 對象例如例程（函數過程和觸發器）類型和聚合並將其綁定到已經上載的程序集中的入口點（對例程來說是方法對類型和聚合來說是類）使用 CREATE PROCEDURE/FUNCTION/ TRIGGER/TYPE/AGGREGATE 語句來完成這一步
　　
　　在創建了這些例程之後應用程序就可以像使用 TSQL 例程一樣使用它們例如可以從 TSQL 查詢中調用 CLR 函數從客戶端應用程序或從 TSQL 批處理中調用 CLR 過程就好像它們是 TSQL 過程一樣
　　
　　Visual Studio Beta 支持在 SQL Server 中開發部署和調試托管代碼有一種新的項目類型（稱為 SQL Server 項目）它允許開發人員在 SQL Server 中開發部署和調試例程（函數過程和觸發器）類型和聚合
　　
　　構建和部署
　　
　　SQL Server 項目提供了代碼模板這使得開發人員能夠輕松地開始為基於 CLR 的數據庫例程類型和聚合編寫代碼該項目還允許添加對數據庫中其他的程序集的引用在構建項目時可以將其編譯成一個程序集部署此程序集可以將程序集的二進制文件上載到與該項目相關聯的 SQL Server 數據庫中部署操作還自動創建在數據庫的程序集中定義的例程類型和聚合方法是使用在代碼中定義的自定義屬性（SqlProcedureSqlFunction 和 SqlTrigger 等等）它還上載與該程序集相關聯的源代碼和 pdb 文件（調試符號）
　　
　　調試
　　
　　對於任何平台來說調試都是開發人員體驗的基本部分SQL Server 和 Visual Studio 為數據庫編程人員提供了這些功能調試 SQL Server 對象的關鍵部分在於其易於安裝和使用調試到運行 SQL Server 的計算機的連接在很大程度上同調試傳統操作系統中運行的進程的方式一樣調試器的功能不會受到客戶端到服務器的連接類型的影響這樣就可以調試表格數據流 (TDS) 和 HTTP 連接而且還可以跨語言進行無縫調試因此如果有一個調用 CLR 存儲過程的 TSQL 存儲過程調試會允許您從 TSQL 過程進入到 CLR 過程
　　
　　CLR 與 TransacTSQL
　　
　　現在我們進入本文的關鍵部分對 CLR 集成和 SQL Server 中支持的現有編程語言進行比較
　　
　　TransacTSQL (TSQL) 是 SQL Server 支持的本機編程語言和大多數 SQL Server 版本一樣它包含數據處理功能和數據定義功能數據處理功能一般可以分為兩類查詢語言（由 SELECT/INSERT/UPDATE/ DELETE 語句組成）和過程語言（WHILE賦值觸發器光標等）一般來說SQL Server 中的 CLR 支持為過程語言提供了 TSQL 的替代方法
　　
　　即使在 SQL Server 中引入 CLR 支持以前數據庫應用程序應該盡可能多地使用查詢語言這始終被認為是很重要的數據庫應用程序應該利用面向集的查詢處理器並且只在查詢語言無法表示邏輯時才轉向過程編程對於 SQL Server 中的 CLR 支持這仍然是正確的不應該使用 CLR 來編寫可以用簡單的 SELECT 語句表示的過程代碼在 SQL Server 中增加了許多重要的功能來提高TSQL 查詢語言的表達能力
　　
　　遞歸查詢遍歷表中的遞歸層次的能力
　　
　　分析函數RANK 和 ROW_NUMBER 允許排列結果集中的行
　　
　　新的關聯操作APPLYPIVOT 和 UNPIVOT
　　
　　試圖使用 CLR 功能的開發人員應該確保他們充分地利用查詢語言包括 SQL Server 中的擴展在內對於在查詢語言中無法以聲明方式表示的邏輯他們應該考慮將 CLR 作為有效的替代辦法
　　
　　現在讓我們看一些方案其中基於 CLR 的編程能夠補充 TSQL 查詢語言的表達能力通常需要在查詢（可稱為函數）內嵌入過程邏輯這允許許多方案例如
　　
　　根據數據庫表中存儲的值對每行進行復雜的計算（必須用過程邏輯來表示）這可以包括將這些計算的結果發送給客戶端或者使用計算來過濾發送給客戶端的行集如以下示例中所示
　　
　　SELECT <complexcalculation>(<columnname>)
　　FROM <table>
　　WHERE <complexcalculation>(<columnname>) =
　　
　　使用過程邏輯來評估表格結果然後在 SELECT 或 DML語句的 FROM 子句中進行查詢
　　
　　SQL Server 引入了支持這些方案的 TSQL 函數（標量和表值）有了 SQL Server 就可以用 CLR 語言更容易地編寫這樣的函數並且會極大地提高它們的性能之所以編寫這些函數非常容易是因為事實上編寫 CLR 代碼的開發人員可以利用 NET Framework API中存在的大量有用函數和類這個類/函數庫比 TransacTSQL 中支持的內置函數要豐富得多此外CLR 編程語言提供了 TSQL 中所沒有的豐富構造（例如數組和列表等）與 TSQL（它是一種解釋語言）相比CLR 編程語言之所以具有更好的性能是因為托管代碼是已編譯的對於涉及算術計算字符串處理條件邏輯等的操作托管代碼的性能可能要優於 TSQL 一個數量級
　　
　　注對於函數幾乎沒有必要從函數中訪問數據庫外部查詢已經從數據庫中檢索到數據值並且將其作為參數傳遞給函數這是 CLR 的優勢在計算性任務上比 TSQL 更勝一籌
　　
　　從 CLR 中訪問數據
　　
　　現在讓我們從編程模型和性能這兩個方面看看如何用 CLR 來編寫訪問數據庫的業務邏輯
　　
　　編程模型
　　
　　使用 TSQL只是在過程代碼內部嵌入查詢語言語句 (SELECT/INSERT/UPDATE/ DELETE)通過托管代碼可以使用 SQL Server 托管提供程序來實現 Microsoft ADONET 數據訪問 API（也稱為 inproc ADONET）使用這種方法可以將查詢語言語句（SELECT 和 DML 語句）作為動態字符串嵌入並且將其作為參數傳遞給 ADONET API與 TSQL 的靜態方法相比過程代碼內嵌入的 SQL 語句的基於動態 API 的特性是它們在編程模型上的主要區別不利的是inproc ADONET 模型會產生比 TSQL 更冗長的代碼此外因為 SQL 語句是動態字符串所以在執行之前不在語法或語義上進行編譯和驗證有利的是帶有 ADONET 的數據庫編程模型與客戶端或中間層中使用的模型相似因而更容易在各層之間移動代碼和利用現有的技術
　　
　　此外在基於 TSQL 和基於 CLR 的編程模型中使用的都是同一 SQL 查詢語言不同之處在於過程部分注意到這一點是非常重要的
　　
　　性能
　　
　　正如已經提到的在談及過程代碼計算等方面時與 TSQL 相比托管代碼在性能方面具有決定性的優勢然而對於數據訪問方面TSQL 在性能方面通常會更好因此通用規則是用 CLR 編寫計算和邏輯密集的代碼要比數據訪問密集的代碼好不過這值得更詳細地考慮
　　
　　讓我們看看數據訪問編程中的一些典型的基元和模式以及在這些情況下如何使用 ADONET 進行 TSQL 和托管編程
　　
　　將結果發送到客戶端
　　
　　這包括將一組行發送到客戶端而沒有在服務器中消費它們（即沒有在例程內導航行）使用 TSQL只需在 TSQL proc 中嵌入一個 SELECT 語句就可以將 SELECT 產生的行發送到客戶端通過托管代碼可以使用 SqlPipe 對象將結果發送到客戶端TSQL 和 inproc ADONET 平台在這種情況下的作用是一樣的
　　
　　提交 SQL 語句
　　
　　這包括來自過程代碼的 SQL 語句的執行往返在這種情況下TSQL 具有很大的優勢（比 inproc ADONET 快兩倍多）
　　
　　此處需要重點注意的是之所以在 CLR 中出現性能降低是因為增加了額外的代碼層包括將來自托管代碼的 TSQL 語句提交給原生 SQL 代碼在將該語句提交給查詢處理器之後基於語句源（TSQL 或 inproc ADONET）的性能方面就沒有什麼不同了
　　
　　注典型的數據訪問密集的存儲過程可能涉及提交一系列的 SQL 語句如果 SQL 語句簡單並且不需要花費大量的時間執行則來自托管代碼的調用開銷可能占用大部分
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22080.html