Different Types of Views in SQL Server

Estimated study time: 10 minutes. Virtual tables that simplify and secure how you access data.

A view in SQL Server is a virtual table built from the result of a stored SELECT query. It doesn't store data itself (with one exception below) but presents data from one or more underlying tables in a simplified or restricted form.

1. Simple Views

Built from a single table with no aggregation, grouping, or joins. Simple views are usually updatable, meaning INSERT, UPDATE, and DELETE can flow through to the base table.

CREATE VIEW ActiveEmployees AS
SELECT EmpID, EmpName, DeptID
FROM Employees
WHERE Status = 'Active';

2. Complex Views

Built from multiple tables using joins, or including aggregate functions, GROUP BY, or DISTINCT. Complex views are usually read-only.

CREATE VIEW DepartmentHeadcount AS
SELECT d.DeptName, COUNT(e.EmpID) AS EmployeeCount
FROM Departments d
JOIN Employees e ON d.DeptID = e.DeptID
GROUP BY d.DeptName;

3. Indexed (Materialized) Views

A view that has a unique clustered index created on it. This physically stores the result set on disk, which speeds up queries against the view at the cost of extra storage and slower writes to the base tables.

CREATE VIEW SalesSummary WITH SCHEMABINDING AS
SELECT ProductID, SUM(Quantity) AS TotalSold
FROM dbo.Sales
GROUP BY ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary
ON SalesSummary (ProductID);

4. Partitioned Views

Combines data from several similarly structured tables (often split across servers or by date range) using UNION ALL, presenting them as one logical table.

CREATE VIEW Sales_AllYears AS
SELECT * FROM Sales_2024
UNION ALL
SELECT * FROM Sales_2026;

5. System Views

Built-in views such as those in the sys and INFORMATION_SCHEMA schemas that expose metadata about the database itself, like table definitions and column properties.

SELECT * FROM INFORMATION_SCHEMA.TABLES;