SQL Server 2005 Views

[GaryMazzone]

I have a question on view behavior in SQL Server.

Lets say I have a proc that is based on some views.

We start with View1 Which is based on View2 and View 3 and View4. View 2 is also based in part on View2 as is View 4.

Does View2 get repopulated for each or do all simply access the data in the View2 after is is called in View1?

Gary

[brucevde]

A standard View is simply a query that creates a "virtual table". The data it returns is not stored in the database so the View's query is executed everytime it is referenced. It really is no different than executing a statement such as

Select * From (Select * From Table2 Where ...) As Data Where...

With the scenario you described though, SQL Server will try and optimize all the queries from all the Views into a single execution plan.

However, if you create an Index on a View then the View's data is stored in the database. Now whenever the View is referenced only the View's data needs to be queried. But don't go and add an Index to every View because each update/insert into one of the underlying tables in a View's query requires the need to refresh the View's data (sql server does this automatically).

[Shuja Ali]

Indexed views should be created carefully. And remember when you create an Indexed view, the underlying tables cannot be changed (altered in anyway) unless the dependency of the view is removed.

[GaryMazzone]

Yes and the reading I have done says you can't create the indexed view when using Outer Joins..... 3 of the 4 views involved use Left Outer Join Isn't that fun for performance.