Link to home
Start Free TrialLog in
Avatar of SaiSiva08
SaiSiva08Flag for United States of America

asked on

What are the differences between a table variable, a temp table and a derived table?

Hi ,

I would like to know  differences between a table variable, a temp table and a derived table?
ASKER CERTIFIED SOLUTION
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A table variable just works like a static variable i.e. it scope lies within a certain block of execution whereas temp tables has a scope for the session unless it is dropped.
Table variables are recommended for holding very less data i.e. (10 KB) approx as it doesn't have statisctics build on it. So, it makes the performance of the query to be slow whereas temp tables are far beyond that. You can make its use whereever and whenever required but  its over usage creates problem with SGAM of tempdb and you will start seeing problem as 2:1:103.
Any update on this question?
As a brand new member, I suspect she has not got the "hang of things".  In other words, she does not realize that appropriate feedback can enhance or re-focus the question.