Difference Between Exec and sp_executesql

/* Difference Between Exec and sp_executesql*/ sp_executesql (also known as "Forced Statement Caching") • Performance wise good, Recommended • Allows for statements to be parameterized • Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs. • Has strongly typed variables/parameters – and this can reduce injection and offer some performance benefits! • Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan • Need NVARCHAR Data type otherwise will show error Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. EXEC (also known as "Dynamic String Execution" or DSE) • Performance wise not good, Not recommended • Do not Allows for statements to be parameterized • Allows *any* construct to be built • Treats the statement similarly to an adhoc statement. This means that the statement goes through the same process that adHoc statements do – they are parsed, probably parameterized and possibly deemed "safe" for subsequent executions to re-use • Does not have strongly typed parameters in the adhoc statement and therefore can cause problems when the statements are executed • Does not force a plan to be cached. o This can be a pro in that SQL Server can create a plan for each execution o This can be a con in the SQL Server needs to recompile/optimize for each execution o Works with VARCHAR and NVARCHAR -- Use the following query to test, CREATE TABLE [dbo].[TestExectuionPlan] ( ID INT ) GO INSERT INTO [dbo].[TestExectuionPlan](ID) VALUES (1) INSERT INTO [dbo].[TestExectuionPlan](ID) VALUES (2) GO --- Clean the existing plans DBCC FREEPROCCACHE DECLARE @ItemID INT DECLARE @Query NVARCHAR(200) SET @Query = 'SELECT ''Query Using EXEC'' AS ''EXEC'', * FROM [dbo].[TestExectuionPlan] WHERE ID = ' SET @ItemID = 1 EXEC( @Query + @ItemID) SET @ItemID = 2 EXEC( @Query + @ItemID) SET @Query = 'SELECT ''Query Using sp_executesql'' AS ''SP_EXECUTESQL'', * FROM [dbo].[TestExectuionPlan] WHERE ID = @ID' SET @ItemID = 1 EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID SET @ItemID = 2 EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID -- To view the execution plan, use the following query. SELECT usecounts, sql FROM sys.syscacheobjects where dbid = DB_ID()--- ( to retrieve the execution plan of current database) ---- Check the list of queries in output create for sp_executesql and exec