What is Materialized View or Indexed View and how to create it in SQL Server

What is difference between View and a Materialized View in SQL Server

View :
The View is a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data.
So thaat we always get the updated or latest data from original tables.
Performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statement in our query.

If we need multiple joins between tables, then use the Materialized view defined below, where we define the Index based columns which we used for joining, as we know that the index based columns are faster than a non-index based column.

Materialized View :
Materialized views are also the logical view of our data-driven by the select query but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.
When we see the performance of Materialized view it is better than normal View, because the data of materialized view will be stored in table and table may be indexed so faster for applying joins.
Also joining is done at the time of materialized views refresh time so that no need to fire join statement every time as in case of view.

Creating a Materialized View :

If you want to create an Index on your View, you must create it with using the " WITH SCHEMEBINDING" Option.
If you want to create a Schema-Bound user defined Function that references to your View then your View must also be " SCHEMEBINDING".

The SHEMABINDING is essentially takes the things that your VIEW is depend upon(Tables/ other Views), and "Binds" them to that View. The significance of this is that, no one can make alteration (ALTER/DROP) to those underlying objects, unless you drop the Schema-Bound View first.

--------------------------------------------------------------------------------------
Example :
Creating a View with SchemaBinding Option
USE [TAMATAM]
GO
CREATE VIEW vw_EmpDeptDtls
WITH SCHEMABINDING
AS
SELECT E.[Emp_Id],
E.[Emp_Name],
E.[Gender],
E.[Joining_Date],
E.[Basic_Sal],
D.[Dept_Id],
D.[Dept_Name],
D.[Bonus_Rate],
J.[Job_Id],
J.[Job_Title]
FROM [dbo].[Emp_Test] E Inner Join [dbo].[Dept] D
ON E.Dept_Id=D.Dept_Id
Inner Join [dbo].[JobTitles] J
ON E.Job_Id=J.Job_Id
GO
--------------------------------------------------------------------------------------
Now you can create an Index on the above View to make the Query execution faster :
Create Unique Clustered Index Ind_Vw_EmpDept
On vw_EmpDeptDtls (Emp_Id,Dept_Id,Job_Id)
--------------------------------------------------------------------------------------

Notes :
Now if you try to make any alterations to the underlying objects...
--ALTER TABLE EMP_TEST Drop Column [Job_Id]
--ALTER TABLE EMP_TEST ALTER Column [Emp_Name] Varchar(250)
--Drop Table [Emp_Test]

You will get the following errors...
--The object 'vw_EmpDeptDtls' is dependent on column 'Job_Id'
--ALTER TABLE DROP COLUMN Job_Id failed because one or more objects access this column.
--Cannot DROP TABLE 'Emp_Test' because it is being referenced by object 'vw_EmpDeptDtls' 

Please note that :
--You can Alter the underlying objects of the View, by removing the SchemaBinding by altering the View .
--You can drop/alter a Column from the base table, which is not used in the View.

Difference between View vs Materialized View :

a) The first difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table.
b) When we create a view using any table, rowid of view is same as the original table but in case of Materialized view rowid is different (in Oracle)
c) In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
d) Performance of View is less than Materialized view.
e) In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
f) In case of Materialized view we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------