Stored procedure vs. SQL Query
One of the common questions that I face while reviewing the applications at customer site is if SQL Server caches and reuses the plan then where is the difference in stored procedure vs. SQL Query.
There is no single answer; recommend to read Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 as this article highlights and explains most of the items in detail.
Couple of more points to remember:
- Main goal for stored procedures is to promote plan reuse and we save on time taken to compile the SP each time it is called. SQL Server converts the name of the stored procedure to an ID, and subsequent plan reuse happens based on the value of that ID.
- Yes, you are right plan reuse is done for Adhoc Queries also. But few catches - ad-hoc queries can reuse the plan only if the texts for two ad-hoc queries are exactly the same. The text match is both case- and space-sensitive. So, even for a slight variation in the query, SQL will end up generating totally a new plan.
