table variable vs #temp table

Question

Post reply

table variable vs #temp table

bp

SSCarpal Tunnel

Points: 4656
More actions
December 17, 2002 at 7:09 pm

#159244

What are the cons of using a table variable as opposed to using a temporary table? (besides the fact that you cannot index a table variable)?
The pros for using a table variable is that it avoids locking the table and it operates in memory (ie. no disk I/O).
Thanks in advance,
Billy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

select into, a few other things don't work with a table var.

table var in memory, temp table in tempdb. Resource issues.

Not sure that a var avoids locking a table or that a temp table does. What do you mean?

Keep in mind memory is a shared resource. It is faster, but don't necessarily want to overload it iwth one spid.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

easy Old Hand Points: 320 More actions · Answer 2

if your table is very large , i suppose you'd better use a temporary table.

i work hard to finish my dream.

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

You can add a constraint to a table variable

(which is the same as an index under the hood)

Steven

Antares686 SSC Guru Points: 125444 More actions · Answer 4

You are limited in some of the things you can do with a table variable where a temp table is not as limited. My opinion is use Table variables on small generic items that need a specific structure to work and require nothing more and temp tables everywhere else where a true table is not wanted. But keep in mind creating objects have drawbacks themselves in resources as they must be created, loaded and destroyed and using permanent objects are more bennifical and can be controlled quite easy with a bit of planing. You need to look at your situation look at the amount of data, the number of times objects will be created and destroyed, and what options will provide the most bang for the buck.

From BOL

quote:
Is the same subset of information used to define a table in CREATE TABLE. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL. A user-defined data type cannot be used as a column scalar data type.

bp SSCarpal Tunnel Points: 4656 More actions · Answer 5

Thanks everyone for their responses.

Steve: sorry, I wasn't too clear on the table locking issue. According to BOL regarding table variables: "Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources."