What Is a View?

A view is a virtual table defined by a query. It does not store data itself but presents data from one or more tables in a convenient, reusable format.

Benefits include:

• Simplify complex queries
• Encapsulate business logic
• Restrict data access
• Improve maintainability

Creating a View

Use CREATE VIEW followed by the view name and the SELECT statement that defines it.

CREATE VIEW dbo.EmployeeSummary AS
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    e.Salary
FROM dbo.Employees e
JOIN dbo.Departments d ON e.DepartmentID = d.DepartmentID;Copy

Altering a View

To modify a view, use ALTER VIEW. The syntax is the same as CREATE VIEW.

ALTER VIEW dbo.EmployeeSummary AS
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    e.Salary,
    e.HireDate
FROM dbo.Employees e
JOIN dbo.Departments d ON e.DepartmentID = d.DepartmentID;Copy

Dropping a View

Remove a view with DROP VIEW.

DROP VIEW dbo.EmployeeSummary;Copy

Best Practices

• Keep view definitions simple and readable.
• Avoid using SELECT * inside a view.
• Document the purpose of each view.
• Consider performance: indexed views can improve read speed for large datasets.

Full Example: Employee Reporting View

This example demonstrates creating a view, querying it, and then cleaning up.

-- 1. Create the view
CREATE VIEW dbo.EmployeeReport AS
SELECT 
    e.EmployeeID,
    CONCAT(e.FirstName, ' ', e.LastName) AS FullName,
    d.DepartmentName,
    e.Salary,
    FORMAT(e.HireDate, 'yyyy-MM-dd') AS HireDate
FROM dbo.Employees e
JOIN dbo.Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.IsActive = 1;

-- 2. Query the view
SELECT * FROM dbo.EmployeeReport
WHERE Salary > 60000
ORDER BY Salary DESC;

-- 3. Update underlying data (view reflects change automatically)
UPDATE dbo.Employees
SET Salary = Salary * 1.05
WHERE DepartmentID = (SELECT DepartmentID FROM dbo.Departments WHERE DepartmentName = 'Sales');

-- 4. Drop the view when no longer needed
DROP VIEW dbo.EmployeeReport;Copy