这两个sql会使用共同的执行计划吗?

Question

(@STATION varchar(6),@FROMDATE varchar(18),@TODATE varchar(19),@OPERATION varchar(3))             Select Distinct Prod, Station_Id from P_Spc_Header with (nolock) where Operation = @OPERATION and Station_Id = @STATION and Date_Tm >= @FROMDATE and Date_Tm <= @TODATE
(@STATION varchar(6),@FROMDATE varchar(19),@TODATE varchar(19),@OPERATION varchar(3))             Select Distinct Prod, Station_Id from P_Spc_Header with (nolock) where Operation = @OPERATION and Station_Id = @STATION and Date_Tm >= @FROMDATE and Date_Tm <= @TODATE

 

这是经过SQL解析之后的sql,不知道为什么,变量的长度有些变化?

那这种sql会使得重用执行计划吗?

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 1

试了下 变量的长度发生变化,是因为传入的参数的长度不同,比如'NEo','NEOSZ',一个会解析成varchar(3),一个会解析成varchar(5) 我记得看SQLServer 2008 Internals里说的,SQLServer会先将用户的sql解析成自己能识别的sql,也即上面的那种sql.那么按道理说,这两个sql就不一样了.那么他们还会使用同一个执行计划吗?还是说还需要继续对下一个sql解析再生成查询树,继而执行计划?

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 2

試試看使用下列的T-SQL來查看執行計畫幫你快取的內容以及使用次數。

select a.usecounts as '使用次數'
	,a.objtype as '快取類型'
	,b.text
from sys.dm_exec_cached_plans a
cross apply sys.dm_exec_sql_text(plan_handle) b
where b.text not like '%sys%'

---

以上說明若有錯誤請指教，謝謝。
http://www.dotblogs.com.tw/terrychuang/

Answer 3

USE tempdb
GO

CREATE TABLE TB(ID INT PRIMARY KEY,NAME VARCHAR(200))

DECLARE @I INT =1
WHILE @I<=1000
BEGIN
	INSERT INTO TB SELECT @I,LTRIM(@I)+'A'
	SET @I+=1
END

DECLARE @NAME VARCHAR(10) ='100A'
SELECT * FROM TB WHERE NAME=@NAME
GO
DECLARE @NAME VARCHAR(20) ='100A'
SELECT * FROM TB WHERE NAME=@NAME

Answer 4

Optimizer generates execution plan based on stats involved, variable length maybe not factor but value will be. Anyway, sql usually doesn't reuse ad hoc query plans.