Post
Ask Question

SQL Server Indexed Views

Introduction to SQL View

 
Views are virtual tables which hold the data from one or more tables. It is stored in database. View does not itself contain any data, it is a set of queries that are applied to one or more tables that are stored within the database as an object. Views are used for security purposes in databases, views restrict the user from viewing certain column and rows which means by using view we can apply the restriction on accessing the particular rows and columns for a specific user. View can be created using tables of same database or different database. It is used to implement the security mechanism in the SQL Server.
 
Find more about Views here: Views In SQL Server
 

What is 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.
 
table 
 
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
 
code 
 
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.
 
result 
 
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
 
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:
 
NOEXPAND  
 
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.

Conclusion

 
If we are creating a view for complex queries that are executing very frequently then instead of normal view always use INDEXED View. In other remaining cases normal views are beneficial. Before using the indexed view always consider your requirement and after that make a decision to use clustered index.
 
Read more articles on SQL Server:


Similar Articles