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

  1. Simplification: Encapsulates complex queries, making it easier to manage and reuse.
  2. Security: Restricts user access to specific rows or columns.
  3. Data Abstraction: Hides the complexity of the database schema.

4. Limitations of using views

  1. Views cannot include certain constructs like ORDER BY without TOP, COMPUTE, COMPUTE BY, TEMPORARY TABLES, and some others.
  2. Views with GROUP BY, HAVING, DISTINCT, or certain joins may not be updatable.
  3. Indexed views have additional restrictions, such as not allowing certain functions, clauses, and joins.


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

line

Copyrights © 2024 letsupdateskills All rights reserved