Indexed Views

By default, when we query a view it fires underlying query and get data from base table.

However, we can change this default behaviour of normal view by changing it to Indexed View.
Indexed view is a view which can store data, Hence this can also be called materialized view.

There are some rules while create an index view, which are listed below

1. View should be created with Schema Binding Option.
2. If an Aggregate function is used, we should make sure that expression used by aggregate function does not results in NULL value.
3. View statement should use Count_Big(*) function, if contains group by.
4. Base table should be reference with two part name.

Now, Create Unique Clustered Index on View to convert it to Indesxed View.


Example:

CREATE VIEW vwProducts(ProductSubcategoryKey,ProductCategory,Product#)
with schemabinding
as
(
    SELECT
        p.ProductSubcategoryKey
        ,EnglishProductSubCategoryName
        ,COUNT_BIG(*)
    FROM[dbo].[DimProductSubcategory] psc  
    join [dbo].[DimProduct] p
        on p.ProductSubcategoryKey=psc.ProductSubcategoryKey
    group by EnglishProductSubCategoryName, p.ProductSubcategoryKey
)


SELECT * FROM vwProducts

CREATE UNIQUE CLUSTERED INDEX IDX_ProductSubcategoryKey
ON [dbo].[vwProducts](ProductSubcategoryKey)

SELECT * FROM vwProducts