TempDB:: Table variable vs local temporary table

dmv

dmv — Mon, 31 Mar 2008 19:55:46 GMT

PingBack from http://manuelsite.freehostingz.com/dmv.html

re: TempDB:: Table variable vs local temporary table

bertcord — Mon, 31 Mar 2008 22:07:15 GMT

Good article and sample scripts…a few additional points..

1. Queries using table variables don’t generate parallel query plans. We have found with large temporary datasets it is better to use #tables to take advantage of parallel plans.

2. If you need indexes on a @ table you can work around this by defining CONSTRAINTS on Columns. Since under the covers a CONSTRAINT is enforced with indexes this will work. This is required if you need to make updates over linked servers since in 2005 SQL will issues a remote scan across the linked server if columns in JOIN are not indexed.

Bert

re: TempDB:: Table variable vs local temporary table

Sunil Agarwal — Tue, 01 Apr 2008 00:46:05 GMT

thanks. I will update the blog with the additional info you have provided.

Table Variable vs Parallelism

EXEC dbo.LongTermMemory__Archive — Tue, 15 Apr 2008 18:00:50 GMT

There are many myths surrounding table variables and one of the most common is probably the 'in memory'

re: TempDB:: Table variable vs local temporary table

colin leversuch-roberts — Wed, 30 Apr 2008 14:08:24 GMT

A nice post - I've needed something "microsoft" to point to when I try to explain exactly the points in this post and developers don't believe me < grin >

re: TempDB:: Table variable vs local temporary table

cf_joe — Thu, 22 May 2008 17:58:15 GMT

Exactly what I was looking for in understanding the Difference between #table and table variable - Thank You

re: TempDB:: Table variable vs local temporary table

YingGuo — Tue, 16 Dec 2008 21:25:17 GMT

Sunil, I have a question about the 'fourth - the operations done on table variable are not logged' part. It is true that a name like '%tv_target%' is not showing in the log, but when I looked at all the log records generated after the insert and update operation on table variable, I saw log entries on AllocationUnitName #2F650636, which is the internal name for @tv_target. Just to confirm it, I checked sys catalogs, also compared these entries with logs generated after the same operations on temp tables, and I am convinced they are the logs on table variables. I did this on SQL 2008. Thanks.

TempDB Basics (cont..)

SQL Server Storage Engine — Mon, 22 Dec 2008 03:22:20 GMT

In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx