TABLE Variables

Andrew Hauger

unread,

Mar 18, 2002, 7:59:03 AM3/18/02

to

I have used TABLE variables in other database environments, but not in SQL
Server. Books Online gave me enough information to get started on this
topic, but not enough information to satisfy my interest. Can anyone tell me
if TABLE variables are actually TEMP tables on disk referenced by variable
names, or if they are actually created only in memory? What are the limits
for TABLE variables?

Thanks in advance for any help you may be able to provide.

Andy

Dan Guzman

unread,

Mar 18, 2002, 10:14:53 AM3/18/02

to

Table variable data may be stored on disk in the tempdb database.
Consequently, memory usage is not an issue with table variables. AFAIK,
you are limited only by tempdb database size.

The main difference between table variables and temp tables is scope and
internals, such as locking.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Andrew Hauger" <a_ha...@yahoo.com> wrote in message
news:rwll8.27480$Dv6.8...@typhoon.austin.rr.com...

Greg D. Moore (Strider)

unread,

Mar 18, 2002, 11:04:31 AM3/18/02

to

"Dan Guzman" <dang...@nospam-earthlink.net> wrote in message
news:Nvnl8.24346$P4.21...@newsread2.prod.itd.earthlink.net...

> Table variable data may be stored on disk in the tempdb database.
> Consequently, memory usage is not an issue with table variables. AFAIK,
> you are limited only by tempdb database size.
>
> The main difference between table variables and temp tables is scope and
> internals, such as locking.
>

Are you sure? I thought one big advantage of table variables was that they
were in memory only and not written to TempDB? It's been awhile since I've
looked at this though.

BP Margolin

unread,

Mar 18, 2002, 6:18:38 PM3/18/02

to

Greg,

In general, table variables will be held in memory, however if the amount of
data loaded into a table variable does grow large enough, then SQL Server
will write the data to tempdb.

It's not unlike the situation with an ORDER BY clause. If possible, SQL
Server will try to do sorting in memory, but it might have to write to
tempdb if the result set being sorted is large.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Greg D. Moore (Strider)" <moo...@greenms.com> wrote in message
news:jeol8.140134$V15.23...@typhoon.nyroc.rr.com...

Dan Guzman

unread,

Mar 19, 2002, 9:04:34 AM3/19/02

to

I believe BP answered your question. The main point is that one doesn't
need to be concerned with table variable memory usage since tempdb will
be used as needed.

Hope this helps.

"Greg D. Moore (Strider)" <moo...@greenms.com> wrote in message
news:jeol8.140134$V15.23...@typhoon.nyroc.rr.com...
>

luc

unread,

Mar 20, 2002, 3:19:07 PM3/20/02

to

Table variables are not stored in memory but may be cached like any other
table. They
are stored in tempdb. The advantages are explained in books online (one is
scope and
another is less locking than user tables).

"Dan Guzman" <dang...@nospam-earthlink.net> wrote in message

news:SzHl8.1163$MC5.1...@newsread2.prod.itd.earthlink.net...