SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video

Today’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Misconception and Resolution.

Quite often I have seen people getting confused with certain behavior of the T-SQL. They expect SQL to behave certain way and SQL Server behave differently. This kind of issue often creates confusion and frustration. Sometime I have seen them also confusing it with bug and submitting the bug, where reality is totally different. Similar concept which are going to see today. I have seen quite commonly developer assuming that table various will be rolled back when transaction is rolled back. This sixty seconds video describes that table various are not rolled back when transactions are rolled back.

More on Errors:
Difference Temp Table and Table Variable – Effect of Transaction
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

4 thoughts on “SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video

  1. @Pinal: Thank you so much for putting some light on this. I noticed this behaviour just 2 week ago and trying to figure out why this is the case inspite of both the tables getting created in tempdb.tables
    I also observed that any local variable declared on the same scenario works like local table variable. Please let me know WHY this is the case. Is it the case that Transactions are associated with connection and scope of Temp table is wrt connection while table variable is local to the scope ?

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s