Indexed View

An indexed view has a unique clustered index. The unique clustered index is stored in SQL Server and updated like any other clustered index. An indexed view is more significant compared to standard views that involve complex processing of large numbers of rows, such as aggregating lots of data, or joining many rows. If such views are frequently referenced in queries, we can improve performance by creating a unique clustered index on the view. For standard view result set is not stored in database, instead of this the result set is computed for each query but in case of clustered index the result set is stored in the database just like a table with a clustered index is stored. Queries that don’t specifically use the indexed view can even benefit from the existence of the clustered index from the view. Index view has some cost in the form of performance, if we create an indexed view, every time we modify data in the underlying tables then not only must SQL Server maintain the index entries on those tables, but also the index entries on the view. In the developer and enterprise editions of SQL Server, the optimizer can use the indexes of views to optimize queries that do not specify the indexed view. In the other editions of SQL Server, however, the query must include the indexed view and specify the hint NOEXPAND to get the benefit of the index on the view.

 

How to create indexed Views?

 

To create an indexed view, first we need to create a view with schema binding option and after this create an index on view. For better understanding let us take an example.

 

 

We will create an indexed view for above table. First we create a view on Employee table,

1. CREATE VIEW VW_Employee WITH SCHEMABINDING AS    
2. SELECT e.Emp_Id,e.EmployeeName,e.EmpSalary,e.StateId,e.CityId    
3. FROM dbo.Employee e    
4. WHERE e.EmployeeName LIKE '[A-P]%'    

In above query create a view with schemabinding option. It is important for indexed view that view must be created with schemabinding option. Schemabinding option ensure that the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. If we try to create an indexed view without schemabinding option then SQL Server will throw an error of “Cannot create index on view 'VW_Employee' because the view is not schema bound”.

 

Example

 

 

Above image clear that for indexed view can’tbe created without Schema binding option. Once this index is created, the result set of this view is stored in the database just like any other clustered index. 

 

How an Indexed View Works

 

Whenever we add a unique clustered index to a view, materialize view is created. Materialized views are disk based and are updated periodically based upon the query definition. In other word the view persists to disk, with its own page structure, and we can treat it just like a normal table. Any aggregations defined by the indexed view are now pre-computed, and any joins pre-joined, so the engine no longer has to do this work at execution time. A well-crafted indexed view write fewer pages to disk than the underlying tables, that means fewer pages queries need to read fewer pages to return results. In form of result we will get fast and efficient result.

 

 

 

 

In above query we can see that query optimizer perform clustered index scan.

 

If we created a clustered index on a view then it is not compulsory that query optimizer always uses this clustered index, query optimizer can use another execution plan that it finds more efficient compared to clustered index. Let us take an example.

 

Query

1. --Create View  
2. CREATE VIEW VI_Demo WITH SCHEMABINDING as    
3. SELECT tcgi.Company_Id, tcgi.Company_Name, tcgi.Contact_Person, tmmc.Category_Name, tcgi.Establish_Year, tcgi.Address+', '+tcgi.PincodeAS Address_, tcgi.Mobile_Number+ISNULL(','+tcgi.Landline_Number,'')ASContact_Info    
4. ,ISNULL(tcgi.Website,'Not Available')ASWebsite,ISNULL(tcgi.Email_Id,'Not Available')AS Email,tcgi.Latitude, tcgi.Longitude, ISNULL(tcgi.Facebook_Id,'Not Available') AS Facebook_Id,    
5. ISNULL(tcgi.Linkedin_Id,'Not Available')AS Linkedin_Id,ISNULL(tcgi.Twitter_Id,'Not Available')AS Twitter_Id,ISNULL(tcgi.Google_Plus_Id,'Not Available')AS Google_Plus_Id    
6. FROM dbo.TblCompany_General_Infotcgi    
7. INNER JOIN    
8. dbo.TblMaster_Main_Categorytmmc    
9. ON    
10. tcgi.Category_Id=tmmc.Category_Id    
11. INNER JOIN    
12. dbo.TblUser_Profiletup    
13. ON    
14. tup.User_Id=tcgi.User_Id    
15. WHERE    
16. tcgi.Company_Id>1    
17. --Create Clustered Index  
18. CREATE UNIQUE CLUSTEREDINDEX idx_MyView ON VI_Demo(Company_Id)    
19. --Select Data from view  
20. SELECT * FROM VI_Demo    

Execution Plan

 

 

We can see that query optimizer doesn’t use the clustered index because this happens often if the optimizer thinks it can do better with the base tables.

 

To force the query optimizer to use the “Clustered Index” always use “NOEXPAND “ option like below:

 

 

Now query optimizer always use the clustered index even if it have better execution plan, so it is not nice to force the query optimizer to use clustered index using the NOEXPAND option.

 

Where to Use Indexed View

 

Indexed views have both a benefit and a cost. The benefit is that query optimizer provides more efficient and faster results for complex and redundant queries. The cost of an indexed view is on the maintenance of the clustered index. In the following scenario, Indexed View can be used.

• When you use the same complex query on many tables, multiple times.
• When new system need to read old table data, but doesn't watch to change their perceived schema.
• The environments that are best suited for indexed views are data warehouses, data marts, OLAP databases but transactional environment are less suitable for Indexed View.

Restrictions on Indexed Views

• View must be created with SCHEMABINDING.
• Functions using in definition of view must have been created with SCHEMABINDING.
• Base tables must have been created with the proper ANSI_NULLS setting..

Source :  https://www.c-sharpcorner.com/article/sql-server-indexed-views/