Why would recompile query hint result in different plan for same adhoc statement after freeproccache? This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general. 

I've got an adhoc sql statement that gets a different query plan solely because of  OPTION(recompile). The batch statement is never (in practice) being reused as it is using non-parameterized dynamic sql generation where the statement changes with dates / other parameters. I've confirmed this is the case by checking the cached plan_handle, always a different ID as values in batch change. 

When I do dbcc freeproccache I expect that the query plan I get for the statement would be exactly the same as one with "option (recompile)" query hint added. But its not, its quite different actually (much much faster). 

Does anyone know why addition of a query hint would cause the engine to pick a different query plan? 

The statement is something like this

dbcc freeproccache
go
sp_executesql ' 
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate 
and exists( 
select A
union all select B
union all select C
)'


all I have to do is change the statement to use recompile query hint and I get different (much better) query plan.

dbcc freeproccache
go
sp_executesql ' 
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate 
and exists( select A
union all select B
union all select C
) OPTION (RECOMPILE)'


As code is passing in the date values I can't use a plan guide to override the default behavior, looks like code change will be required. But I wish I knew where to look on why recompile would force a different plan to be used.
The query you posted contains variables. 

SQL Server doesn't do variable sniffing so without OPTION (RECOMPILE) it will compile a general plan as it would for OPTIMIZE FOR UNKNOWN. 

I don't really follow your question though. At one point you seem to be saying that the version without the hint is "much much faster" and  then later you say the version with the hint is "much better". So which one is it?

Both are explicable however. If you find the version with the hint is better than this is because SQL Server can use statistics to estimate the number of rows that will be matched by the date predicate and choose an appropriate plan for that case. 

If the version without the hint is better the statistics themselves may need updating. Perhaps when they were last updated there were few or no rows meeting that predicate and so SQL Server massively underestimates the number of rows that will be returned. See Statistics, row estimations and the ascending date column for more about this potential issue.