Why there are temporary table and table variable concepts in SQL server, when both of them provide the same functionality.
As table variable and temporary table uses the temp DB and have almost identical features.
2
-
dba.stackexchange.com/questions/16385/…Kritner– Kritner2015-08-10 11:49:35 +00:00Commented Aug 10, 2015 at 11:49
-
stackoverflow.com/questions/27894/…A_Sk– A_Sk2015-08-10 11:50:00 +00:00Commented Aug 10, 2015 at 11:50
Add a comment
|
2 Answers
Though they look same, there are signficant differences.
Major differences
- You can create indexes on Temp table. But not on table variable.
- You can pass table variable as parameter, but that is not the case with temp table.
- you can have transactions working on temp table, but not on table variable.
there are other differences. Just google it.
Comments
One major difference is that you can't use temporary tables in user defined functions, but you can use table variables. I would assume that's because of the difference in scope -- and I would assume this is the reason why table variables have been implemented.
