View Vs Indexed View

View:

View is a virtual or imaginary table created based on the result set of the SQL statement. The views can be used to get the result set from multiple tables using joins or Unions or Union All.

The output of the view is not stored in the SQL database. Instead, the result set is determined at the time a query utilizing that view is executed.

View can be updated based on below,

1. The view is defined based on only one table.

2. The view must include the PRIMARY KEY of the table based upon which the view has been created.

3. The view should not have any field made out of aggregate functions and Group by and Having clause.

4. The view must not have any DISTINCT clause in its definition.

5. The view must not have any Sub queries in its definitions.

6. The select fields should not have a constant value.

Indexed View

Indexed views are similar to views, if the index are created on the views, then that is an Indexed view.

Why we need to create Indexed view?

The reason for going to index view is query performance, the performance of the index view is far better than standard view. As the indexed view has index on it and once an indexed view is created, the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.

Limitations of Indexed View:

The view definition should not have any hints, like with(nolock)

There should not be any derived query in joins

Only inner join is allowed. No outer joins and Cross joins are allowed

Only deterministic functions can be used. No non deterministic function can be used. Non deterministic functions like GETDATE(), Year(),Now(), etc.

If the select columns has a function, even that function should be Deterministic.

All the tables mentioned in the view definition should have an alias name.

Indexed view can be created on top the base tables and not on top of any other views.

while selecting the indexed view, With(noexpand) hint should be used.

If With(noexpand) hint is not used, then the query executes similar to standard view. Like it queries the base tables, rather than the index created, so the performance will be slow.

For the view to be indexed view, it should be created with Schemabinding.

The user defined functions also should be schemabinding.

Schema of the base table should be used in the view definition.

What is Schemabinding?

As name describes, schemabinding is bound to the schema of the base table. Now schema change is allowed to the base table. In order to do any changes to the base tables, the indexed view should be dropped and once the schema changes are done, then the Indexed view should be recreated.

Schemabinding cannot be created on the view having tables with the sys schema.

How to create an Index view?

Create View dbo.vw_viewname

With Schemabinding

As

Select tbl1.Col1 As tbl1_Col1,

tbl1.Col2 As tbl1_Col2 ,

tbl2.Col2 As tbl2_Col2

For dbo.table1 As tbl1

Inner Join dbo.tbale2 As tbl2

on tbl1.Col1=tbl2.Col2

once the view created with schemabinding, then the index should be created on that view, if not it will work like standard view(Query executes on the base table as no index is created on the view).

For the view to be indexed, it should have Unique clustered index.

Clustered Index Create Unique Clustered Index
ucidx_tb1_Col1
ON dbo.vw_viewname ( tbl1_Col1 )

Non Clustered Index Create Nonclustered Index ncidx_tb1_Col12 on dbo. vw_viewname ( tbl1_Col2 ) ;

Executing Index view: Select * from dbo. vw_viewname with(noexpand)