Indexed or materialized views in Microsoft SQL Server

View with Indexing applied is called Indexed or materialized view. It improves query performance because it store in the database in the same way a table with a clustered index is stored. 

Indexed or materialized view is a Schema binding version of Base table. The first index created on a view must be a unique clustered index, After that we can create more non clustered indexes.

Why unique clustered index first?

Before creating Pinter data layer of raw data, it required the data to be physically arranged. The unique clustered index do this job in view.

Where to use?

In case for high security and Structured database, if vendor does not allow to change the indexes on the base tables, then it is a useful concept for enhancing technique.

For Data Warehouse/Reporting/OLAP systems it gives benefit. 

Now will go for a sample materialized views. 

CREATE VIEW DBO.VU_EXAMPLE

(EMPLOYEE_BID,FORMATTED_NAME,JOB_NUMBER,TOTAL_WORKING_HOURS)

WITH SCHEMABINDING

AS

 

select

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

,SUM(EMPTIME.WORKING_HOURS_PER_DAY) AS TOTAL_WORKING_HOURS

FROM DBO.FACT_EMPLOYEE_TIMESHEET EMPTIME

INNER JOIN DBO.DIM_EMPLOYEE EMP ON

            EMP.DIM_EMPLOYEE_ID=EMPTIME.DIM_EMPLOYEE_ID

GROUP BY

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

See, here most of the cases I have used “DBO.” Before the table because it is schema binded. Other wise you will get error like “invalid for schema binding. Names must be in two-part format”

Here, the word  “WITH SCHEMABINDING” is mandatory.

So, Now will execute  the view with Actual exection plan, After that will open the plan and look what’s happening?

SELECT * FROM VU_EXAMPLE

Here, the view behaving same as other normal views in SSMS. Because when we call the view,then it will goes to base tables and return the result set.

We can tell the view is not yet Materialized.

Now, will Materialize the view by creating Unique cluster index of first column (EMPLOYEE_BID).

CREATE UNIQUE CLUSTERED INDEX IX_VUEXAMPLE_EMPLOYEEBID ON DBO.VU_EXAMPLE(EMPLOYEE_BID)

Now, will execute this code. While executing this will throw the error like “VU_EXAMPLE' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list”

From  Microsoft, they have mentioned following:

·         If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

·         If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Now we will change our create query by adding  COUNT_BIG function.

CREATE VIEW DBO.VU_EXAMPLE

WITH SCHEMABINDING

AS

 

select

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

,SUM(EMPTIME.WORKING_HOURS_PER_DAY) AS TOTAL_WORKING_HOURS

,COUNT_BIG(*) as count

FROM DBO.FACT_EMPLOYEE_TIMESHEET EMPTIME

INNER JOIN DBO.DIM_EMPLOYEE EMP ON

            EMP.DIM_EMPLOYEE_ID=EMPTIME.DIM_EMPLOYEE_ID

GROUP BY

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

And create the UNIQUE CLUSTERED INDEX over this View

CREATE UNIQUE CLUSTERED INDEX IX_VUEXAMPLE_EMPLOYEEBID ON DBO.VU_EXAMPLE(EMPLOYEE_BID)

So, Now will execute  the view with Actual execution plan, After that will open the plan and look what’s happening?

SELECT * FROM VU_EXAMPLE

Here, the view behaving  very different from normal views in SSMS. Because when we call the view,then it will cluster scan of UNIQUE CLUSTERED INDEX column and return the result set.