對於設計和創建資料庫完全是個新手？沒關係，Joe Celko， 世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的資料庫老手，也會給他們帶來驚喜。Joe是DMBS雜誌是多年來最受 讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO ...
對於設計和創建資料庫完全是個新手？沒關係，Joe Celko， 世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的資料庫老手，也會給他們帶來驚喜。Joe是DMBS雜誌是多年來最受 讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO SQL標準委員會工作了10年，為SQL-89和SQL-92標準做出了傑出貢獻。
在上一篇文章里已經介紹了SQL Server里的存儲過程標題，Joe會繼續談下存儲過程內容的話題。在這篇文章里，他會概況談下作為過程化語言的T-SQL的局限性，當決定如何使用它們時要記住那些。
在第一篇到第四篇，我們創建了表，架構的基礎和可視化。但我們還沒結束，因為架構不止這些。在一個真正的資料庫里，有更多的結構需要考慮。在這些其它架構層級外的東西是：游標，觸發器和存儲過程。還有其它像核對，翻譯，特權（collations, translations, privileges）和像這樣的東西。我只談這三個東西——游標，觸發器和存儲過程——我只用最常規的方式命名。5-SQL和其它產品可以有更高的專利，不管ANSI/ISO標準。理由很簡單：這些東西是建立在早期SQL產品使用的現有文件系統之上。這些過程化的結構是用來彌補早期產品聲明式代碼的缺陷。供應商有鎖在“代碼博物館”里的用戶，不會放棄他們的客戶基礎。
在第5篇，對於存儲過程，我們討論存儲過程標題有什麼和它是如何工作的，就像一個黑盒子。在第6篇，我們到黑盒子裡面看看。
過程化SQL
SQL允許存儲過程代碼模塊在架構里保存。同時在標準SQL里有SQL/PSM語言，你會使用像T-SQL的專門語言。這些語言通常是Algol家族的成員；那就是說他它們有IF-THEN-ELSE，WHILE迴圈和有BEGIN-END作用域的代碼塊。
這些專用語言的大多數從未想用做程式開發。對於T- SQL的首要規則（The rules of thumb）是不寫超過50行的的過程，且不使用PRINT。但事實上，你可以避免所有的面向過程，每個表像文件和代碼一樣對待，好像資料庫是個過程化的 文件系統。如果你喜歡疼痛，大可敲個釘子到你身體，所以不用糾結。
T-SQL是個一次通過的編譯器。這是你必須前置本地變數，使用@（@標誌，“蝸牛（snail）”或“小蝸牛（petite escargot）”）的參數，@@是系統級的變數，#（井號）和##是臨時表。多通道編譯器創建符號表，然後用每個通道探索程式對象的東西。當它第一次找到它時，一次通過編譯器需要用新的符號來告知做什麼。一旦它們傳過來，因此@表示“為我分配本地存儲”，@@表示“在程式外找我，對它我是全局的”，#表示“對於當前會話在tempdb里創建我”，##表示“在臨時表裡創建並保持我”。其它的一起是假定在DDL里定義。
SQL不計算
你不能期望T-SQL來做過程化代碼的優化。那需要多通道。例如，大部分FORTRAN編譯器使用代數學寫入來進行計算上的優化。來自IBM的F和G系列可以給學生完整錯誤信息的快速編譯器，慢但可以為產成品優化性能。
1960年期間一個經典的IT故事是，IBM的國防部（DoD (Department of Defense) ）測試和通用電腦的FORTRAN編譯器。IBM編譯器運行了很長時間，生成一個壓縮的可執行模塊，當運行的時候，很快得出正確答案。通用編譯器運行了很長時間，生成了很小的可執行模塊，包含一個WRITE語句，立即列印出正確的答案。啥問題？問題是涉及函數和它們的取消退出逆轉。關鍵是浮點取整錯誤。通用FORTRAN編譯器成功配對函數和它們的逆向，完成代數並並以常量生成答案。
在T-SQL里避免浮點數和實數。在T-SQL里有同樣的數據類型，但在標準SQL里沒有。問題是浮點數需要特定來處理避免取整錯誤和比較。有個它們需要調用近似數字數據類型的特定原因。這個特定處理需要要麼是軟體內建的，要麼是硬體的一部分，這就說你需要浮點處理器。同樣，你的老闆不會為你的桌面安裝游戲顯卡。商業應用伺服器通常不需要這些昂貴的功能，不管你在工作的時候花多少時間在玩Halo或Doom游戲上。
即使晶元便宜，你也不能合理期望期望T-SQL來做數學上優化的事。SQL是個數據檢索和管理語言，不是用來計算的。你想要的是寫出傳給統計軟體包來獲得數據的好查詢，一個報表或其他特定工具。
如果需要十進位的地方，那麼就使用DECIMAL數據類型。它們可以很好處理。竅門就是給你自己足夠的十進位控制項來獲得正確的整數。那意味著你需要知道你行業的標準。尤其是，如果你用歐元，你需要知道“歐元三角（euro triangulation）”，貨幣轉換和記賬規則。
最好親自做下代數，讓演算法儘可能簡單。這樣建議也適用於字元和時間數據。
T-SQL有基於C的函數庫。這是為什麼可以使用%來代替標準mod函數的原因。
SQL不用來顯示
再次強調，SQL是數據檢索和管理語言，不是用來做前端顯示的。在SQL數據類型里一起拿到數據，把它們“FQ（over the wall）”傳給前端程式，例如報表編輯器和圖形包，這樣看起來會更好。
但是因為過程語言是焊接到它們的文件，程式員寫單片程式成長起來。COBOL只是字元串和顯示模板。FORTRAN有它自己的格式化語句。BASIC版本有使用#和其它符號的圖片選項。即使像C的低級語言，在它的printf函數里有精確的格式化選項！
長期的過程化語言編程後，對於很多程式員，分層的概念非常困難。事實上，在現在，你還是可以聽到抗議：“在資料庫我就可以完整這個並節約時間”。
有時候拿是對的。但大多時候，這不會節約。顯示格式化會從在基本列上使用索引阻止優化器。前端然後會拆回格式化列到它們的源數據或另一個格式。比起在它們的列里有基本數據類型的簡單列，真正的損失是這更難維護。
讓我給你2個常見的例子。使用專門的CONVERT()函數把時間數據轉為字元來顯示。讓程式為你做這個；它們有函數庫來做這個。你不用擔心國家設置或正確的取整（可以是通過程式設計決定的程式）。當你有DATEPART（）和CAST（）時，CONVERT（）的最壞使用是對字元處理。可以看下兩個日期轉為字元串，然後比較字元串。
第2個常見例子是從姓和名組合為姓名。這會阻止在姓列上的索引使用，會給前端帶來可用空間和規則的重格式化問題。你會看到翻轉名字順序（名，姓）的前端代碼
基本聲明式編程啟發法（Basic Declarative Programming Heuristics）
結構化編程實際上有修正性的數學證據。你可以且應該看下Dijkstra, Wirth and Manna。這實際上是會幫你編程的理論。聲明式編程還沒到那個點。但可以給你寫啟發。當你看到一個特定情形時可以嘗試些事情；它們不是宇宙法則，就像精明投資者的押註。
關於這個話題有2個系列（看下下麵參考文章）可以給你過程化的例子，半過程化和聲明式編程風格。但現在，讓我給你有幫助的“高水平提示”的快速清單。
傾向一句頂多句
在一個沒有使用T-SQL流程式控制制的一個SQL語句里，你可以做的更多工作，代碼越好，工作越順。因此，如果你的存儲過程主體有兩個或更多引用到同個表，你大可以組合它們並一次訪問那個表。
你可以使用CASE表達式來避免很多的IF-THEN-ELSE控制邏輯。在CASE表達式前，是應用邏輯到SQL的表達式。經典的例子是多年來Sybase/SQL伺服器類一部分的UPDATE語句。你有個書店，想修改書的價格。超過25美元的書上漲10%（這個會做廣告），低於25美元打85折（這個不會做廣告）。
經典的在偽代碼里，結構化編程的答案如下：
BEGIN OPEN FILE (Bookstore); READ (price) FROM Bookstore; WHILE NOT EOF (bookstore) DO BEGIN IF price < 25.00 THEN UPDATE Books SET price = price * 1.10 ELSE UPDATE Books SET price = price * 0.85; FETCH NEXT Bookstore; END IF; END WHILE; CLOSE FILE (Bookstore); END:
很容易把偽代碼準換為游標。純粹的SQL語句會如下:
BEGIN UPDATE Books SET price = price * 1.10 WHERE price < 25.00; UPDATE Books SET price = price * 0.85 WHERE price >= 25.00; END;
但這不對！如果一本書現在售價是24.95美元。當第一個UPDATE語句執行後，會是27.45美元。但當我們執行第2個UPDATE時，最後的價格會是23.33美元。這不是我們想要的。交換下UPDATE語句也沒用；在頂部的書會更新2次。
這是對游標的經典異議。在那些日子里，對於這類問題，我們有各類可怕的多個表掃描存儲過程。現在，我們有了CASE表達式，它是聲明式，做一次表掃描。
UPDATE Books SET price = CASE WHEN price < 25.00 THEN price * 1.10 ELSE price * 0.85 END;
這個啟髮式有個部分：
- 在多個語句里查找出現的同樣表；它們是可以組合一起的。
- 如果用IF-THEN-ELSE控制語句的話，可以在單個語句里用CASE表達式替換分支。
同樣的啟髮式適用於INSERT INTO語句。這個的一個格式是插入初始的一些行，隨後是選擇的一些行。結構如下：
1 INSERT INTO Foobar (..) VALUES(..); 2 INSERT INTO Foobar SELECT .. FROM.. WHERE..;
也可以寫成這樣：
1 INSERT INTO Foobar (..) 2 (SELECT X.* FROM (VALUES (..)) AS X) 3 UNION ALL 4 SELECT .. FROM.. WHERE..;
當然CASE表達式也可以用在SELECT語句里。
或許這個啟髮式的最佳例子是MERGE語句，可以讓你把INSERT和UPDATE組合為一個語句。這裡我不會討論它，但強烈建議你看下它。
避免本地變數
T-SQL必須分配本地本地變數，它們經常是不需要的。一個常見的模式：
1 CREATE FUNCTION Foobar (..) 2 RETURNS <data type> 3 AS 4 BEGIN 5 DECLARE @local_return_holder <data type>; 6 SET @local_return_holder 7 = <scalar query>: 8 END;
可以更簡單：
1 CREATE FUNCTION Foobar (..) 2 RETURNS <data type> 3 AS 4 BEGIN 5 RETURN (<scalar query>); 6 END;
本地變數的其他缺點它們會從優化器隱藏表達式。
BEGIN SET @local_x = (<scalar query>); -- has to load local variable .. <statement using @local_x>; END;
可以是：
BEGIN .. <statement using (<scalar query>)>; --optimizes whole expression END;
你也可以嵌套調用函數，不用在本地變數里的直接值逐步處理。這個的最好例子是REPLACE（）的如下系列調用：
SET @x = REPLACE (@x, 'a', 'A'); SET @x = REPLACE (@x, 'B', 'b'); ETC
使用REPLACE (REPLACE..(REPLACE (@x, 'z', 'Z') ..))最多你可以32層。
對此概念有問題，你可以和LISP程式員談下。這個語言只有嵌套函數調用。
傾向JOIN非Loop
有很多其他技巧可以避免逐行處理。例如，不用說太多，for迴圈通常可以用join到系列表（Series table）來代替。系列表（Series table）是來一個到上限的一系列整數。
尋找應該在DDL里的東西
在存儲過程里IF-THEN邏輯的使用在運行時清理數據，這是你真的在DDL里需要CHECK（），在第一時間就阻止出錯。例如：
1 SET T.x = COALESCE (T.x, 0); 2 IF (x > 12)..;
這是你需要你在一些列上有預設值和約束的標誌。在表裡修改“x INTEGER”如下：
CREATE TABLE T (.. x INTEGER DEFAULT 0 NOT NULL CHECK (x BETWEEN 0 AND 12), ..);
避免CLR和XML混用
保持外部語言在架構之外。不添加其他語言來混合的SQL已經很難維護。當你在語句里找到一個CLR模塊你不知道，你會怎麼辦？它們不會遵循例如MOD（），SUBSTRING（）和算術取整等同樣的定義。最好的例子是C#和VB之間的區別，2個微軟專屬語言在布爾值表達上卻是一致的。
參考文章：
原文鏈接：
http://www.sqlservercentral.com/articles/Stairway+Series/70950/
