x

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, 2009 at 08:57 AM in Default

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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, 2009 at 09:49 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

answered Oct 15, 2009 at 04:10 PM

avatar image

Peso
1.6k 5 6 9

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

Check this link below out...

There is a "Myth" section

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

more ▼

answered Oct 14, 2009 at 09:09 AM

avatar image

sp_lock
10.5k 27 37 37

(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, 2009 at 12:23 PM

avatar image

TimothyAWiseman
15.6k 22 51 38

(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, 2009 at 04:18 AM

avatar image

Rob Farley
5.8k 16 22 28

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1069
x457
x73
x43
x27

asked: Oct 14, 2009 at 08:57 AM

Seen: 7252 times

Last Updated: Oct 15, 2009 at 09:24 AM

Copyright 2016 Redgate Software. Privacy Policy