From: Gert-Jan Strik on

SQL Server does not create execution plans for views. It only creates
execution plans for queries. So the fact that you are using a view, and
that that view joins several tables or selfjoins is not relevant.

When you use a view in a query, the view is expanded *, and the view's
definition is merged with the rest of the query before the execution
plan is determined. (* indexed view work differently)

So that reduces your question to the questions:
1) will SQL Server create a new execution plan if you add an extra
expression in the WHERE clause that uses a parameter/local variable.

2) And will the "earlier" execution plan for the query without the extra
expression still be available?

Answer to 1: Yes, a new execution plan will always be determined for a
new query.

Answer to 2: That depends on the way you supply the parameter, and
whether auto-parameterization kicks in. As Uri suggested, it is probably
easiest to simply test this.


"OceanDeep via" wrote:
> We are using SQL 2005/2008 std. I have a view that joins and self joins
> between two tables with various join criteria and the criteria is static.
> Once the view is created, we have a query that will join the view with
> another big table and the where clause has two parameters which are based on
> the big table. When this query runs with various values in the two
> parameters, I believe the execution plan will be cached based on the new
> feature, parameter sniffing in SQL 2005/2008. Is this correct?
> Furthermore, say a third parameter is introduced in this query, obviously the
> optimizer will need to redo the execution plan. My another question is that
> would SQL keeps two execution plans, one for the two parameter one and one
> for the three parameter one assuming memory resouce is plenty and these two
> queries are run often?
> Keep in mind that the query mentioned above is not a stored procedure. Our
> web application calls the query whenever a user uses that page on our web
> application.
> od
> --
> Message posted via
From: Erland Sommarskog on
OceanDeep via (u46587(a)uwe) writes:
> Thank for the reply. My wording may be a bit confusing but as mentioned
> in my post, the view is static. Our web application uses that view and
> joins another big tables so parameterizing is done in the code in the
> web application. When SQL receives the query, the parameter values
> (literals) should be in place already as in your first example.

This is bad! You should not interpolate parameter values into the
query string, but you should use parameterised queries instead. Right
now, you get one new cache entry for each new set of parameters you
use. There is also the risk for SQL injection.

Erland Sommarskog, SQL Server MVP, esquel(a)

Links for SQL Server Books Online:
SQL 2008:
SQL 2005:
SQL 2000:

First  |  Prev  | 
Pages: 1 2
Next: DBMail Problems