Tuesday, 11 August 2015

Interview Question - What is the difference between Table Variable and Temp Table ?

Few days back, one of my colleague asked me about the difference between Table variable and Temp table. Although I did manage to tell him few difference that got him going but that answer did not satisfy me. So to settle the nerve I ended up reading about them and got to know few more differences between table variable and temp table. 

Then it occurred to me that it's good candidate for the blog entry as this question fielded quite often during screening process. 

Below given screenshot shows the difference between the Table variable and Temp table

First points from the differences are self explanatory hence I won't give an example for only selected few can be verified

De-allocation : 

As you can see in the screenshot below I am trying to create 2 table variables and 2 temp tables in two separate sessions but with the same name. One succeeds while second one fails

Identity column :

In order to insert the value into identity column explicitly we need to specify IDENTITY_INSERT ON for the particular table. But it does not work with table variable at all which implies that it does not allow explicit insert to be carried out against the INDENTITY column

Transaction :

In the below queries we have created temp table and table variable inside transaction. And we ROLLBACK the transaction which effectively means that any statement referencing it outside transaction should fail. But it does not fail in case of table variable as it did return result for the next SELECT which suggests that ROLLBACK did not work for it. 

No comments:

Post a Comment