SQL Server Views Revisited

It's been a long time I refreshed on my SQL Skills. Below post is on views.

Why view ?

• Join Several Tables and Fetch Information
• Hide the Query from End-users,Ex-Expose only aggregated results, Read-Only Information presented to users
• Expose only required/relevant information to users
• View with schemabinging -  When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition 
• View can update underlying tables if it satisfies below conditions
• View is created based on only one table
• No Aggregate function in view definition (Reference)
• List all view in DB
• select * from sysobjects where xtype = 'v'

How a View Works -

• Every time a query references a view, SQL Server substitutes the definition of the view into the query internally until a modified query is formed that only references base tables. Thus, Indexes on underlying tables are critical for performance of views
• When a view is executed for the first time, only its query tree is stored in the procedure cache
• Each time a view is accessed, its execution plan is recompiled.   

Partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables
Indexed Views - SQL Server allows creation of index on view. Steps required for creating indexed views provided in link, link1. Best for OLAP systems where data is updated infrequently.

Views Examples - Creating Indexed Views, Create View, Good Distributed Partitioned Views / Federated Databases Article, An Inside View

Happy Learning!!!