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:
Order ID | Product | Quantity | Sales | LOCATION | CUSTOMER |
1 | A | 1 | $100 | 1 | 55 |
2 | B | 10 | $2000 | 2 | 56 |
3 | C | 20 | $200 | 3 | 57 |
Location Table :
LOCATION ID | LOCATION |
1 | INDIA |
2 | US |
3 | INDIA |
Customer Table :
CUSTOMER ID | CUSTOMER NAME |
55 | TOM |
56 | RONY |
57 | SAM |
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
Order ID | Product | Quantity | Sales |
1 | A | 1 | $100 |
2 | B | 10 | $2000 |
3 | C | 20 | $200 |
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.
Order ID |
Product |
Quantity |
Sales |
1 |
A |
1 |
$100 |
2 |
B |
10 |
$2000 |
3 |
C |
20 |
$200 |
CREATE VIEW vwSalesAmount
AS
SELECT SUM(SALES) FROM SALES.
4.Indexes can be created on view
5.View can be encrypted.
1,970 total views, 2 views today
The attribute value in Dimension table are going to change slowly over time and they…
Changing Database compatibility level will be required when you upgrade your database from one…
To know what is compatibility Level check this post link. You can view the compatibility…
SQL Server Compatibility level will be used to decide how SQL will use it features.…
Below is the error which I was getting today while deploying the SSIS pacakge for…
Query store is a new powerful feature introduced in SQL 2016 which stores the query…