Table Variables

Question

Table Variables

Viewing 10 posts - 16 through 24 (of 24 total)

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

Mike B in AK SSC Eights! Points: 900 More actions · Answer 1

While embedding an insert into a table variable in a transaction is valid and would function, the way it is in the question led me to check it, along with the two correct choices.

While I knew it would not fail if tested, it was a pointless option because it included extraneous code for inserting records into a table variable. If the question had inserts, updates or deletes into a temp or permanent table as part of the answer, I probably would not have checked it.

But, the end result is the statement would execute cleanly, so I will take my lumps and move on.

Coyote Blue SSC-Addicted Points: 486 More actions · Answer 2

I did the SAME THING... 'hmm,,, looks like two are wrong ... but which is MORE wrong...' :pinch:

And I am relatively new to tsql; only been doing 'hard' sql in my career for about 6 months (with no formal training); so I was *happy* I was 1/2 right!:D

srienstr SSCrazy Points: 2435 More actions · Answer 3

GSquared (1/31/2008)
The only IT-specific definition of "valid" that I can find in any dictionary online is "A data-flow computer language", in which it is a proper noun.
If we're dealing with made up words here, with no formal/accepted definition, then I can validly say that your definition of "valid" is, in fact, invalid. 🙂
Unless, of course, you can provide a source for that "specific contextual meaning". Can you?
(I do have actual dictionary definitions for the SQL use of words such as "integer", "character", "return", etc. Very easy to find those. "Valid", however, does not appear to have a specific SQL meaning, nor a meaning generic to anything related to error messages from code compilation/use.)

These two sites show a similar definition to how my Computer Science professors used the term "valid code". They essentially state that valid code is code that meets the standards defined for the language, and thus will compile and run. This leaves the question of whether it did what you intended as a matter of how well it is coded.

Then again, I'm basing this on more general coding framework, so A) I was overly specific in my prior statement, and B) I was assuming that what I had learned as the general usage also applied to the SQL environment. Perhaps database programmers use different terminology; I haven't been involved in the SQL community long enough to know for sure. Had this been an application programming or web design forum, you'd be laughed at, but here I am the novice.

Whether I'm right or wrong, (the latter being more common, if you ask my wife) I thank you for helping me realize that I can't just blithely assume that terms I know from other coding I've done are being used in the same way here. It's more time consuming to verify one's understanding each time, but I prefer right to quick.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

JB-117464 Ten Centuries Points: 1279 More actions · Answer 4

Following on from Jim Fosters comment about the "BEGIN TRANSACTION; INSERT INTO @Vendors VALUES (1, 'Wicked Widgets Inc.', 'A');ROLLBACK TRANSACTION" statement. I ticked this as not being valid. This is clearly not the intended outcome and although it produces no syntax or runtime errors it should fall under the "NOT valid statements" category.

I would let it slide if there wasn't an identical statement without the rollback transaction which i think is designed to emphasise the difference. The only difference between the two is the rollback transaction which is not valid and should really give the following error "Msg 3903, Level 16, State 1, Line 1. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

SG_Explorer Ten Centuries Points: 1339 More actions · Answer 5

I tested this & not satisfied the answer. Please provide with an valid example.

Mohamed-401375 SSC Eights! Points: 965 More actions · Answer 6

Good Question. Table variables do not honor transaction, but are valid. This option caught me off guard.

BAD-DBA-DAB SSCommitted Points: 1966 More actions · Answer 7

hi i performed truncate table in sql server 2005 & i was successful but it just throws an alert not an error as u have mentioned

cwcridindirty Valued Member Points: 56 More actions · Answer 8

cwcridindirty

Valued Member

Points: 56

August 27, 2010 at 3:28 pm

#1213295

Table variables can be truncated.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

cwcridindirty (8/27/2010)
Table variables can be truncated.

Are you sure?

DECLARE @Vendors TABLE

(VendorPK int,

VendorName varchar(50),

VendorStatus char(1));

TRUNCATE TABLE @Vendors;

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '@Vendors'.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

cwcridindirty Valued Member Points: 56 More actions · Answer 10

You're right. At first, I was too focused and thought this was in reference to regular variables assigned with a Table name, not actual Table Variables. 🙂