I'm just reading my company's code guidelines and it says to never treat variables as a constant in sql server, use literals instead. The reasoning is that sql server can't build a good execution plan when you're using variables in the query.
Anybody know if this is still true? We're using MSSQL 2005 and this document may have been written for 2000.
I don't know if this is always true, but using a variable that can't be evaluated until execution time will limit the ability of the optimizer to use any statistics that it has collected about the table when making optimization decisions. The article referenced discusses this and give some tips on how to help SQL Server make it's optimization decisions in the event that it can't be avoided.
Ref: http://www.sqlmag.com/Article/ArticleID/42801/sql_server_42801.html
SQL Server can build a better execution plan if it's using a literal. One case I saw involved a partion view. If you select from the partioned view with a variable SQL will evaluate the variable at runtime, where as if your using a literal SQL goes right after the underlying table.
With that said you should test before hardcoding, and if you have two queries which only differ by the literal then that would be a smell.
This is "generally" true for SQL Server 2005 since it now tries to pre-generate execution plans for as many queries as possible. Your mileage may vary since it depends on your data and your SARG (test your query plans and see) but this tecdoc (they were using a pre-release version of SQL Server 2005) states:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Avoid use of local variables in queries
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, and the optimizer typically will be able to pick a better query plan. For example, consider this query that uses a local variable: