A view in SQL Server is a virtual table created based on the result set of a SQL query. It simplifies complex queries, enhances security, and provides a level of abstraction. Unlike tables, views do not store data physically; they store the query that generates the data dynamically.
Consider below table
1. Creating and Using Views
Create a View:
A view can be created using the CREATE VIEW statement. Below is an example of creating a view
CREATE VIEW EmployeeView AS SELECT e.EmployeeID, e.Name, e.DepartmentID, e.HireDate FROM Employees e
Executing View : its very simple "Select * from Viewname"
Business Use Case:
suppose we do not want to give access to production data on some tables so instead of giving access on table , we can give access on view and user will able to see only those columns those we defined in select query of view.
Here in this example Employee table have Salary and we removed salary Column from View and user executed view as below he will not able to see Salary of Employee
select * from EmployeeView
2. Updating Data through Views
If the view does not involve joins or aggregations, you can update data through the view:
UPDATE EmployeeView SET Name='New Name' WHERE EmployeeID = 1;
3. Benefits of Using Views
4. Limitations of using views
5. Encrypt a view
You can encrypt a view to hide its definition by using the WITH ENCRYPTION option
CREATE VIEW ViewName WITH ENCRYPTION AS SELECT Column1, Column2 FROM TableName WHERE Condition;
6. Read View definition
Query: sp_helptext "viewname"
7. Alter View Definition
We can use alter command
alter VIEW EmployeeView AS SELECT e.EmployeeID, e.Name, e.DepartmentID, e.HireDate , e.Salary FROM Employees e
we can see in result that salary has been added to view
Happy learnings and happy coding
Copyrights © 2024 letsupdateskills All rights reserved