optimize for unknown for SQL Server 2005?

Question

I was listening to the SO podcast and they mentioned Optimize For Unknown for SQL server 2008, they also mentioned that there was something similar for SQL Server 2005. Anyone know what this is?

Answer 1

in sql server 2005 you don't have the unknown option. so the only thing you can do is specify a value of somekind.

Answer 2

As @Mladen Prajdic mentioned, OPTIMIZE FOR UNKNOWN was only introduced in SQL Server 2008.

If you have reasonable knowledge of your query workload, OPTIMIZE FOR can be used to improve query plan caching:

http://blogs.msdn.com/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

http://decipherinfosys.wordpress.com/2007/10/23/optimize-for-query-hint-in-sql-server-2005/

Answer 3

I came across this question... I know it's almost a year old. But Inside a stored procedure you can use a local parameter to mimic the behavior of OPTIMIZE FOR UNKNOWN. See this related stackoverflow question.