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.
Well, I suppose you're talking mostly about the Profiler classes, but the explanation stands anyway.
An SQL batch is a set of one or more statements grouped together and separated by a GO statement. EG: more SELECT and INSERT statements form a batch if they have a GO at the end.
A RPC call is a call that comes from a client application to the database. EG: a windows service, a web application, a windows app, whatever needs a connection to the database actually makes a RPC call.
Now, in Profiler you'll see everything that touches the database server.
A batch from Management Studio, an RPC call (which is either a batch or a stored procedure call) from an external application, a procedure execution from Management Studio.
Each of them is formed of TSQL statements, so this Profiler class is useful in case you want to expand the execution further, to see what's actually executed. What inserts, selects..etc.
The easiest way to look at them in Profiler is to enable only End RPC call, or End batch call and you'll see there all the statistics needed (duration, IO, CPU). Then, move further by enabling TSQL Statements class and dig deeper.
Best Answer
The documentation on query hints says
That trace flag was documented in KB980653 which tells us
The documentation on supported trace flags says about 4136
It seems to me that these are all a single feature, wrapped in several syntactic formulations, to be applied at different granularity. It can be applied on the instance, a database, the session, a stored procedure, a query or a single parameter within that query. They all do the same thing, just use a different form of words to achieve it. This opinion is supported by the steps to apply when addressing sniffing issues which uses OPTIMIZE FOR and DISABLE.. in turn.
Some of the duplication can be explained in the general move from opaque trace flags to developer-friendly transparent hints. I find "disable sniffing" easier to reason about than "optimize for unknown".
I suppose we may never know if there is any actual code-path difference internally, unless someone with source code access happens to becoming interested in answering. Practically, I don't believe there is.
I looked at the plans produced for very simple queries
The query plans for both hints are indistinguishable. Both the actual execution plan and the bound tree (trace flag 8605) are the same.