View in SQL server

A view is a virtual table.  View will not store any data physically. The data is pulled from the underlying base table when you call a view.

Advantages of View :

1.It can be used to combine multiple tables and can have required columns for them.

           Example :

           Let us consider below three tables

           SALES TABLE: 

Location Table :

Customer Table :

You can create a view to combine all the three tables and user will not need to know the underlying base table.

CREATE VIEW vwSales

AS

SELECT S.ORDERID,S.PRODUCT,S.QUANTITY,S.SALES,L.LOCATION NAME,C.CUSTOMER NAME

FROM SALES S

LEFT JOIN LOCATION L

ON S.LOCATION ID = L.LOCATION ID

LEFT JOIN CUSTOMER C

ON S.CUSTOMER ID = C.CUSTOMER ID

2.Views can be used  to implement the row level and column level security.

For example if I have below sales table and do not want to show the sales information to user then I can create the view as

CREATE VIEW vworder

AS

SELECT ORDERID,PRODUCT,QUANTIY FROM SALES

B.If the user need to be restricted by row for example in the above table if we only want to show user details where product is B then we can do that using view as

CREATE  VIEW vworder

AS

SELECT ORDERID,PRODUCT,QUANTITY,SALES

FROM SALES

WHERE PRODUCT = ‘B’

3.Views can be used to present aggregate and summarized data. Let says for below example i

Want to show the total sales then i can create the below view.

User can simply execute the query against the view rather than writing the complex query.

CREATE VIEW vwSalesAmount

AS

SELECT SUM(SALES) FROM SALES.

4.Indexes can be created on view

5.View can be encrypted.

 1,943 total views,  1 views today