Using variables in query generates different queryplan

How come the same query with and without variables generates different queryplans? For example the following query:

DECLARE @p0 Int = 103
DECLARE @p1 Int = 1
DECLARE @p2 Int = 38
DECLARE @p3 Int = 103
DECLARE @p4 Int = 1

SELECT [t5].[pkCompanyID] AS [CompanyID], [t5].[name] AS [Name], [t5].[imageurl] AS [ImageURL]
FROM (
    SELECT [t0].[pkCompanyID], [t0].[name], [t1].[imageurl], 
        (CASE 
            WHEN EXISTS(
                SELECT NULL AS [EMPTY]
                FROM [tblCompany] AS [t2]
                WHERE ([t2].[fkCompToCompID] = ([t0].[pkCompanyID])) AND (EXISTS(
                    SELECT NULL AS [EMPTY]
                    FROM [tblUserToGroupToCompany] AS [t3]
                    INNER JOIN [tblGroupToApplication] AS [t4] ON [t3].[fkGroupID] = [t4].[fkGroupID]
                    WHERE ([t3].[fkCompanyID] = [t2].[pkCompanyID]) AND ([t3].[fkUserID] = @p0) AND ([t4].[fkApplicationID] = @p1)
                    ))
                ) THEN 1
            ELSE 0
         END) AS [value], [t0].[fkCompToCompID]
    FROM [tblCompany] AS [t0]
    LEFT OUTER JOIN [tblNodeTypes] AS [t1] ON [t1].[pkNodeTypeID] = [t0].[fkNodeTypeID]
    ) AS [t5]
WHERE (([t5].[value] = 1) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [tblUserToGroupToCompany] AS [t6]
    WHERE [t6].[fkCompanyID] = [t5].[pkCompanyID]
    ))) AND ([t5].[fkCompToCompID] = @p2) AND (EXISTS(
    SELECT N开发者_JAVA百科ULL AS [EMPTY]
    FROM [tblUserToGroupToCompany] AS [t7]
    INNER JOIN [tblGroupToApplication] AS [t8] ON [t7].[fkGroupID] = [t8].[fkGroupID]
    WHERE ([t7].[fkCompanyID] = [t5].[pkCompanyID]) AND ([t7].[fkUserID] = @p3) AND ([t8].[fkApplicationID] = @p4)
    ))

Generates this plan (part of plan)

But the same query if I exchange the variables for values directly in the query. eg.

...WHERE ([t7].[fkCompanyID] = [t5].[pkCompanyID]) AND ([t7].[fkUserID] = 103) AND ([t8].[fkApplicationID] = 1)

generates this plan (same part as other)

There are other changes in the plan also, but I cant fit the whole image here. The first query is about 50% faster than the second.

---

Because when you use FIXED values with AUTO-PARAMETERIZATION off, then the query plan knows EXACTLY what value it needs to run the query for. So the plan is tuned SPECIFICALLY to those values.

However when you use variables, the plan that will be put into the Query Cache is the one containing the parameterized variables - which can be replaced by any variable and will re-use the same plan. Therefore these plans will have to be more robust and generic to handle "best average case".

In SQL Server 2008, you can set whether or not simple parameters are automatically parameterized so you get consistently the "best average case" plan - with all its good and bad.

Ref:

• ALTER DATABASE {parameterization_option} ::=
• Forced Parameterization

EDIT - on performance

As for performance, the optimizer can get it wrong - in this case it looked at statistics for the exact values and felt that the index is not helpful (may be a tipping point issue) so the plan is for clustered scan instead. It is a fine art as to whether to force a query plan - but clearly using an index is 50% faster for

1. specific hardware
2. specific point in time data distribution
3. specific values given

Unless I had a very very good reason, I would not game the Query Optimizer (e.g using index hints) unless I have lots of data to prove it will invariably make it faster.