先站在應(yīng)用程序的角度說說它們的不同。

1、? 直接拼 SQL

就像大家了解的那樣，直接拼 SQL 帶來了 SQL 注入攻擊，帶來了拼時些許的性能損失，但是拼不用添加 SqlParameter ，會少寫很多代碼——很多人喜歡直接拼，也許就因?yàn)檫@點(diǎn)。這種做法會把你拼好的 SQL 原樣直接發(fā)送到 DB 服務(wù)器去執(zhí)行。（注意類似 ”exec yourproc ‘param1’, 12” 的語句不在此范疇，這是調(diào)用存儲過程的一種方式）

2、? 參數(shù)化 SQL

所謂的“參數(shù)化 SQL ”就是在應(yīng)用程序側(cè)設(shè)置 SqlCommand.CommandText 的時候使用參數(shù)（如： @param1 ），然后通過 SqlCommand.Parameters.Add 來設(shè)置這些參數(shù)的值。這種做法會把你準(zhǔn)備好的命令通過 sp_executesql 系統(tǒng)存儲過程來執(zhí)行。通過參數(shù)化 SQL ，和直接拼 SQL 相比，最直接的好處就是沒有 SQL 注入攻擊了。

3、? 調(diào)用存儲過程

直接調(diào)用存儲過程其實(shí)和參數(shù)化 SQL 非常相似。唯一的本質(zhì)不同在于你發(fā)送到 DB 服務(wù)器的指令不再是 sp_executesql ，而是直接的存儲過程調(diào)用而已。

?

很多人非常非常厭惡在應(yīng)用程序中使用存儲過程，而寧愿使用拼 SQL 或者參數(shù)化 SQL ，理由是它們提供了更好的靈活性——這個理由其實(shí)非常非常的發(fā)指（俺現(xiàn)在喜歡上這個詞了）。

現(xiàn)在做設(shè)計(jì)，一般都是從上到下來，重心都在業(yè)務(wù)邏輯上。傳說中的領(lǐng)域模型設(shè)計(jì)完，測試用例都通過之后，才會考慮數(shù)據(jù)持久化方式。數(shù)據(jù)持久化是系統(tǒng)的一部分，但絕對不是最重要的部分，設(shè)計(jì)應(yīng)該圍繞業(yè)務(wù)邏輯開展，持久化應(yīng)該僅僅是個附件。至少，高層應(yīng)用應(yīng)該盡可能的不關(guān)心處于最底層的物理存儲結(jié)構(gòu)（如：表）和數(shù)據(jù)持久、反持久方式（是拼 SQL 還是存儲過程），所以用不用存儲過程根本不重要。很多人害怕存儲過程，其實(shí)是害怕存儲過程中包括業(yè)務(wù)邏輯——真實(shí)情況是，如果存儲過程中包含了業(yè)務(wù)邏輯，那一定最初需求分析不夠?qū)е掠美崛〔蛔悖瑢?dǎo)致測試用例覆蓋不夠，導(dǎo)致領(lǐng)域模型設(shè)計(jì)不充分，要不就是偷懶。

?

=====

站在 DB 角度討論它們的不同，主要從 cpu 、內(nèi)存方面來考慮，其他諸如安全性， msdn 上都有， google 也能拿到一堆資料，不再贅述。

首先是查詢計(jì)劃。

SQL 編譯完一條 SQL 之后，會把它緩存起來（可以通過 sys.syscacheobjects 系統(tǒng)視圖查看），以后再有相同的查詢過來（注意 sys.syscacheobjects 視圖中的 sql 字段，和它存儲的東西完全一樣才能稱為“相同的查詢”），會直接使用緩存，而不再重新編譯。

?? 存儲過程，伊只編譯一遍（如果沒有指定 with recompile 選項(xiàng)的話，如果指定了，根本就不會生成計(jì)劃緩存）。

?? 參數(shù)化 SQL ，和存儲過程基本一樣，只要是相同的查詢，也都是只編譯一次，以后重用（當(dāng)然，指定了 option(recompile) 的除外）。這里不得不提 .NET SqlClient 組件的一個齷齪：如果你的參數(shù)中包含 varchar 或者 char 類型的參數(shù)，你在 Parameters.Add 的時候又沒有指定長度，它都會根據(jù)你實(shí)際傳入的字符串長度 ( 假設(shè)是 n) 給你重新定義成 nvarchar(n) 。如： select * from mytable where col1 = @p1 ，你設(shè)置 @p1 為 ’123456’ ，實(shí)際傳到 sql 這邊的命令是： exec sp_executesql N'select * from mytable where col1 = @p1',N'@p1 nvarchar(6)',@p1=N'123456' 。這樣，系統(tǒng)緩存中實(shí)際存儲的 sql 是： (@p1 nvarchar(6))select * from mytable where col1 = @p1 。看到了吧？如果你的輸入?yún)?shù)變動比較多，那么看起來同樣的一條語句，會被編譯很多次，在緩存中存儲很多份。 cpu 和內(nèi)存都浪費(fèi)了。這也是在《寫有效率的 SQL 查詢 IV 》中建議的使用最強(qiáng)類型參數(shù)匹配的原因之一。

?? 拼 SQL 。到這里不說大家也猜的出來，拼 SQL 要浪費(fèi)大量的 cpu 進(jìn)行編譯，浪費(fèi)大量緩存空間來存儲只用一次的查詢計(jì)劃。

?

服務(wù)器的物理內(nèi)存有限， SQLServer 的緩存空間也有限。有限的空間應(yīng)該被充分利用。通過性能計(jì)數(shù)器 SQL Server:Buffer Manager"Buffer Cache hit ratio 來觀察緩存命中率。如果它小于百分之 90 ，你就得研究研究了。關(guān)注一把諸如 sys.dm_os_memory_cache_counters 、 sys.dm_os_memory_cache_entries 、 sys.dm_os_memory_cache_hash_tables 、 sys.syscacheobjects 等視圖，基本可以確定問題出在哪兒。

cpu 方面需要關(guān)注三個性能計(jì)數(shù)器： SQLServer:SQL Statistics"Batch Requests/Sec 、 SQLServer:SQL Statistics" SQLCompilations/sec 、 SQLServer:SQL Statistics" SQL Re-Compilations/sec 。如果 compilations 數(shù)目超過 batch 請求數(shù)目的百分之 10 ，或者 recompilations 數(shù)目超過 compilations 數(shù)目的百分之 10 ，那基本可以說明 cpu 消耗了太多在編譯查詢計(jì)劃上面。

??? 最后，我的建議是：
????1、DB中的所有操作都盡可能的使用存儲過程，哪怕只是一句簡單的select。
??? 2、鄙視拼SQL。

btw:MSDN中對拼SQL稱為"ad hoc"，呵呵。

==================
補(bǔ)充一點(diǎn)，說明一下N'@p1 nvarchar(6)'換成N'@p1 nvarchar(30)'會重新編譯:)。
程序代碼如下：

執(zhí)行完這段程序，可以觀察觀察sys.syscacheobjects:

???
上圖中的5、6行標(biāo)記了緩存的查詢計(jì)劃。
=======

另外，再來說個更應(yīng)該注意的地方：

注意，上述代碼中最后一次操作我把@d參數(shù)重命名成了@a，然后再來看看sys.syscacheobjects里面有啥:


注意第六行。

================
稍微提一下“簡單參數(shù)化”（SQL2k中稱為自動參數(shù)化）和“強(qiáng)制參數(shù)化”。在簡單參數(shù)化下，SQL會試圖參數(shù)化你的語句，以減少查詢計(jì)劃編譯和重編譯，但是可以被參數(shù)化的語句非常有限。這個東東可以通過一條簡單的insert語句測試到，偶就不貼圖了。簡單參數(shù)化是SQLServer的默認(rèn)行為。

強(qiáng)制參數(shù)化可以通過設(shè)置庫的屬性PARAMETERIZATION為FORCED實(shí)現(xiàn)。強(qiáng)制參數(shù)化會在很大程度上參數(shù)化你的語句。但是它有很多的限制（見MSDN）。

但是要注意，由于查詢計(jì)劃不會有兩種和兩種以上的副本，所以SQL可能會選擇一個不合適的計(jì)劃來執(zhí)行你的查詢。這也是偶一再的說，如果你的輸入?yún)?shù)引起選擇性劇烈變化，最好指定recompile選項(xiàng)的原因。

寫有效率的SQL查詢（V）