动态语句exec与sp_executesql执行计划区别

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--> <!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

--測試如下(Windows2003+SQL2005 sp3)

USE tempdb

go

IF OBJECT_ID ( 'Test' ) IS NOT NULL

DROP TABLE Test

SELECT * INTO Test FROM syscolumns

go

--step1:

DBCC FREEPROCCACHE -- 清空緩存中的執行計劃

--step2:( 執行3次 )

DECLARE @TabName sysname , @ID INT , @SQL NVARCHAR ( 4000)

SET @TabName= 'Test'

SET @ID= 103--@ID{101,102,103} -- 輸入次值，執行3次

SET @SQL= N'SELECT * FROM ' + @TabName+ ' WHERE ID=' + RTRIM ( @ID)+ ' order by ID asc'

EXEC ( @SQL)

-- 查看產執行計劃

SELECT dbName= DB_NAME ( dbID ) , Cacheobjtype, objtype, objid , sql

FROM sys.syscacheobjects WHERE cacheobjtype= 'Compiled Plan' AND objtype IN( 'Adhoc' , 'prepared' ) AND sql LIKE '%Test%' AND sql NOT LIKE '%syscacheobjects%' AND sql NOT LIKE '%msdb.%'

-- 執行計劃產生3次

/*

dbName Cacheobjtype objtype objid sql

tempdb Compiled Plan Adhoc 319724907 SELECT * FROM Test WHERE ID=102 order by ID asc

tempdb Compiled Plan Adhoc 513201771 SELECT * FROM Test WHERE ID=103 order by ID asc

tempdb Compiled Plan Prepared 475976984 (@1 tinyint)SELECT * FROM [Test] WHERE [ID]=@1 ORDER BY [ID] ASC

tempdb Compiled Plan Adhoc 674221447 SELECT * FROM Test WHERE ID=101 order by ID asc

*/

go

--step1:

DBCC FREEPROCCACHE -- 清空緩存中的執行計劃

--step2:( 執行三次 )

DECLARE @TabName sysname , @ID INT , @SQL NVARCHAR ( 4000)

SET @TabName= 'Test'

SET @ID= 101--@ID{101,102,103} -- 輸入次值，執行三次

SET @SQL= N'SELECT * FROM ' + @TabName+ ' WHERE ID=@ID order by ID asc'

exec sp_executesql @stmt= @SQL, @Params= N'@ID int' , @ID= @ID

SELECT dbName= DB_NAME ( dbID ) , Cacheobjtype, objtype, objid , sql FROM sys.syscacheobjects WHERE cacheobjtype= 'Compiled Plan' AND objtype IN( 'Adhoc' , 'prepared' ) AND sql LIKE '%Test%' AND sql NOT LIKE '%syscacheobjects%' AND sql NOT LIKE '%msdb.%'

-- 執行計劃產生1次

/*

dbName Cacheobjtype objtype objid sql

tempdb Compiled Plan Prepared 421211796 (@ID int)SELECT * FROM Test WHERE ID=@ID order by ID asc

*/