Table Variables

Question

Table Variables

Viewing 15 posts - 16 through 30 (of 32 total)

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

Patricia Schiefelbein SSC Rookie Points: 49 More actions · Answer 1

I have found both temporary tables and variable tables to be useful. I have found this article to have a good explanation of when to use each.

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 2

Thank you for taking the time to share with us, Jambu. Maybe you were not able to help the geniuses, but it was very helpful to the rest of us.

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 3

Jambu,

Thanks for sharing your info - this topic sparked a great discussion on the topic!

Mark

the sqlist SSCrazy Points: 2398 More actions · Answer 4

Contradiction:

Nothing comes without caveats! You cannot use Table Variables in the following situations:

select xxx into @table_variable

or

(here) insert into @table_variable select * from myTable

For example if you want to get the output of a Stored Procedure into a table variable, you cannot do so without circumvention. Here is a sample piece of code which gets the output of a Stored Procedure in to a temporary variable. The only way to get the output of a stored procedure into a table variable is by using an intermediary table, for example using a temporary table.

set nocount oncreate table #sp_depends (  t1nchar(128),  t2nchar(128)  )insert into #sp_depends execute sp_depends myTabledeclare @t1 table (ta varchar(200), tb varchar(200))

(here) insert into @t1 select * from #sp_depends

drop table #sp_dependsselect * from @t1

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 5

Quite a nice article, but, as has been pointed out here, a table variable 'does' create in tempdb. In fact, I tested this, and was able to show a growth with just 1 row into an INT only table.

quote:'A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

So we can be explicit now that table tavriables are created on tempdb, albeit in a splightly different manner.

--something bizarre is happening with the formatting of this message

shashi kant SSCommitted Points: 1659 More actions · Answer 6

Hi To all

This is nice article , i worked lot on Table Variable and know all it's + and -

there is a restriction on Table Variable that we cannot use EXEC and string query on table variable.

Can any one give me the right Alternative how i insert Dynamic query result into Table Variable . I don't Want to Use # Table or ## table.

my problem is like that.

DECLARE @shashi(PRIMARY_KEY INT NOT NULL, NAME VARCHAR(50))

DECLARE @SSQL AS STRING, @CHECK AS INT

SET @CHECK = 1

SET @SSQL = 'SELECT ID , NAME FROM MY_TABLE'

IF @CHECK = 1

SET @SSQL = @SSQL + 'WHERE ID >25'

ELSE

SET @SSQL = @SSQL + 'WHERE ID <25'

INSERT INTO @shashi

----------------------HOW I USE THE DYNAMIC QUERY TO INSERT VALUES INTO MY @shashi TABLE. CAN ANY ONE EXPERT SOLVE MY PROBLEM WITHOUT USING TEMP TABLE........

THANKS IN ADVANCE........HOPPING URGENT REPLY.........

Derek Wallace Ten Centuries Points: 1069 More actions · Answer 7

Hello All. First of all thank you Jambu for your article. Certainly got the yeah's and nay's going. A little experience I have had in the past on not so unrelated. Without going into too much detail, I had an issue where a stored procedure was being excessively recompiled. Did the usual analysis and ended up logging a call to MS. A order number and revision were passed to the SP and then updates took place. Because we are a very high OLTP site, MS got me to modify the sp, to DECLARE local variables in the sp and assign the order number and revision passed to the SP to these variables. The variables took the place of the parameters thruout the SP. And guess what, the excessive recompilations stopped. So just a little experience I had with local variables. Hope this might help one of you someday. Derek.

Paul Paiva SSCrazy Points: 2237 More actions · Answer 8

Nice article covering some basic points, but here are 2 important points to hightlight:

A lot of my work day-to-day is fining previously written procedures that are slow and re-writing them more to be more efficient. I often end up converting all the table variables to temp tables and making indexes on the columns that are used in JOINs or WHERE or GROUP BY clauses in subsequent queries. This is especially germane when the row sizes are large. When I bench mark and compare the two methods, I'd say that 75% of the time the #temp tables are considerable faster especially when number of rows are great (100,000 or higher)

However, in the case of user-defined functions you have no choice but to use a table variable.

- Paul

http://paulpaivasql.blogspot.com/

Danni Afasyah SSC Journeyman Points: 98 More actions · Answer 9

Guys,

is it possible to use table variabel in dynamic sql, for example how to make this query works ?

DECLARE @xyz TABLE (

CustID varchar(50),

CustName varchar(50)

)

insert into @xyz (CustID, Custname) VALUES ('1','Name 1')

insert into @xyz (CustID, Custname) VALUES ('2','Name 2')

insert into @xyz (CustID, Custname) VALUES ('3','Name 3')

EXEC sp_executesql N'SELECT * FROM @xyz'

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 10

yes, it is POSSIBLE, but only by having the declaration of the variable, population of the table variable, and usage of the table variable, ALL INSIDE THE DYNAMIC SQL.

not something you want to be doing, though.....

Danni Afasyah SSC Journeyman Points: 98 More actions · Answer 11

Danni Afasyah

SSC Journeyman

Points: 98

November 23, 2006 at 7:32 pm

#673746

i see. thanks. im using temp table instead.

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 12

Table variables are indeed very slow when processing even moderately sized result sets. I have a proc that produces, in one use case, about 2200 rows. This table variable needs to be joined(or updated) with another couple of tables which can be large to get the desired results. The table variable with umpteen hours of tuning still gets the job done in about 16 seconds and the temp table version of the exact same proc gets it done in 2 seconds. Go figure ...

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 13

Guru Nagabhushan (9/22/2008)
Table variables are indeed very slow when processing even moderately sized result sets. I have a proc that produces, in one use case, about 2200 rows. This table variable needs to be joined(or updated) with another couple of tables which can be large to get the desired results. The table variable with umpteen hours of tuning still gets the job done in about 16 seconds and the temp table version of the exact same proc gets it done in 2 seconds. Go figure ...

That seems to tie in to my heuristic:

Wanderer (11/21/2006)
Colin,
You are completely correct that table variables are justly famous for big improvements on small resultsets, especially in savings on re-compliations on SPs. The question always seems to remain - what is the threshold at which you move from one to the other? Should we always write our queries in both forms, then performance test, and use the better version - I don't know.
At the moment, the hueristic I use is > 1000 rows, I look at using temp tables first, <= 1000 I look at table variables first. After that, if it is a frequently a case of deciding if it is an ad hoc query, or something that will be used with regularity.
I'm not sure why you think that temporary table, or table variables, wouldn't be used in complex queries, but that's a completely seperate thing.
Cheers

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 14

It is better to try both(temp tables and table variable) before deciding. A stored procedure I am working on requires two temporary structures. However, one of them needs rows to be inserted using a dynamic query and since table variables cant be used in a dynamic query ( uncless offcourse you create the table variable itself as a part of the dynamic query) I chose to use temporary table for that one while I used table variable for the other. But i did test both separately and inspite of the large number of rows, table variable gave me a lower processing time ( for about 5 million rows)

FredFlintst0ne Valued Member Points: 61 More actions · Answer 15

Here's a puzzling table variable speed behavior that I can't explain.

I have a user-defined table function that returns a small resultset (~200 rows).

The user-defined table function employs a GROUP BY in a query of a very large table

(say, 1,000,000 rows).

When I merely select from the user-defined table function,

the small resultset of ~200 rows is returned in ~5 seconds.

--psuedocode

SELECT myColumn

FROM udtReturningSmallResultsetFromGroupByOverLargeNumberOfRecords

(@myDateVariable)

However, when I try to insert this small resultset into a table variable,

the query below takes about 30 seconds

--psuedocode

DECLARE @tblVariable TABLE(myColumn varchar(50) NOT NULL)

INSERT@tblVariable(myColumn)

SELECT myColumn

FROM udtReturningSmallResultsetFromGroupByOverLargeNumberOfRecords

(@myDateVariable)

Does anyone have any ideas why inserting such a small resultset into a table variable (~200 records)

would cause a 6-fold slowdown? Lacking a good explanation, I can only guess that the presence of the table variable insert is somehow throwing off the optimizer for the user-defined table function select.