View

A view is a named query stored in the database catalog that allows you to refer to it later. So the query above can be stored as a view.

select 

e.name,e.city,e.state,e.state,d.head

from employee

e join

department d on

e.dept=d.dept_name

 To see the result of above query, Instead of rewriting and executing above:

create view emp_dept as

select 

e.name,e.city,e.state,d.head

from employee

e join

department d on

e.dept=d.dept_name

Advantages of views

Security

You can restrict users to access directly to a table and allow them to access a subset of data via views.

Simplicity

A relational database may have many tables with complex relationships e.g., one-to-one and one-to-many that make it difficult to navigate.

However, you can simplify the complex queries with joins and conditions using a set of views.

Consistency

Sometimes, you need to write a complex formula or logic in every query.

To make it consistent, you can hide the complex queries logic and calculations in views.

Once views are defined, you can reference the logic from the views rather than rewriting it in separate queries.

How to see the definition of existing view:

EXEC sp_helptext 'emp_dept' ;

Drop View in SQL Server:

drop view if exists emp_dept

Materialized View: Indexed views are materialized views that stores data physically like a table hence may provide some the performance benefit if they are used appropriately.

To create an indexed view, you use the following steps:

• First, create a view that uses the WITH SCHEMABINDING option which binds the view to the schema of the underlying tables.
• Second, create a unique clustered index on the view. This materializes the view.

Because of the WITH SCHEMABINDING option, if you want to change the structure of the underlying tables which affect the indexed view’s definition, you must drop the indexed view first before applying the changes.

create view mat_view with schemabinding

as

select e.name,e.salary,d.dept_name,d.head from

dbo.employee e join dbo.department d on e.dept=d.dept_name where e.salary>50000

CREATE UNIQUE CLUSTERED INDEX 

    ucidx_product_id 

ON dbo.mat_view(name);