May 26, 2013 at 8:00 pm
Do materialized views and indexed views exist in tempdb or the buffer, correct? They could be in the buffer if the view were small, right?
I think they usually reside in tempdb. Can someone tell me exactly where they live?
May 26, 2013 at 8:29 pm
Materialized views are not stored in the buffer cached (unless records are needed by a query) and are not materialized in the tempdb.
When you create an index on a view (thus making it a materialized view), the index (and the data) are created in the default filegroup (unless otherwise specified) of the database in which the view is materialized.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 27, 2013 at 3:01 am
Golfer22 (5/26/2013)
Do materialized views and indexed views exist in tempdb or the buffer, correct? They could be in the buffer if the view were small, right?
Neither
I think they usually reside in tempdb. Can someone tell me exactly where they live?
Not in TempDB.
A view is just a saved select statement. It's stored in the system tables of the database it's in. When the view is queried, as part of the parsing of the query using the view, the name of the view is replaced by the definition and the resultant query optimised and executed.
An indexed view (or materialised view, they're the same thing) is stored like the clustered index of a normal table in the database that it's created in (with its definition in the system tables). When an indexed view is queried, if the optimiser decides to use the index on the view, then it's just like querying any permanent table.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply