SQL Views

VIEW:
View can be defined as a virtual table or a stored query. The data accessible through a view is not stored in the database as a distint object.

Views are useful due to two main reasons:

1. Security
2. Convenience

From security point of view, it restrict users to specific rows or columns. Also user is not aware of data source of the View. For example, we can get data from multiple tables using JOIN in a view but the user is not aware about tables from which data is coming. 

Another most important benefit of View is that we don't have to write large querys again and again to fetch data from multiple tables, we only write query once and then stored it in a View. 

View Syntax:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

INSERT,UPDATE and DELETE Operations on View:

We can apply these operatoins on Views, they have affect on the data present inside the table.

Example:

-- We are using AdventureWorks2012 database and will create a View using Person.Address table.

CREATE VIEW vw_PersonAddress
AS
SELECT Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode,Person.Address.StateProvinceID FROM Person.Address
-- We created a view and getting Address,City,PostalCode and StateProvinceID 
-- Now we will add a new row using vw_PersonAddress
--INSERTION
INSERT INTO vw_PersonAddress(AddressLine1,City,StateProvinceID,PostalCode) VALUES('Inside SQL','Table',14,'333')
SELECT * FROM vw_PersonAddress WHERE City = 'Table'
-- Output:
-- AddressLine1 City PostalCode StateProvinceID
-- Inside SQL Table 333 14

--UPDATION

UPDATE vw_PersonAddress
SET AddressLine1 = 'Inside SQL updated',
 PostalCode = '999'
WHERE City = 'Table'

--Output:
SELECT * FROM vw_PersonAddress
WHERE City = 'Table'

--AddressLine1 City PostalCode StateProvinceID
--Inside SQL updated Table 999 14

-- DELETION:
DELETE FROM vw_PersonAddress WHERE City = 'Table'

-- It will delete the row

Conditions:

1. Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
2. The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
3. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

With Check Option:
It has three options.

1. ENCRYPTION
2. SCHEMABINDING 
3. VIEW_METADATA

Encryption:

It encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
We can better understand With Encryption check with below example.

Example:

-- We will alter the above view WITH ENCRYPTION option
-- Before altering the view first look at the definition of view using Object_definition
SELECT OBJECT_DEFINITION(OBJECT_ID('vw_PersonAddress'))
--OUTPUT:
--CREATE VIEW [dbo].[vw_PersonAddress]
--AS
--SELECT Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalC--ode,Person.Address.StateProvinceID FROM Person.Address
ALTER VIEW [dbo].[vw_PersonAddress]
WITH ENCRYPTION
AS
SELECT Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode,Person.Address.StateProvinceID FROM Person.Address
--After Alteration:
SELECT OBJECT_DEFINITION(OBJECT_ID('vw_PersonAddress'))
--OUTPUT:
--NULL
-- Because we altered it using with Encryption check, we can't view it's definition

SCHEMABINDING:

It binds the View to the schema of the underlying table or tables, means if we create a view using this option the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must be first modified or dropped to remove dependencies on the table that is to be modified. Also those tables that are referenced in a view containing Schemabinding option can be removed until view is removed or schemabinding option is removed from that view.
Example:

--First I will modify the view to remove ENCRYPTION option

ALTER VIEW [dbo].[vw_PersonAddress]
AS
SELECT Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode,Person.Address.StateProvinceID FROM Person.Address

--Now I will alter the view with SCHEMABINDING option

ALTER VIEW [dbo].[vw_PersonAddress]
WITH SCHEMABINDING
AS
SELECT Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode,Person.Address.StateProvinceID FROM Person.Address

-- Now I will try to delete Person.Address table

DELETE FROM Person.ADDRESS

--Output

--Msg 547, Level 16, State 0, Line 1
--The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessEntityAddress_Address_AddressID". The conflict occurred in database "AdventureWorks2012", table "Person.BusinessEntityAddress", column 'AddressID'.
--The statement has been terminated.

-- The AddressID is referenced in multiple tables, so I will create a new table and view to demonstrate it

CREATE TABLE [dbo].[testView]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](10) NULL
)
INSERT INTO dbo.testView(NAME) values ('apple')
INSERT INTO dbo.testView(NAME) VALUES ('banana')
INSERT INTO dbo.testView(NAME) VALUES ('orange')

CREATE VIEW vw_testView
WITH SCHEMABINDING
AS
SELECT ID,Name FROM dbo.testView
-- Now I will try to drop testView table
DROP TABLE testView
--Output
--Msg 3729, Level 16, State 1, Line 1
--Cannot DROP TABLE 'testView' because it is being referenced by object 'vw_testView'.

Partitioned Views:
In partioned view we can fetch data from multiple tables in either the same instance of sql server or in a group of autonomous instances of sql server using UNION ALL.
Genral Syntax:

-- Server1
create database Partitioning;

USE Partitioning
Go

Create table Students
(
 ID int Identity,
 Name varchar(30)
);

INSERT INTO Students(Name) VALUES('A');
INSERT INTO Students(Name) VALUES('B');
INSERT INTO Students(Name) VALUES('C');

-- Server2
create database Partitioning;

USE Partitioning
Go

Create table Students
(
 ID int Identity,
 Name varchar(30)
);

INSERT INTO Students(Name) VALUES('E');
INSERT INTO Students(Name) VALUES('F');
INSERT INTO Students(Name) VALUES('J');

-- Now I will create a partition view to fetch data from these two tables
CREATE view vw_Part_Students
AS
Select * from [Server1].Partitioning.dbo.Students
UNION  ALL
Select * from [Server2].Partitioning.dbo.Students

select * from vw_Part_Students
order by Name

--Output:
--ID     Name
--1       A
--2       B
--3       C
--1       E
--2       F
--3       J

Conditions for Partioned View:

1. All columns in the member tables should be selected in the column list of the view definition.
2. The columns in the same ordinal position of each select list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.

Indexed Views:
We can created indexes on views. The first index created on a view must be a unique clustered index. After unique clustered index we can create additional nonclustered indexes.
Example:

CREATE UNIQUE CLUSTERED INDEX myidx ON vw_testView(ID)

Thanks for reading if you have any query please ask.

Source:

MSDN