Posted by Erland Sommarskog on 10/01/18 11:42

Frank Villasenor (jawzx01@gmail.com) writes:
> Regarding stored procedures and views, I know that stored procedures
> cause SQL Server to create a cached execution plan. Is the same thing
> done for views? Also, how bad is the performance hit for a stored
> procedure that use 1 or a few views as opposed to re-creating the same
> select statement with the proper joins to the required tables?

Views are essentially macros that are expanded, and the plan is built for
the expanded query.

As Stu says, SQL server caches plans for stored procedures as well as
ad hoc-statements.

The performance hit there could be for using a view, rather than a direct
SELECT is that the view refers to tables that the query at had does
not need.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]