SQL Server Indexed Views

Article

Introduction to View

Views are virtual tables which hold the data from one or more tables. It is stored in database. View does notitself 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 restricts 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,

CREATEVIEWVW_EmployeeWITHSCHEMABINDINGAS
SELECTe.Emp_Id,e.EmployeeName,e.EmpSalary,e.StateId,e.CityId
FROMdbo.Employee e
WHEREe.EmployeeNameLIKE'[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 a 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

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 other execution plan that it finds more efficient compared to clustered index. Let us take an example.

Query:

/*Create View*/
CREATE VIEW VI_DemoWITHSCHEMABINDINGas
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
,ISNULL(tcgi.Website,'Not Available')ASWebsite,ISNULL(tcgi.Email_Id,'Not Available')ASEmail,tcgi.Latitude,tcgi.Longitude,ISNULL(tcgi.Facebook_Id,'Not Available')ASFacebook_Id,
ISNULL(tcgi.Linkedin_Id,'Not Available')ASLinkedin_Id,ISNULL(tcgi.Twitter_Id,'Not Available')ASTwitter_Id,ISNULL(tcgi.Google_Plus_Id,'Not Available')ASGoogle_Plus_Id
FROM dbo.TblCompany_General_Infotcgi
INNERJOIN
dbo.TblMaster_Main_Categorytmmc
ON
tcgi.Category_Id=tmmc.Category_Id
INNERJOIN
dbo.TblUser_Profiletup
ON
tup.User_Id=tcgi.User_Id
WHERE
tcgi.Company_Id>1
/*Create Clustered Index*/
CREATEUNIQUECLUSTEREDINDEXidx_MyViewONVI_Demo(Company_Id)
/*Select Data from view*/
SELECT*FROMVI_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:

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.