How to load a temp table with the result set of a sproc

Question

How to load a temp table with the result set of a sproc

Viewing 7 posts - 16 through 21 (of 21 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

And that wouldn't be needed if MS could implement proper autonomous transactions functionality in SQL Server...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Eugene Elutin (5/15/2012)
Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
And that wouldn't be needed if MS could implement proper autonomous transactions functionality in SQL Server...

Not sure that Microsoft should try to implement everything that Oracle has.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Lynn Pettis (5/15/2012)
Eugene Elutin (5/15/2012)
Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
And that wouldn't be needed if MS could implement proper autonomous transactions functionality in SQL Server...
Not sure that Microsoft should try to implement everything that Oracle has.

I haven't asked for everything, but the above, DECODE function and concept of packages (with package scope variables) would be great... 😉

Also, MS don't really need to try. I thought it has few of former ORACLE devs employed...:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Eugene Elutin (5/15/2012)
Lynn Pettis (5/15/2012)
Eugene Elutin (5/15/2012)
Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
And that wouldn't be needed if MS could implement proper autonomous transactions functionality in SQL Server...
Not sure that Microsoft should try to implement everything that Oracle has.
I haven't asked for everything, but the above, DECODE function and concept of packages (with package scope variables) would be great... 😉
Also, MS don't really need to try. I thought it has few of former ORACLE devs employed...:hehe:

Don't need the DECODE function, it was confusing to me, found use CASE WHEN more readable if verbose. Packages, just reminds me of ADA and after working with SQL Server for over 15 years I don't see much benefit from the added complexity.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

...
Don't need the DECODE function, it was confusing to me, found use CASE WHEN more readable if verbose. Packages, just reminds me of ADA and after working with SQL Server for over 15 years I don't see much benefit from the added complexity.
...

M-da...

From one side: based on my "confusions", the only programming language which should be allowed to exist would be Assembly, as it hard to find more readable language isn't it? Check this one:

MINICOPY CSECT
USING *,12
SAVE (14,12),,*
LR 12,15
LA 15,SAVEAREA
ST 15,8(,13)
ST 13,4(,15)
LR 13,15
OPEN (INDCB,INPUT,
OUTDCB,OUTPUT)
LOOP GET INDCB
LR 0,1
PUT OUTDCB,(0)
B LOOP
EOF CLOSE (INDCB,,OUTDCB)
L 13,4(,13)
RETURN (14,12),T,RC=0
SAVEAREA DC 18F'0'
INDCB DCB DSORG=PS,MACRF=GL,
DDNAME=INPUT,
EODAD=EOF
OUTDCB DCB DSORG=PS,MACRF=PM,
DDNAME=OUTPUT
END MINICOPY

Very neat! That what I call "non-confusing beauty" of coding... Just love it! :w00t:

From another one: If they try to keep everything simple we would still be using punch-cards.

:hehe:

Time to go home for me...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Geoff A (5/15/2012)
absolutely. and i know when you say "couple of sandwiches", you mean "a few beers". I'm there, buddy. 😛

Well, it does have about the same amount of yeast in it! Soon as I get off the meds for this bloody bronchitis, I'll give you a heads up for a hydraulic lunch. Looking forward to it! Maybe we can even get Ron to sponsor the event! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

chandan_jha18 SSChampion Points: 11330 More actions · Answer 7

Initially I was always fascinated by table variables till I found some problems. When I started my new job 2 years back, I was given the task first up to tune a procedure which used to run in 5-10 seconds but not takes around 10 minutes or so.

With just 3 years of experience, I did not like the welcome given but had to do something. I kept following google to all posts. One webpost said that the table variables do not perform greatly in SQL2005 onwards and I realized that these guys migrated from 2000 to 2005 a month back. So I followed the suggestion and changed that procedure to use temp tables and things were smooth. I got a pat on my back.

What I did not realize that the solution worked but I actually did not have a great reason for this and nobody wants to tell their client that the trick was just by chance. They do not like to hear such a thing.

Then few months back after reading a lot of posts on this website, I drew the conclusion that temporary variables may work good if you are putting less amount of data(how less is less,not sure??). With increase in data load, the query plan may suck because it does not keep statistics on temporary variables. and temporary tables can have this advantage because their statistics can be used and you can also have non-clustered index on it.

So I decided that whenever a solution work or does not work, I always try to find reasons for both scenarios and then SQLServerCentral contributors come to my rescue.

I hope I did not bore anyone with a long story:-D

Thanks

Chandan