"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

November 05, 2010

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.

Happy Learning!!!

No comments: