Table variable does not take part in Transaction

Question

Table variable does not take part in Transaction

Viewing 5 posts - 16 through 19 (of 19 total)

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

WayneS SSC Guru Points: 95450 More actions · Answer 1

Gianluca Sartori (2/25/2011)
Sean Lange (2/25/2011)
...there is not a table to log against. This is no different than any other variable type.
Not true: operations on table variables are logged, but don't participate in transcations.

Which begs the questions:

1. WHY are table variables logged if they don't participate in transactions?

2. Is is possible to have data from a table variable rolled back?

(I'll come back shortly with the answers... and code to prove it...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

WayneS (2/25/2011)
Gianluca Sartori (2/25/2011)
Sean Lange (2/25/2011)
...there is not a table to log against. This is no different than any other variable type.
Not true: operations on table variables are logged, but don't participate in transcations.
Which begs the questions:
1. WHY are table variables logged if they don't participate in transactions?
2. Is is possible to have data from a table variable rolled back?
(I'll come back shortly with the answers... and code to prove it...)

Gianluca posted a link to Gail's blog that answers those questions. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95450 More actions · Answer 3

Sean Lange (2/25/2011)
WayneS (2/25/2011)
Gianluca Sartori (2/25/2011)
Sean Lange (2/25/2011)
...there is not a table to log against. This is no different than any other variable type.
Not true: operations on table variables are logged, but don't participate in transcations.
Which begs the questions:
1. WHY are table variables logged if they don't participate in transactions?
2. Is is possible to have data from a table variable rolled back?
(I'll come back shortly with the answers... and code to prove it...)
Gianluca posted a link to Gail's blog that answers those questions. 🙂

Sure enough... 😀

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

AJ07 Ten Centuries Points: 1334 More actions · Answer 4

Thanks Gianluca Sartori,

Its really a very good article, now I have most of the information about Table variable and Temp tables all proc and cons about them.

Regards,

AJ

AJ07 Ten Centuries Points: 1334 More actions · Answer 5

And thanks to all for your valuable inputs and time 🙂

Really I learned so much new today.

Regards,

AJ