January 30, 2008 at 9:27 pm
Comments posted to this topic are about the item Table Variables
January 31, 2008 at 12:14 am
I combed the msdn article cited from top to bottom twice, and found no mention that truncate was not allowed.
I'd appreciate the location and exact quote.
Thanks.
):-D
January 31, 2008 at 1:19 am
Dr. Diana Dee (1/31/2008)
I'd appreciate the location and exact quote.
Hi D,
The relevant quote on the referenced page is: "It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements." Neither CREATE INDEX nor TRUNCATE TABLE is included in this limited enumeration of places where a table variable can be used.
Another place where this is (somewhat) documented, is in the Books Online articel on TRUNCATE TABLE, where the syntax diagram shows that you need to supply a table name, not a table variable.
In case you now want to write that this should be better documented, I fully agree with that. Don't shoot the messenger 🙂
January 31, 2008 at 1:59 am
This question is kind of mean.
It makes you think that maybe the author did not really mean to ask for "valid SQL", but "SQL that makes sense".
So I guessed that the question was "misworded" and also chose the TRANSACTION answer since it doesn't make sense (while it is still "valid" SQL):
Table Variables are not affected by transactions.
Best Regards,
Chris Büttner
January 31, 2008 at 4:35 am
I chose 'TRUNCATE TABLE' and was told I was wrong, the correct answer is 'TRUNCATE TABLE'...
January 31, 2008 at 6:35 am
http://msdn2.microsoft.com/en-us/library/aa175774(SQL.80).aspx
Although you can't do a CREATE INDEX against a table variable, you can have an index created behind the scenes when you declare the table variable with a PRIMARY KEY constraint
January 31, 2008 at 8:04 am
Jon Spink (1/31/2008)
I chose 'TRUNCATE TABLE' and was told I was wrong, the correct answer is 'TRUNCATE TABLE'...
I chose the index one, and was also partly right. I too missed that they were check boxes rather than simple multiple choice.
January 31, 2008 at 8:12 am
A tried executing TRUNCATE TABLE against a table variable and did get an "incorrect syntax" error.
There is nothing like experimentation!
):-D
January 31, 2008 at 8:27 am
One other thing also is that although
BEGIN TRANSACTION; INSERT INTO @Vendors VALUES (1, 'Wicked Widgets Inc.', 'A'); ROLLBACK TRANSACTION
does not generate any syntax errors, the table variable does NOT participate in the transaction. This can be seen by executing
BEGIN TRANSACTION; INSERT INTO @Vendors VALUES (1, 'Wicked Widgets Inc.', 'A'); ROLLBACK TRANSACTION; SELECT * FROM @Vendors;
In practice, I have been able to use this to my advantage when I need to keep a list of business rule errors found in the processing of a SP and then doing a rollback. My errors are still contained in the table variable.
January 31, 2008 at 8:39 am
How mean - I spent ages trying to decide between two answers because I did not realise I needed two!
So, I failed even though my answer was one of the correct ones.
I guess that teaches me to read the instructions next time 😉
January 31, 2008 at 11:11 am
Thank you very much for your comments and your critique. I knew the question was difficult, and perhaps meant it to be somewhat tricky (using the transaction is valid, but does not perform as you might at first expect... unless you are Jim). However I did not intend for it to be confusing or unclear. I apologize if there was part that did not make sense on the first read. I hoped most people would take the SQL statements from the question and try to run them, similar to what Diana did. Using the exact statements provided, two will execute and two will not. I do appreciate the feedback (both above and hopefully below this post), and will incorporate your suggestions in future articles and QODs.
Thanks!
Chad
January 31, 2008 at 12:14 pm
Hi Chad,
I'll just chime in here and say that I like the question very much. The only thing that could have imoproved it was if there had been an explicit statement "check all that pply" somewhere - now, I was in doubt and just tried if I could check more than one.
I had to try out one of the four options, because I had some doubts. The other three, I knew for sure. So I learned something today - and isn't that the aim of the QotD, rather than amassing points?
January 31, 2008 at 12:17 pm
I disagree with the point about the transactions.
Dictionary definition of "valid": "well based or logical" (Compact Oxford English Dictionary, as referenced on http://www.onelook.com).
Doing a transaction and rollback on a table variable is very much not well based or logical, since it does not do what one would probably expect. Since there are no other actions in the "transaction", the code makes no sense if one knows that it won't do anything.
If the intention of the question was, "Which of these will raise errors or fail to compile?", then the answers given would be correct, since it won't raise an error. But I certainly don't consider wrapping an insert into a table variable in a transaction to be "valid" code. If raising errors was the intent of the question, then it should have said that and avoided the word "valid".
(I feel like Inigo in "The Princess Bride": "I do not think that word means what you think it means.")
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2008 at 12:53 pm
GSquared (1/31/2008)
I disagree with the point about the transactions.Dictionary definition of "valid": "well based or logical" (Compact Oxford English Dictionary, as referenced on http://www.onelook.com).
Doing a transaction and rollback on a table variable is very much not well based or logical, since it does not do what one would probably expect. Since there are no other actions in the "transaction", the code makes no sense if one knows that it won't do anything.
If the intention of the question was, "Which of these will raise errors or fail to compile?", then the answers given would be correct, since it won't raise an error. But I certainly don't consider wrapping an insert into a table variable in a transaction to be "valid" code. If raising errors was the intent of the question, then it should have said that and avoided the word "valid".
(I feel like Inigo in "The Princess Bride": "I do not think that word means what you think it means.")
The term "invalid" has a specific contextual meaning in regard to SQL which is different from the standard dictionary definition, much as the definition of "character" in SQL is much more rigid than you will find in the dictionary and "integer" is much more limited than the definition in a mathematics textbook. His usage of "valid" is both accurate and precise within the framework in which he used it.
January 31, 2008 at 2:33 pm
srienstr (1/31/2008)
GSquared (1/31/2008)
I disagree with the point about the transactions.Dictionary definition of "valid": "well based or logical" (Compact Oxford English Dictionary, as referenced on http://www.onelook.com).
Doing a transaction and rollback on a table variable is very much not well based or logical, since it does not do what one would probably expect. Since there are no other actions in the "transaction", the code makes no sense if one knows that it won't do anything.
If the intention of the question was, "Which of these will raise errors or fail to compile?", then the answers given would be correct, since it won't raise an error. But I certainly don't consider wrapping an insert into a table variable in a transaction to be "valid" code. If raising errors was the intent of the question, then it should have said that and avoided the word "valid".
(I feel like Inigo in "The Princess Bride": "I do not think that word means what you think it means.")
The term "invalid" has a specific contextual meaning in regard to SQL which is different from the standard dictionary definition, much as the definition of "character" in SQL is much more rigid than you will find in the dictionary and "integer" is much more limited than the definition in a mathematics textbook. His usage of "valid" is both accurate and precise within the framework in which he used it.
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.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply