differences between EXECUTE() and SP_EXECUTESQL()

EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
N'@level tinyint',
@level = 109,@MessageIn Output;


1. EXECUTE() :: If we write a query which takes a parameter lets say "EmpID". When we run the query with "EmpID" as 1 and 2 it would be creating two different cache entry (one each for value 1 and 2 respectively).

It means for Unparameterised queries the cached plan is reused only if we ask for the same id again. So the cached plan is not of any major use.

2. SP_EXECUTESQL() :: In the similar situation for "Parameterised" queries the cached plan would be created only once and would be reused 'n' number of times. Similar to that of a stored procedure. So this would have better performance.

A Simple usage of Replace Function

REPLACE ( string_expression1 , string_expression2 , string_expression3 )


SELECT Replace('OurTeam.com Rocks!', 'Rocks', 'Rolls')