Performance consideration when using a Table Variable

re: Performance consideration when using a Table Variable

Nick Beagley — Thu, 15 Oct 2009 23:05:12 GMT

I knew a plan that included a join on a table variable would never be parallel, i didn't realise it would also affect populating the table variable.

There's a good comparison of table variables and temp tables on sql server central (and the comments): http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

re: Performance consideration when using a Table Variable

Everest — Tue, 20 Oct 2009 02:03:44 GMT

Amazing to me that a table variable, which is represented as a # table in tempdb, has all of the functionality chopped from it when comparint to its counterpart.

Good article

Thanks

Lee

Output = MAXDOP 1

SQL and the like — Thu, 04 Mar 2010 09:32:17 GMT

It is widely know that data modifications on table variables do not support parallelism, Peter Larsson

re: Performance consideration when using a Table Variable

Peso — Thu, 04 Mar 2010 10:06:40 GMT

Yes, I wrote that I came across this last time in 2007.

re: Performance consideration when using a Table Variable

Martin Smith — Mon, 24 Dec 2012 12:36:01 GMT

@Peso As I'm sure you realised since writing this operations on table variables are logged.

@Nick - Plans that join on table variables can certainly be parallel but you may need an `OPTION (RECOMPILE)` hint for this to happen so that SQL Server takes account actual row counts. Here is an example http://stackoverflow.com/questions/1645846/can-queries-that-read-table-variables-generate-parallel-exection-plans-in-sql-se/8242831#8242831

Also I'll plug my write up on the differences here http://dba.stackexchange.com/a/16386/3690