January 25, 2009 at 5:09 pm
hi frnds i want to know why does temporary tables in a stored procedure are recompiled more number of times than table variables in a stored proc. i want to know that difference in detail.. please post me any relevant data or info to
regards
abhi
January 26, 2009 at 12:35 am
Please post in the appropriate forum in the future.
Temp tables are treated the same way (almost) as permanent tables. So creation of a temp table will force a proc to recompile, changes in the statistics will also force a proc to recompile. It's done so that the query is assured of having an accurate plan.
Since table variables don't have statistics at all, they can't force a proc recompile due to stats changes. The downside to that is because they don't have statistics, the plans created for them may be less accurate, especially for larger resultsets.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply