Views

Types of views:

1. General Views:  These views are normal view, which are just return the results of the underlying tables from one server  without saving the data. 
2. Indexed Views(materialized views): These views store the actual results of the view query in the clustered index structure. We use these views to improve the performance if our view query contains multiple joins and aggregations.
3. Partitioned Views: These views used to get the data from joining partitioned data from the tables which are on the multiple databases and multiple servers.
1. If the all underlying tables are in one server then these views are called "Local Partitioned view".
2. If the underlying tables are in multiple servers then the view is called "Distributed Partitioned view".

Creating General View:

Syntax:

CREATE VIEW [VIEW NAME] 

WITH [OPTION]

AS 

       [SELECT STATEMENT]

GO

Example:

      Creating a view using employee table which returns the employee information along with his manager name. Usually we save only employee manager Id in the table, we need to make a join every time when we need the manager name, instead we can create a view and use it when ever we need same as table.

if not exists(select * from information_schema.tables where table_schema = 'dbo' and table_name = 'Employee')

begin

create table dbo.Employee(Id int, Name Varchar(30),ManagerId int, salary INT)

END

GO

--Creating a view to return the employee and hiss manager

CREATE view dbo.EmployeeManager
WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',

emp.salary 

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

Some points about view:

1. The view creation always start with "CREATE VIEW" key word.
2. The body of the view is general "SELECT" statement, which is subjected to general rules of SELECT.
3. There is no restriction on the columns in the select statement, we can Add new columns(computed or any other) or Remove the columns which we are interested in
4. If the underlying table structure changes, the view will give errors, so we can use "SCHEMABINDING" option while creating a view. so the alter table is not allowed unless we drop the view.
5. Materialized views also created with SCHEMABINDING option.

Creating indexed view:

          Indexed views are SCHEMABINDED views which are indexes defined on it. We can change the above to indexed view by creating a index on it.

CREATE view dbo.EmployeeManager

WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',

emp.salary 

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

CREATE CLUSTERED INDEX idx_cl_id  ON  dbo.EmployeeManager(id);
GO

*) Indexed views store the view results and create the index on them.

Altering View:

We can use "ALTER VIEW" to alter the view definition.

Example: Let's alter the view to add "Manager salary"  column.

ALTER VIEW dbo.EmployeeManager

WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',
                mng.salary 'ManagerSalary',

emp.salary

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

Dropping View:

        1. All the views will be stored in "sys.views" system table. 

Syntax: 

DROP VIEW [VIEWNAME]

Example:

if exists (select 'x' from sys.views where object_id = OBJECT_ID(N' dbo.EmployeeManager'))
BEGIN

       DROP VIEW dbo.EmployeeManager

GO

Uses Of  View:

1. Hide the complexity of the queries: Let us say if you have a query with complex joins and calculations you can just create a view for that query and use it anywhere. It'll make the query more readable.
2. Security: If you have a table which have some security information and you want to share some of it's data to others. You can just create a view with the data you want to share and give permission on the view to user, instead of the table. In such a way you can protect illegal access to the tables
3. Support code referring to Deleted or changed tables: If you have a code which referring to a table and in the process of clean up you decided to delete the table or change the table name. In that case the code referring to old table will break, Instead you can create a view with the old table name and refer it to the new table. In such a you can make the old code work,

PS : It is best practice to check the existence of the view before creation and altering and dropping to avoid the SQL errors in the script.

Thanks for reading. Please feel free to comment if you have any suggestions.