Views in Sql Server

Views are nothing but saved SQL statements, and are sometimes referred as Virtual Tables. Keep in mind that Views cannot store data rather they only refer to data present in tables.

Benefits of Views:
A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all the data). Views can do the following:

•   Restrict access to specific rows in a table
•   Restrict access to specific columns in a table
•  Join columns from multiple tables and present them as though they are part of a  single  table
•  Present aggregate information (such as the results of the COUNT function)

Lets checkout the basic syntax for creating a view:

CREATE VIEW <View_Name>
AS
<SELECT Statement>

Let us create a Sample DataBase with Tables to understand Views Concepts:
By using the below script we are creating a Sample DataBase Named:ViewDemo. Then in this database we are creating two tables Customers and Orders  and in these tables populating the sample data.

Create DataBase ViewDemo

GO

USE ViewDemo

GO

Create Table Customers

(

CustomerID int Identity(1,1),

FirstName Varchar(50),

LastName VarChar(50),

Phone varchar(50),

City Varchar(50)

)

Create Table Orders

(

OrderId int Identity(1,1),

CustomerId int

)

GO

Insert INTO Customers

Values ('Kalpana','Biradar','2727272727','Bangalore')

Insert INTO Customers

Values ('Basavaraj','Biradar','1616161616','Mysore')

INSERT INTO Orders

Values(1)

INSERT INTO Orders

Values(2)

INSERT INTO Orders

Values(2)

INSERT INTO Orders

Values(2)

INSERT INTO Orders

Values(2)

Create Simple View:

The below script creates a view named vwSample:

CREATE VIEW vwSample

As

 SELECT CustomerID, FirstName, LastName

 FROM CUSTOMERS

GO

We can use a statement like below to return all the customer records with three columns: CustomerID, FirstName, LastName.

SELECT * from vwSample

We can use the statement like below to see the content of view:
Sp_helptext vwGetCustomers

Different Uses of Views:
Views can also be used to insert, update and delete data from a table.

Ex1: Insert View Example

INSERT INTO vwSample

VALUES ('Test1','Test1')

SELECT * from vwSample

SELECT * from Customers

Ex2: Update View Example

UPDATE vwSample

SET LastName = 'B'

WHERE CustomerID = 1

SELECT * from vwSample

SELECT * from Customers

Ex3: Delete View Example

DELETE FROM vwSample Where CustomerID > 2

SELECT * from vwSample

SELECT * from Customers

Difference Between Views and User Defined Functions:
Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, where as Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.

Addition of New Column’s in the Underlying Table will not automatically reflect in the existing views:
Let us prove this behaviour by creating a view vwGetCustomers  which returns all customer details with all the columns in the customer table: 

Create View vwGetCustomers

AS

SELECT *

FROM Customers

GO

Select * FROM vwGetCustomers

Now add one more column Country to the Customers table:

ALTER Table Customers

ADD Country Varchar(30)

Execute the below statement and observe that the new column country added in the Customers table is not present in the result.

SELECT * From vwGetCustomers

The only way to reflect this new column in the view is to drop and create back the view as below:

Drop View vwGetCustomers

GO

Create View vwGetCustomers

AS

 SELECT *

 FROM Customers

GO

SELECT * From vwGetCustomers

GO

Below is an example view where it returns the data from multiple tables by joining:

Create View vwGetCustomerOrders

AS

 SELECT C.FirstName,O.OrderId

 FROM Customers C

   INNER JOIN Orders O

    ON C.CustomerId = O.CustomerId

GO

Select * from vwGetCustomerOrders