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


Table Variables


Table Variables

Author
Message
shashi kant
shashi kant
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 547

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
Derek Wallace
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 1

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
Paul Paiva
SSC Eights!
SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)

Group: General Forum Members
Points: 873 Visits: 265

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



- Paul

http://paulpaivasql.blogspot.com/
Danni Afasyah
Danni Afasyah
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1
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
Regan Galbraith
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 265

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
Danni Afasyah
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1
i see. thanks. im using temp table instead.
sqlGDBA
sqlGDBA
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 315
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
Regan Galbraith
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 265
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
namrata.dhanawade-1143388
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 205
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
FredFlintst0ne
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
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.
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