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


Table Variables


Table Variables

Author
Message
Chad Crawford
 Chad Crawford
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6033 Visits: 18732
Comments posted to this topic are about the item Table Variables
Dr. Diana Dee
Dr. Diana Dee
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: 3615 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27310 Visits: 12736
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
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7181 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 Visits: 945
I chose 'TRUNCATE TABLE' and was told I was wrong, the correct answer is 'TRUNCATE TABLE'...
Glen Weldon Maney
Glen Weldon Maney
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 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
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 495
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.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: 3615 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 (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 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 (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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