SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables


Table Variables

Author
Message
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2842 Visits: 18717
Comments posted to this topic are about the item Table Variables
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3251 Visits: 143
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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11042 Visits: 11996
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 :-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3587 Visits: 3889
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
Jon Spink
Jon Spink
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 945
I chose 'TRUNCATE TABLE' and was told I was wrong, the correct answer is 'TRUNCATE TABLE'...
Glen Weldon Maney
Glen Weldon Maney
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 392
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
srienstr
srienstr
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 433
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.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3251 Visits: 143
A tried executing TRUNCATE TABLE against a table variable and did get an "incorrect syntax" error.

There is nothing like experimentation!

):-D
Jim Foster
Jim Foster
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 43
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.
Jayeff
Jayeff
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 84
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 Wink
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search