December 9, 2009 at 9:01 am
I know this is very simple and basic question but I want to confirm
Is view uses tempdb to store its result ?
The question came to my mind when I have created a Stored Procedure and instead of using table directly in it. I have created many views and used it .I have observed that the tempdb size is growing when I am running Procedure. your comments are welcome.
December 9, 2009 at 9:04 am
Views don't store results. They're simply saved select statements that SQL unrolls into the query when they are referenced.
TempDB is used for Temp tables, table variables, internal work tables (resulting from hash joins, hash aggregates, sorts and some other query operations) and for the Row Version store, and probably a few more internal things.
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
December 9, 2009 at 9:34 am
That means if I have some hash join or something in the view....it will store those intermediate result into tempdb......
Thanks for your reply
December 9, 2009 at 9:40 am
Only when you run the query that references the view.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply