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


Table variable does not take part in Transaction


Table variable does not take part in Transaction

Author
Message
AJ07
AJ07
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 198
Hi,

I am a bit confused about the table variables. As transaction does not affect the table variable operations. So is there any way to force table variable to be part of transaction or in such situations we'll have to use temp tables only?

Also when we submit a query having a table variable then the query optimizer always assumes only single row and the statistics are not generated for the table variable so I am curious that why table variables are better option over temp tables (not always)?


Regards,
AJUnsure
Gianluca Sartori
Gianluca Sartori
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16812 Visits: 13353
Check this great article by Wayne Sheffield:

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Sean Lange
Sean Lange
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43582 Visits: 17850
Variables are not part of a transaction. If you are using a table variable it will not be part of the transaction as there is not a table to log against. This is no different than any other variable type. If you want to be able to rollback changes during an operation you should use a temp table.


so I am curious that why table variables are better option over temp tables (not always)?


As with everything in sql the correct answer is "it depends". In this case a temp table is probably the better choice.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jason-299789
Jason-299789
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3417 Visits: 3232
In my opinion a Table varialbe is great for very small data sets (1 or two columns, and less than 100 rows), any more than that and you should consider a temp table.

Also consider that Table variables actually end up as unindexed tables in the tempdb anyway, as demonstrated below.


Declare @TableVariable Table (DT DateTime Default GetDate() NOT NULL)
Insert Into @TableVariable DefaultValues

WaitFor DELAY '00:00:10'

Create Table #TempTable (DT DateTime Default GetDate() NOT NULL)
Insert Into #TempTable Defauly Values

Select DT as TableVariable From @TableVariable
Select DT as TempTable From @TempTable

Select * from tempdb.sys.objects
Where type='U'
and create_date between DateAdd(minute,-1,GetDate()) and GetDate()
Drop Table #TempTable



Its best to use this on local or development box where the useage is almost non-existant.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Sean Lange
Sean Lange
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43582 Visits: 17850
AJ-636201 (2/25/2011)
Hi,

I am a bit confused about the table variables. As transaction does not affect the table variable operations. So is there any way to force table variable to be part of transaction or in such situations we'll have to use temp tables only?

Also when we submit a query having a table variable then the query optimizer always assumes only single row and the statistics are not generated for the table variable so I am curious that why table variables are better option over temp tables (not always)?


Regards,
AJUnsure


I was thinking that I had read a good article about this topic not long ago. Smile

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Gianluca Sartori
Gianluca Sartori
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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

Jason-299789 (2/25/2011)
Also consider that Table variables actually end up as unindexed tables in the tempdb anyway...


Not true: add a primary key or unique costraint and you can add indexes to a table variable.

Here's a great post by Gail Shaw on both myths: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Jason-299789
Jason-299789
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

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

Jason-299789 (2/25/2011)
Also consider that Table variables actually end up as unindexed tables in the tempdb anyway...


Not true: add a primary key or unique costraint and you can add indexes to a table variable.

Here's a great post by Gail Shaw on both myths: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/


I stand corrected on that point, I was pulled up by a senior on a code review several years ago when he caught me using table variables, and he insisted I revisit all my code and change the table variables to Temp tables.

ps : thanks for the link, its a good article.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Carlton Leach
Carlton Leach
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1318 Visits: 1303
My 5 cents is always use a temp table. It behaves more like a real table than a table variable does.

Carlton.
Sean Lange
Sean Lange
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43582 Visits: 17850
Carlton Leach (2/25/2011)
My 5 cents is always use a temp table. It behaves more like a real table than a table variable does.

Carlton.


There are perfectly valid reasons to use a table variable instead of a temp table. One thing about sql that never changes is that the word "always" is never 100% accurate. Read the first article referenced and you can see clearly that in some situations a variable would be preferred over a temp table and vice versa.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

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



Ah yes. I guess I wasn't really thinking about if the changes were actually in the log or not since the end result is that it is not in the transaction. Would only make sense that any data changes would have to be logged. I guess I was trying to over simplify it in my head.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
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