SQL Server – Difference Between SET NOEXEC ON and SET PARSEONLY ON

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables

When the optimiser has no usable statistics at all for a column it will guess that an = predicate will match 10% of rows, BETWEEN 9%, and any of >, >=, < and <= will match 30%. If there are column statistics available an = predicate will be treated differently as below.

Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name. So, for example, if the column column_name contains all unique values, then a selectivity estimate of 1/(number of unique values in column) will be used, which is accurate.

So this is essentially the same as using for OPTIMIZE FOR (UNKNOWN). It may well be more accurate than a flat 10% guess but it isn't tailored to the specific values that you are querying.

To force SQL Server to optimize a query every time it is run, and use the values of local variables to estimate cardinality and cost during optimization of the query, add the RECOMPILE query hint to the query.

With the use of RECOMPILE you presumably are getting more accurate cardinality estimates and therefore a different plan with join orders/ join types more suited to the number of rows returned from different parts of your actual query.

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

Rules for Using Batches

The following rules apply to using batches:

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
• A table cannot be changed and then the new columns referenced in the same batch.
• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.