Table variables are not "in memory" tables, they function basically the same as #temporary tables and even create objects in tempdb.
The difference is that table variables are "lightweight" temporary tables in that they exist only for the scope of the batch and there are no statistics associated with them (the optimizer assumes only one row in table). Also, you cannot create indexes on them (aside from primary key and unique constraints).
So, table variables can improve performance and take up less resources in situations with small data sets, however larger data sets can have better performance as temporary tables.
No comments:
Post a Comment