CLR vs T-SQL vs stored procedure

本來複習做過的case是應該當天完成的任務，不過最近忙得夠嗆。。。當case量開始上去的現在，看來要好好學習下如何分清任務的先後主次。

SQL Server 2005（包括08）引入了CLR編程模型，使用普通程式設計語言（C#, VB.NET, C++）來寫SQL函數觸發器等。
CLR介紹：
1. 在資料庫中註冊和運行Managed 程式碼：
編寫assembly，使用CREATE ASSEMBLY來放到資料庫中。最終CLR函數能被T-SQL查詢調用，CLR過程也能被用戶端調用。
2. 使用VS來Building, Deploying, and Debugging
有特定模板來建立項目。

到底選擇CLR，T-SQL還是stored procedure呢？

A. CLR vs. Transact-SQL
T-SQL包含了資料操作和資料定義特徵。資料操作包括說明式查詢語言（SELECT/INSERT/UPDATE/DELETE）和過程語言（WHILE, assignment, triggers, cursors等等）。CLR提供了過程語言的替代性方案。
能用說明式查詢語言就要盡量用，因為容易最佳化+能大大量操作。只有要表達的邏輯不能用說明式查詢語言來完成了才用CLR。（T-SQL還有很多新增特性，例如遞迴查詢，RANK和ROW_NUMBER等分析性函數，EXCEPT, INTERSECT, APPLY, PIVOT和UNPIVOT 等關係型操作。這些都最好用T-SQL來執行)

那麼CLR可以在什麼時候用呢？
1. 對每行的資料進行複雜計算，如下範例：
SELECT <complex-calculation>(<column-name>,...)
FROM <table>
WHERE <complex-calculation>(<column-name>,...) = ...
舉一個實際的例子。上周有一個需求要對我們Newgroup所有的case進行統計，其中Newsgroup存在資料庫中的中文標題都被用Base64編碼了，需要在取出來後進行解碼。這個時候雖然也可能可以用T-SQL寫，不過因為有現成的解碼assembly，因此直接引用assembly了。SSIS2005中script還一定要用VB來寫，最後用Renjie Yu推薦的Reflector解決了。
2. 對錶格式資料操作（順便說一句，SQL Server 2008中也允許對傳遞表格了，所以這一條還是要看具體情況了）

CLR資料訪問
CLR通過Sqlclient來訪問資料，因此代碼相對冗長，但最終查詢所使用的查詢語句和T-SQL都是相同的。
就大多數的過程性的計算而言，CLR占決定性優勢，但只有資料訪問T-SQL佔優。

B. CLR vs. XPs
CLR相比比預存程序的優點如下：
1. 粒狀控制
這個名字聽起來怪怪的。解釋一下的話這裡是指安全性控制。CAS在這裡得到應用。
2. 資料訪問
XPs需要新開個串連。而CLR可以更有效地利用現有串連。
3. 特別的資料類型支援
這裡指xml，nvarchar(max)之類，我猜現在的預存程序應該支援了吧
4. Scalability
這個詞語我一直不知道怎麼樣才能最確切的翻譯。可測量性？可擴充性？
託管的API使能夠控制記憶體線程同步等。不過08新特性之Resouce Governor也可以做到了。

總而言之，資料訪問和發送結果集，CLR表現佔優；除此之外，Managed 程式碼因為多了一步轉換自然效能相對差。

中介層編程
某些邏輯可以放到資料庫而非程式中解決。常用的有以下兩個：
資料驗證
減少網路傳輸

下面我們來看個範例：
生產計劃
該任務包括對未來需求的預測，有以下幾個特點：
1. 有大量的資料輸入
2. 結果集很小
3. 需要相當多的計算

把演算法代碼放到中介層會導致大量的需求資料的傳輸，pass。
因為可能包含複雜資料類型和複雜的計算，所以預存程序pass。
所以這個時候就需要用CLR和T-SQL結合了。

常用應用執行個體：
1. 使用.NET framework進行資料驗證
這裡需要用到System.Text.RegularExpressions命名空間的RegEx類（Regular expression），這個比在T-SQL中一個個寫like操作更有效。
2. 產生結果集
從資料庫物件（預存程序或視圖）中獲得結果集，如果簡單的話可以通過使用視圖或inline table-valued function來解決。複雜的話就要靠CLR的ADO.NET provider（如SQLDataReader）了。好像還有個SQLPipe的東西，不過不怎麼瞭解，還是略過算了。

總結
以下的這張圖總結了使用場合：http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508

參考資料：
how to weigh the strengths and weakness of CLR over T-SQL you might consider reading the following resources from Microsoft:
Using CLR Integration in SQL Server 2005
<http://msdn.microsoft.com/en-us/library/ms345136.aspx>

Performance of CLR Integration
<http://technet.microsoft.com/en-us/library/ms131075.aspx>

other resources that may be helpful:
The Database Administrator’s Guide to the SQL Server Database Engine .NET Common Language Runtime Environment
<http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508>