tharucse (1/12/2011)
Thanks Gail.I just asked one person who has this practice of using temporary variables.They say that they keep it as a standard way to write sql procedures even there is no use in insert queries Because can not expect all the developers to think in an intelligent way before write queries when it comes to a particular query that may have performance issue because of parameter sniffing.:-)
That's a bad practice. The use of variables for parameter sniffing problems is a specific solution to a specific problem. By using variables everywhere you'll be hindering the optimiser and generally preventing it from finding good execution plans.
Use variables for the parameters only when there really is a parameter sniffing problem and investigation has shown that it is an appropriate solution (rather than rewriting the query or specific query hints). It should not be a standard development practice
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability