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)
Syntax: 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 SqlHintsViewDemo GO USE SqlHintsViewDemo GO Create Table dbo.Customers ( CustomerID int Identity(1,1), FirstName Varchar(50), LastName VarChar(50), Phone varchar(50), City Varchar(50) ) GO Create Table Orders ( OrderId int Identity(1,1), CustomerId int ) GO Insert INTO dbo.Customers Values ('Kalpana', 'Biradar', '2727272727', 'Bangalore'), ('Basavaraj','Biradar','1616161616','Mysore') GO INSERT INTO Orders Values(1), (2), (2), (2), (2)
Create Simple View:
The below script creates a view named vwSample:
CREATE VIEW dbo.vwSample As SELECT CustomerID, FirstName, LastName FROM dbo.Customers GO
We can use a statement like below to return all the customer records with three columns: CustomerID, FirstName, LastName.
SELECT * from dbo.vwSample
We can use the statement like below to see the content of view:
Different Uses of Views:
Views can also be used to insert, update and delete data from a table.
Example 1: Insert View Example
INSERT INTO dbo.vwSample VALUES ('Test1','Test1') SELECT * from dbo.vwSample SELECT * from dbo.Customers
Example 2: Update View Example
UPDATE dbo.vwSample SET LastName = 'B' WHERE CustomerID = 1 SELECT * from dbo.vwSample SELECT * from dbo.Customers
Example 3: Delete View Example
DELETE FROM dbo.vwSample Where CustomerID > 2 SELECT * from dbo.vwSample SELECT * from dbo.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 dbo.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 dbo.vwGetCustomers
The only way to reflect this new column in the view is to drop and create back the view as below:
Drop View dbo.vwGetCustomers GO Create View dbo.vwGetCustomers AS SELECT * FROM dbo.Customers GO SELECT * From dbo.vwGetCustomers GO
Below is an example view where it returns the data from multiple tables by joining:
Create View dbo.vwGetCustomerOrders AS SELECT C.FirstName,O.OrderId FROM dbo.Customers C INNER JOIN dbo.Orders O ON C.CustomerId = O.CustomerId GO Select * from dbo.vwGetCustomerOrders
[ALSO READ] How to check if a VIEW exists in Sql Server