你的SQL語句的參數(shù)化總是個好想法。使用參數(shù)化SQL語句你不會污染你的計劃緩存——錯?。。?/span>在這篇文章里我想向你展示下用參數(shù)化SQL語句就可以污染你的計劃緩存，這是非常簡單的！

ADO.NET-AddWithValue

ADO.NET是實現(xiàn)像SQL Server關(guān)系數(shù)據(jù)庫數(shù)據(jù)訪問的.NET框架的組成——有一些嚴(yán)重的副作用。不要誤解我——只要你正確使用，ADO.NET一直很棒。你馬上就會看到，它很容易被錯誤使用。我們來看下面實現(xiàn)SQL語句執(zhí)行的C#代碼。 

 1 for (int i = 1; i <= 100; i++) 2 { 3    val += i.ToString(); 4  5    cmd = new SqlCommand( 6       "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber", 
 7       cnn); 8    cmd.Parameters.AddWithValue("@CarrierTrackingNumber", val); 9    SqlDataReader reader = cmd.ExecuteReader();10    reader.Close();11 }

我們是聰明的開發(fā)者，因此SQL語句本身被參數(shù)化，因為ADO.NET框架是地球上棒的框架，我們使用System.Data.SqlClient.SqlParameterCollection類的AddWithValue方法來提供實際的參數(shù)值。我在WHLIE循環(huán)里運行那個SQL語句100次，總用不同長度賦予參數(shù)值。在Sales.SalesOrderDetail表里CarrierTrackingNumber列定義為NVARCHAR(25)。因此我們可以在基于我們提供的不同字符長度上有上至25個不同數(shù)據(jù)類型的參數(shù)?，F(xiàn)在讓我們檢查下我們SQL語句執(zhí)行后的計劃緩存。

1 SELECT2     st.text,3     cp.*4 FROM sys.dm_exec_cached_plans cp5 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st6 GO

現(xiàn)在事情變得有點瘋狂：在計劃緩存里我們存儲了100個不同的執(zhí)行計劃！

對于每個可能的數(shù)據(jù)類型參數(shù)都有1個執(zhí)行計劃——即使當(dāng)數(shù)據(jù)類型是NVACHAR(25)。AddWithValue方法非常，非常邪惡：基于你提供的參數(shù)值派生出數(shù)據(jù)類型。永遠(yuǎn)不要使用它！

ADO.NET – SqlDbType.VarChar

因為從我們的錯誤中我們學(xué)到了，現(xiàn)在我們知道ADO.NET的AddWithValue方法的副作用——我們不再用它。現(xiàn)在讓我們重寫我們的C#程序代碼，如下所示定義一個顯示的參數(shù)數(shù)據(jù)類型： 

 1 for (int i = 1; i <= 100; i++) 2 { 3    val += i.ToString(); 4  5    cmd = new SqlCommand( 6       "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber", 7       cnn); 8    cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar)); 9    cmd.Parameters["@CarrierTrackingNumber"].Value = val;10    SqlDataReader reader = cmd.ExecuteReader();11    reader.Close();12 }

從代碼里你可以看到，ADO.NET現(xiàn)在不能派生參數(shù)數(shù)據(jù)類型了，因為我們已經(jīng)指定了SqlDbType.Varchar數(shù)據(jù)類型。讓我們再次執(zhí)行這個SQL語句100次并再次檢查下計劃緩存：

沒有啥改變。問題還是一樣：在計劃緩存里我們還有100個不一樣的的執(zhí)行計劃?，F(xiàn)在的問題是ADO.NET只強(qiáng)制數(shù)據(jù)類型（SqlDbType.VarChar），但不是數(shù)據(jù)類型的"長度"。有100個不同的長度在計劃緩存里你就有100個不同的執(zhí)行計劃。

如果你在你的ADO.NET代碼里顯式指定參數(shù)數(shù)據(jù)類型，你也要指定它的長度！現(xiàn)在我們來看下一些修正的C#代碼。

 1 for (int i = 1; i <= 100; i++) 2 { 3    val += i.ToString(); 4  5    cmd = new SqlCommand( 6       "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber", 7       cnn); 8    cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar, 100)); 9    cmd.Parameters["@CarrierTrackingNumber"].Value = val;10    SqlDataReader reader = cmd.ExecuteReader();11    reader.Close();12 }

這次我也指定了數(shù)據(jù)類型的長度——這里是100，現(xiàn)在當(dāng)我們再次執(zhí)行SQL語句100次時，后我們在計劃緩存里以1個執(zhí)行計劃且重用了100次來完美收工。這是從SQL Server角度的終目標(biāo)。

小結(jié)

寓意：ADO.NET是個很棒的數(shù)據(jù)訪問框架，它提供你有用的功能（例如AddWithValue方法），當(dāng)從SQL Server角度來說你真的要考慮下你在做什么。當(dāng)你使用參數(shù)化SQL語句時，你要盡量顯式：你必須地冠以參數(shù)值的實際數(shù)據(jù)類型，還有你想要的獲得數(shù)據(jù)類型長度。

感謝關(guān)注！ 

注：此文章為WoodyTu學(xué)習(xí)MS SQL技術(shù)，收集整理相關(guān)文檔撰寫，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁面明顯位置給出此文鏈接！

http://www.cnblogs.com/woodytu/p/4751915.html