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.
[youtube=https://www.youtube.com/watch?v=mekifucLXYI]
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 (https://blog.sqlauthority.com)
4 Comments. Leave new
@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 ?
Thank you. I’ve learnt this from you!
gr8! reference
Hey Pinal,
I would require your inputs on one of the issue which I encounter’s at random interval.
Issue Is with the SP logic. There are 2 Tables one is Master Table ‘Day’ which has day wise entry and other is the transaction table ‘Date’ which is date wise. The SP logic is implemented using Table Variable and CTE.
SP Logic is as follows:
1) I am checking, if the date has an entry in the transaction table for that date. If not find any entry for that date then I am removing the day of that date by Date function and inserting the data of that day which is taken from DAY(Master) table and inserting into Table Variable
2) After inserting into table variable, Further I insert into Transaction table row by row.
3)After the insertion process , I have used CTE for displaying the result.
Now the challenge is like this… At the time time of insertion from master to table variable or table variable to transaction table, A column of Nvarchar Datatype is getting null,however master table ‘DAY’ has the entry for that column. This issue happens randomly and I am unable to trace it where it is causing the issue.
If you can help me with this at the earliest that will be more helpful for me…
Thanks in advance..:-)