Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Table Variable :Doesn't care Expand / Collapse
Author
Message
Posted Tuesday, July 6, 2010 9:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,836, Visits: 3,952
Comments posted to this topic are about the item Table Variable :Doesn't care

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #948276
Posted Tuesday, July 6, 2010 10:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Nice question, thanks!

I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome.
Post #948288
Posted Tuesday, July 6, 2010 11:26 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
UMG Developer (7/6/2010)
Nice question, thanks!

I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome.


Or copy, paste, alt+x syndrome (F5 is to far away on the keyboard... and actually clicking execute is WAY out of the question).

Good question though.
Post #948301
Posted Tuesday, July 6, 2010 11:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,972, Visits: 369
these rollback type questions are my favourites...
thanks :)
Post #948305
Posted Tuesday, July 6, 2010 11:54 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 20, 2010 2:06 PM
Points: 683, Visits: 185
UMG Developer (7/6/2010)
Nice question, thanks!

I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome.


I didn't execute the code, but I was definitely helped by the title, "Table Variable: Doesn't Care"... All the same, it's a good question that's pointing out interesting and completely reasonable behavior that I never paid attention to before!
Post #948309
Posted Tuesday, July 6, 2010 11:55 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, Visits: 373

Good point, covered nicely. I like it.



KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
Post #948311
Posted Wednesday, July 7, 2010 12:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162


I didn't execute the code, but I was definitely helped by the title, "Table Variable: Doesn't Care"... All the same, it's a good question that's pointing out interesting and completely reasonable behavior that I never paid attention to before!


It's true.I also find the answer using question it self witout executing script.


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #948318
Posted Wednesday, July 7, 2010 12:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 13,300, Visits: 10,167
Nice question to point out the difference between temp table and table variable, but a little more explanation would be helpful (such as a link to a BOL article).



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #948335
Posted Wednesday, July 7, 2010 12:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
Nice question, thanks.

According to Books Online, "Transactions involving table variables last only for the duration of an update on the table variable." - Good to know! One of those things that makes sense when you think about it, but could easily catch you out.

Duncan
Post #948339
Posted Wednesday, July 7, 2010 1:24 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
Of course, one place this behaviour comes in useful is if you want to store the values from an output clause when rolling back a transaction. Just store them in a table variable first so they don't get lost when the transaction is rolled back.

Have a look here at Martin Bell's explanation, which I found useful.

Duncan
Post #948355
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse