Thursday, September 5, 2013

Indexed Views in SQL Server

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

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 = 75121

To 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 = 75121

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.

 

No comments: