views

it is easy, or relatively so, to query a database table.  

Select * from table where something ....

If you need to query multiple tables, it becomes more complex and if you have to join 5 tables, it can be annoying.  And if you do it from scratch each time?

Or you can create a view of the join of the five tables (on time) and then do the query on the one table, or rather the one view.

Create VIEW vMyView with schemabinding as (Select * from whatever, join however where however)

then

select * from vMyVIew where condition...

You don’t have to Select * to create the view,  you can hide\not show whatever you want and let other people use the view for their purposes.  

Each base table in the view will be involved each time the view is queried.  They should have indexes on them, just like always.  Because a table is involved in a view doesn’t affect where or how it is used everywhere else for maintenance, indexes, backups, et al.

Now, a view is defined as a virtualized table created from a saved query.  We can add indexes (clustered, then nonclustered) to a view,  However if we made a CI, the view becomes is physical,  not virtual and is “materialized’ to disk .  Also, depending on the fields referenced in the index, it might be updated each time any of the base tables in the view change.   

Indexed views are especially valuable for calculating complex aggregations of data.  Instead of running them on the base tables directly, we use the view to determine the aggregations.  

Advertisements