Indexed View or Materialized View

Indexed View or Materialized View 

Indexed View also known as Materialized view because they have a virtual table to store data whereas the regular view is the metadata of a query. Even though Indexed view on SQL Server has tons of limitation, you can create view to store result of a query that involves calculation or need to aggregate data. Calculate/Aggregate once and use the result as many times as you want.

You might have heard DBA or developer saying Indexed View enhance performance of a query. How do you gain performance using materialized view? Instead of running the calculation  every time you run the query, you just pull the calculated result from materialized view which takes very less time compared to original query with calculation.

In Oracle, you will need to refresh materialized view on scheduled basis or when the data on the base tables are updated. With SQL Server, there is no need to refresh the materialized view, the SQL Server  takes care of refreshing materialized view when the data changes on a base tables. Isn't this cool?

The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

1. Verify the SET options are correct for all existing tables that will be referenced in the view.
2. Verify that the SET options for the session are set correctly before you create any tables and the view.
3. Verify that the view definition is deterministic.
4. Create the view by using the WITH SCHEMABINDING option.
5. Create the unique clustered index on the view.

Let’s see how we can create a materialized view.

USE AdventureWorks2012;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO

Now, I want you to run these queries below and record the execution time for each query?

1.  SELECT * FROM 'Sales.vOrders;
2. SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,        OrderDate, ProductID, COUNT_BIG(*) AS COUNT    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o    WHERE od.SalesOrderID = o.SalesOrderID    GROUP BY OrderDate, ProductID;

Are you not a-m-a-z-e-d with Materialized View performance?
Remember: The base tables cannot be truncated. This makes the Materialized View invalid on Oracle, you will need to run complete refresh after base table truncate or else the Mview will have stale data. SQL Server will throw error for truncate statement because of schemabinding option. Not all queries can be converted into materialized view, there are tons of restrictions. See MSDN documentation for list of restrictions before you plan to use materialized view or commit for one.

Source: http://msdn.microsoft.com/en-us/library/ms191432.aspx