Jason Massie's SQL blog - All Comments

re: Never use table variables?

GrumpyOldDBA — Wed, 30 Jan 2008 13:20:09 GMT

I've never found that the recompiles occur as published in BOL/msdn. ( 2000 or 2005 ) but never gave it much thought. Table variables are fine, as you point out, if used to hold small sets of data - I've found issues with table variables ( with largish datasets ) used in joins of several tables - but that's another matter.

In case you don't read our Roller... Breaking news on RTM release date

Adam Machanic — Fri, 25 Jan 2008 21:29:25 GMT

And I quote... final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3. Thanks to Jason

re: The problem with local variables

JasonMassie — Fri, 25 Jan 2008 18:55:13 GMT

Your right. The only fix in all situations is a covering index.

The problem is alot of generic apps are things like seibel, SAP etc. The industry standard stuff...

re: The problem with local variables

GrumpyOldDBA — Fri, 25 Jan 2008 10:14:55 GMT

this isn't exactly unknown territory and has been a possible issue back as far as I can remember. If you can't properly parameterise then your only option is to use a plan guide - however having used plan guides to resolve one set of problems they actually caused another set. If result sets can vary( e.g. it may require a seek for one range vs a scan for another ) the normal option is to set the plan guide to recompile to make sure a correct plan is generated each time. Can be tricky with third party apps which use in-line / dynamic sql ( and I wonder where LINQ will fit in with this? ) Perhaps we should put more pressure on management not to buy "generic" apps which use in-line sql?