May 15, 2012 at 9:01 am
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...
May 15, 2012 at 9:05 am
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.
May 15, 2012 at 9:11 am
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:
May 15, 2012 at 9:16 am
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.
May 15, 2012 at 9:47 am
...
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...
May 15, 2012 at 7:53 pm
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
Change is inevitable... Change for the better is not.
May 16, 2012 at 5:19 am
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
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply