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 123»»»

Table Variables Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 9:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:37 PM
Points: 2,391, Visits: 17,929
Comments posted to this topic are about the item Table Variables
Post #449791
Posted Thursday, January 31, 2008 12:14 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, August 28, 2014 8:11 AM
Points: 3,143, Visits: 137
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.
)
Post #449829
Posted Thursday, January 31, 2008 1:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #449836
Posted Thursday, January 31, 2008 1:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #449840
Posted Thursday, January 31, 2008 4:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
Points: 530, Visits: 945
I chose 'TRUNCATE TABLE' and was told I was wrong, the correct answer is 'TRUNCATE TABLE'...
Post #449871
Posted Thursday, January 31, 2008 6:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:15 AM
Points: 377, Visits: 370
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
Post #449902
Posted Thursday, January 31, 2008 8:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, 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.
Post #449963
Posted Thursday, January 31, 2008 8:12 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, August 28, 2014 8:11 AM
Points: 3,143, Visits: 137
A tried executing TRUNCATE TABLE against a table variable and did get an "incorrect syntax" error.

There is nothing like experimentation!

)
Post #449967
Posted Thursday, January 31, 2008 8:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 4, 2010 9:46 AM
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.
Post #449984
Posted Thursday, January 31, 2008 8:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:00 AM
Points: 182, Visits: 69
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 ;)
Post #449995
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse