December 17, 2002 at 7:09 pm
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
December 17, 2002 at 9:00 pm
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
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 17, 2002 at 9:28 pm
if your table is very large , i suppose you'd better use a temporary table.
i work hard to finish my dream.
December 18, 2002 at 1:56 am
You can add a constraint to a table variable
(which is the same as an index under the hood)
Steven
December 18, 2002 at 4:07 am
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.
December 18, 2002 at 3:38 pm
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."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply