SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables


Table Variables

Author
Message
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1925 Visits: 711

Mark,

I've found that UDF's that return table variables, or larger data sets tend to be awful slow, since the returned 'table' ends up not using any indices. If you can avoid a UDF that returns a dataset (table) I would do so!

Mark


Mark Yelton
Mark Yelton
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 433
There is an initiative here to get rid of the UDFs and rewrite them and make them stored procedures. I can thankfully say this app was before my time here at my current position.
Bill Zanzinger-363443
Bill Zanzinger-363443
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

Another consideration when using Table Variables is parallelism: Temp Tables support it, Table Variables don't. This can be significant in some environments.


Jambu Krishnamurthy
Jambu Krishnamurthy
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 98

one clarification:

this is what I meant to say. You cannot use this construct to get the output of a stored procedure into a table variable.


declare @table_variable table(f1 varchar(100), f2 varchar(100))
INSERT INTO @table_variable EXEC sp_depends 'anyTableName'

Server: Msg 197, Level 15, State 1, Line 2
EXECUTE cannot be used as a source when inserting into a table variable.


sorry for the confusion.
jambu


Regan Galbraith
Regan Galbraith
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 265
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
Patricia Schiefelbein
Patricia Schiefelbein
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 6

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
Carlos Urbina
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 1
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
SuperDBA-207096
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1925 Visits: 711

Jambu,

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

Mark


the sqlist
the sqlist
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 724

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 (  t1      nchar(128),  t2      nchar(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
Regan Galbraith
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 265
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search