Are table variables only stored in memory

Seeder question: I've been told that table variables are stored in memory, but temporary tables are written out disk in the tempdb. I've also been told that I should always use table variables because they'll be faster because they're only in memory. Are these statements true and if not, why not?

more ▼

asked Oct 14 '09 at 08:57 AM in Default

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

The above article is a good reference, but I thought it would be worth mentioning a couple of key points here:

1) Table variables lack statistics. This means that query plans often treat the table variable as if it had 1 row. This can cause some severely sub-optimal plans when getting to larger numbers of rows.

2) Indexing on table variables is limited. For example, if you want to create a non-unique clustered index on a table variable, you are out of luck.

And, just for completeness, I'll mention here that both table variables and temporary tables (and, for that matter, data passed via a table type) can exist either in memory, or on disk. The query engine will make the appropriate choices as to where and how to store the objects.

more ▼

answered Oct 14 '09 at 09:49 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Oct 15 '09 at 04:10 PM

Peso gravatar image

1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left

Check this link below out...

There is a "Myth" section


more ▼

answered Oct 14 '09 at 09:09 AM

sp_lock gravatar image

9k 24 27 30

(comments are locked)
10|1200 characters needed characters left

As pointed out by sp_lock, the article at http://www.sqlservercentral.com/articles/Temporary+Tables/66720/ by Wayne Sheffield does a good job of debunking this and expanding on it greatly.

I find that code written with table variables tends to be easier to read and has less worries about the entity remaining when the script is concluded. Thus, I tend to use table variables unless there is a compelling reason to use temp tables.

more ▼

answered Oct 14 '09 at 12:23 PM

TimothyAWiseman gravatar image

15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

If you create a table variable and then query tempdb.sys.objects, you will see an entry there. This definitely suggests that a table variable is a proper object, which is not stored in memory.

But in addition to this, consider the scenario where you put a large amount of data into a table variable. You see the transaction log of tempdb growing as well.

Table variables are definitely stored on disk, although being in tempdb, I would hardly suggest that they 'persist'... they are only scoped to the batch.

more ▼

answered Oct 15 '09 at 04:18 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 14 '09 at 08:57 AM

Seen: 5253 times

Last Updated: Oct 15 '09 at 09:24 AM