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

1、? 直接拼 SQL

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

2、? 參數(shù)化 SQL

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

3、? 調(diào)用存儲(chǔ)過(guò)程

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

?

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

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

?

=====

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

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

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

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

?? 參數(shù)化 SQL ，和存儲(chǔ)過(guò)程基本一樣，只要是相同的查詢，也都是只編譯一次，以后重用（當(dāng)然，指定了 option(recompile) 的除外）。這里不得不提 .NET SqlClient 組件的一個(gè)齷齪：如果你的參數(shù)中包含 varchar 或者 char 類型的參數(shù)，你在 Parameters.Add 的時(shí)候又沒(méi)有指定長(zhǎng)度，它都會(huì)根據(jù)你實(shí)際傳入的字符串長(zhǎng)度 ( 假設(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í)際存儲(chǔ)的 sql 是： (@p1 nvarchar(6))select * from mytable where col1 = @p1 。看到了吧？如果你的輸入?yún)?shù)變動(dòng)比較多，那么看起來(lái)同樣的一條語(yǔ)句，會(huì)被編譯很多次，在緩存中存儲(chǔ)很多份。 cpu 和內(nèi)存都浪費(fèi)了。這也是在《寫(xiě)有效率的 SQL 查詢 IV 》中建議的使用最強(qiáng)類型參數(shù)匹配的原因之一。

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

?

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

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

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

btw:MSDN中對(duì)拼SQL稱為"ad hoc"，呵呵。

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

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

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

另外，再來(lái)說(shuō)個(gè)更應(yīng)該注意的地方：

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


注意第六行。

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

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

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

寫(xiě)有效率的SQL查詢（V）