前言
很多人認為資料庫其實很簡單，也沒什麼大深入的細節去研究，但是真正的一些細節問題決定着你的是否是專家。
本文主要講述一下存儲過程參數傳遞的一些小細節，很多人知道參數嗅探，本例也可以了解成參數嗅探的威力加強版++
小例子
1 ---建立測試表
2 SELECT IDENTITY(INT,1,1) AS RID,
3 * INTO TB1
4 FROM sys.all_columns
5 GO
6 ---模拟大量資料
7 INSERT INTO TB1
8 SELECT *
9 FROM sys.all_columns
10 GO 100
11
12
13
14 --在 user_type_id列 建立一個索引
15 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160625-164531] ON [dbo].[TB1]
16 (
17 [user_type_id] ASC
18 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19 GO
20
21 --開啟IO統計
22 set statistics io on
23
24 --測試查詢執行計劃
25 select * from tb1 where user_type_id = 10 
注：本例中，語句的執行應該走索引seek + key look up
測試一
1 --測試1：使用定義變量，把參數值傳遞給變量
2
3 create PROCEDURE dbo.USP_GetData
4 (
5 @PID INT
6 )
7 AS
8 BEGIN
9 DECLARE @ID INT
10 SET @ID= @PID
11 SELECT *
12 FROM TB1
13 WHERE user_type_id = @ID
14 END
15 GO
16 EXEC dbo.USP_GetData @PID=10 結論：如果在存儲過程中定義變量，并為變量SET指派，該變量的值無法為執行計劃提供參考（即執行計劃不考慮該變量）,将會出現預估行數和實際行數相差過大導緻執行計劃不優的情況
測試二
1 ---測試2 : 對參數進行運算
2 create PROCEDURE dbo.USP_GetData2
3 (
4 @PID INT
5 )
6 AS
7 BEGIN
8 SET @PID=@PID-1
9 SELECT*
10 FROM TB1
11 WHERE user_type_id = @PID
12 END
13 GO
14 EXEC dbo.USP_GetData2 @PID=11 結論：如果在存儲過程中使用SET為存儲過程參數重新指派，執行計劃仍采用執行時傳入的值來生成執行計劃。
測試三
1 --測試3 ：對參數行進拼接
2
3 create PROCEDURE dbo.USP_GetData3
4 (
5 @PID INT
6 )
7 AS
8 BEGIN
9 DECLARE @ID INT
10 set @ID = 2
11 SET @PID = @ID + @PID
12 SELECT *
13 FROM TB1
14 WHERE user_type_id = @PID
15 END
16 GO
17 EXEC dbo.USP_GetData3 @PID= 8 結論：如果在存儲過程中使用新定義的變量與傳入參數拼接重新指派，執行計劃仍采用執行時傳入的值來生成執行計劃。
測試四
1 --測試4 ： 對變量進行運算
2 create PROCEDURE dbo.USP_GetData4
3 (
4 @PID INT
5 )
6 AS
7 BEGIN
8 SELECT *
9 FROM TB1
10 WHERE user_type_id = @PID+ 2
11 END
12 GO
13 EXEC dbo.USP_GetData4 @PID=8 結論：雖然傳入參數在傳入後被修改,但是生成執行計劃時仍使用傳入時的值
測試五
1 --測試5 ：對變量進行複雜運算
2 create PROCEDURE dbo.USP_GetData5
3 (
4 @PID INT
5 )
6 AS
7 BEGIN
8 SELECT *
9 FROM TB1
10 WHERE user_type_id = @PID+ CAST(RAND()*600 AS INT)
11 END
12 GO
13 EXEC dbo.USP_GetData5 @PID=8
14 GO 結論：對參數做複雜運算,無法獲得準确的值,是以不能準确地預估行數,也不能生成合理的執行計劃
測試六
1 --測試6 ： 複雜運算使用變量拼接
2 create PROCEDURE dbo.USP_GetData6
3 (
4 @PID INT
5 )
6 AS
7 BEGIN
8 DECLARE @ID INT
9 set @ID = CAST(RAND()*600 AS INT)
10 SET @PID = @ID + @PID
11 SELECT *
12 FROM TB1
13 WHERE user_type_id = @PID
14 END
15 GO
16 EXEC dbo.USP_GetData6 @PID=8
17 GO 結論：針對測試五可以使用參數拼接的方式，以便準确地預估行數，使用正确的執行計劃
總結
技術支援做了比較長的時間了，遇到了很多很多坑，在這些坑中不斷反思，慢慢成長！不要說什麼資料庫更優秀，不要說我們海量資料庫需要什麼什麼高端的技術，其實解決問題的關鍵隻是那麼一點點的基礎知識。
注：本例中還有另外一種情況就是查詢的資料量很大，那麼本身走全表掃描是最優計劃，而由于參數傳遞的問題錯誤的走了index seek + key look up 道理是一樣的。
--------------部落格位址-----------------------------------------------------------------------------
原文位址: http://www.cnblogs.com/double-K/
如有轉載請保留原文位址!
----------------------------------------------------------------------------------------------------
注：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，非常感謝！