Table Variable Effect on Query Plans

I was presenting a session to a local user group a few weeks ago when a member of the audience asked how table variables impacted a query plan.  (Thanks to Itzik Ben-Gan, Lubor Kollar, Erland Sommarskog, and Andrew Kelley for their input.)

This is a good question, especially because table variables act a lot like a temporary table but are handled quite differently by the SQL Server query engine.  For one thing, SQL Server creates statistic histograms (a.k.a. distribution statistics) for permanent and temporary tables but not table indexes.  That means that the query optimizer is forced to make assumptions about the selectivity of the table variable, assumptions that may be off the mark and lead to inefficient plans.  The assumptions, in lieu of statistics, is a 30% distribution within the table variable.

Typically, trivial plans are unaffected (e.g. a query with a seek plus a partial scan in clustered or covering index) because they don't use a histogram to answer the query.  However, query plans with frequently varying efficiency based on selectivity are affected (e.g. a query with a seek plus a partial scan plus a lookup on nonclustered, noncovering index).

As an aside, SQL Server 2000 and 2005 maintain rowcount information for table variables in their respective metadata structures (i.e. sysindexes for 2000 and sys.partitions for 2005).  But table variables can only access this information during a recompile and table variables alone in a query do not cause a recompile.  You'll have to manually force that using, for example, a RECOMPILE query hint in SQL Server 2005.

Hope this helps,

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.