Indexed Views -- An indexed view is a view that has a unique clustered index created on it. Normally the records of view don’t exist on the disk, whereas in Indexed views the rows are stored on the disk. Since the indexed view exists on the disk the disk space was taken by view.
We can use this Indexed views while writing query with a complex join on large amount of data. There will be good performance while retrieving the data because it exists on the disk and joins and aggregations are already defined in advance of running the query.
Limitations for Indexed views
Tables must be referred only thru two-part names.
All the tables must reside on the same database
The view must be created with SCHEMABINDING option
The ANSI_NULLS must set to ON.
The select statement cannot contain Union, TOP, Distinct, Orderby, count(*) etc.,
SCHEMABINDING – This locks the underlying tables preventing schema changes being made to the tables that would affect the view. We cannot rename or drop the fields from the table which are referred in the indexed view.
We can add clustered index to view only after creating the view not possible at the time of creation. This makes SQL to materialize the view to disk.
Example
The above statement creates a view with name sales_view_indexed on the database. As discussed above we need to add clustered index to it, otherwise only metadata will be stored on the view just like a normal view, you can verify it by using the below command
As discussed Indexed view is materialized and it has data on it after adding index to it, If you run the below command it should refer only view, but if you execute the below command it refers the physical tables like products and sales order details table. (see the execution plan)
To make use of Indexed view we need to give command as below
NOEXPAND – By specifying this key at select statement it fetches records directly from the indexed view without touching the base tables. Like this we can increase the performance of specific queries which has lot of joins and large amount of data on it.
Advantages
Improves the performance of SELECT statements
Indexes and data on multiple tables
Avoid complex joins and aggregations at run-time
Disadvantages
Increased disk space used by the database as the views are created with physical data.
Slows down the performance of the insert, update and delete statements on the tables used in indexed views.
Online re-index is not supported in indexed views.
High number of updates will not serve the benefit.
We can use this Indexed views while writing query with a complex join on large amount of data. There will be good performance while retrieving the data because it exists on the disk and joins and aggregations are already defined in advance of running the query.
Limitations for Indexed views
Tables must be referred only thru two-part names.
All the tables must reside on the same database
The view must be created with SCHEMABINDING option
The ANSI_NULLS must set to ON.
The select statement cannot contain Union, TOP, Distinct, Orderby, count(*) etc.,
SCHEMABINDING – This locks the underlying tables preventing schema changes being made to the tables that would affect the view. We cannot rename or drop the fields from the table which are referred in the indexed view.
We can add clustered index to view only after creating the view not possible at the time of creation. This makes SQL to materialize the view to disk.
Example
create view sales_view_indexed with
schemabinding as
select pp.Name, pp.ProductID, sd.SalesOrderID, sd.SalesOrderDetailID, sd.CarrierTrackingNumber, sd.OrderQty, sd.UnitPrice, convert(money,(sd.UnitPrice * sd.OrderQty * (1 - sd.UnitPriceDiscount)/100)*100) discount_price from sales.SalesOrderDetail sd ,
production.Product pp where pp.ProductID =
sd.ProductID
The above statement creates a view with name sales_view_indexed on the database. As discussed above we need to add clustered index to it, otherwise only metadata will be stored on the view just like a normal view, you can verify it by using the below command
sp_spaceused
sales_view_indexed
create unique clustered
index idx_SalesOrderDetailID On sales_view_indexed (SalesOrderDetailID)
After running the above command it creates index on the view and becomes materialize, Now examine the space you will identify it occupied space on the database.
sp_spaceused
sales_view_indexed
As discussed Indexed view is materialized and it has data on it after adding index to it, If you run the below command it should refer only view, but if you execute the below command it refers the physical tables like products and sales order details table. (see the execution plan)
set statistics io on
select * from
sales_view_indexed where SalesOrderID = 75121To make use of Indexed view we need to give command as below
set statistics io on
select * from
sales_view_indexed with (noexpand)
where SalesOrderID =
75121NOEXPAND – By specifying this key at select statement it fetches records directly from the indexed view without touching the base tables. Like this we can increase the performance of specific queries which has lot of joins and large amount of data on it.
Advantages
Improves the performance of SELECT statements
Indexes and data on multiple tables
Avoid complex joins and aggregations at run-time
Disadvantages
Increased disk space used by the database as the views are created with physical data.
Slows down the performance of the insert, update and delete statements on the tables used in indexed views.
Online re-index is not supported in indexed views.
High number of updates will not serve the benefit.
No comments: