SQL Server Views — From Basics to Advanced (How I Actually Understand It)

Content of this blog

• Database Architecture (Where Views Fit)
• Why Do We Even Need Views?
• CTE vs Stored Procedure vs Function (Why They’re Not Enough)
• What is a View?
• Use Cases of Views
• Types of Views (Simple vs Complex)
• Indexed (Materialized) Views
• How Indexed Views Work Internally
• Restrictions & Limitations of Indexed Views
• Partitioned Views
• Updatable Views
• INSTEAD OF Triggers (Making Complex Views Updatable)
• WITH ENCRYPTION
• Views vs CTE vs Temp Tables (Real Differences)
• Final Thought

Views are one of those things that look simple at first… SELECT * FROM ViewName and you move on.

But once you go deeper — performance, security, abstraction, architecture — you realize they’re doing way more than just wrapping a query.

This post is me breaking down everything I learned about Views in SQL Server, from basics to advanced stuff like Indexed Views and Partitioned Views.

Let’s go.

Database Architecture (Where Views Fit)

Before talking about views, you need to understand where they actually live.

A database has 3 layers:

1. Physical Layer

This is the low-level stuff:

• MDF / NDF files
• How data is stored on disk
• Pages, storage, growth

You don’t deal with this directly most of the time.

2. Conceptual Layer (Schema)

This is what you usually design:

• Tables
• Relationships
• Constraints

This is your actual database structure.

3. External Layer (View Level)

This is where Views live.

Here you:

• Customize data per user
• Hide complexity
• Control access

Think of it as:

“What does each user see from the database?”

The Problem: Why Do We Even Need Views?

Before jumping to views, I tried to solve the same problems using other tools.

Option 1: CTE

WITH OrdersCTE AS (
    SELECT CustomerId, SUM(Amount) AS Total
    FROM Orders
    GROUP BY CustomerId
)
SELECT * FROM OrdersCTE

Looks clean. But:

• Only lives inside one query
• Not reusable
• Can’t share across the system

→ Not enough.

Option 2: Stored Procedure

CREATE PROCEDURE GetCustomerTotals
AS
SELECT CustomerId, SUM(Amount)
FROM Orders
GROUP BY CustomerId

Problem:

• Not composable
• Can’t easily use inside another SELECT
• Not flexible for chaining queries

→ Not what I want.

Option 3: Function

CREATE FUNCTION GetTotals()
RETURNS TABLE
AS
RETURN (
    SELECT CustomerId, SUM(Amount) AS Total
    FROM Orders
    GROUP BY CustomerId
)

Better… but:

• Restrictions
• Sometimes worse performance
• Not always needed if no parameters

The Solution: Views

View = Stored Query (Virtual Table)

CREATE VIEW V_CustomerTotals
AS
SELECT CustomerId, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerId

Now I can do:

SELECT * FROM V_CustomerTotals

Clean. Reusable. Centralized.

Use Cases (Where Views Actually Shine)

1. Abstraction

You have a monster query:

SELECT c.Name, SUM(o.Amount)
FROM Customers c
JOIN Orders o ON c.Id = o.CustomerId
WHERE o.Status = 'Completed'
GROUP BY c.Name

You don’t want every developer rewriting this.

→ Wrap it in a View.

SELECT * FROM V_CustomerRevenue

Done.

2. Security

Let’s say your table has sensitive data:

SELECT Id, Name, Salary, SSN
FROM Employees

You don’t want everyone seeing SSN.

→ Create a view:

CREATE VIEW V_PublicEmployees
AS
SELECT Id, Name, Salary
FROM Employees

Now give access to the view, not the table.

3. Reusability (Single Source of Truth)

Instead of:

• dev1 writes query
• dev2 writes same query
• dev3 copies and edits

→ You centralize logic in one place.

4. Stability (Backward Compatibility)

You had:

Customer

Then you normalized:

Customer_Basic
Customer_Details

Everything breaks.

→ Solution:

CREATE VIEW Customer
AS
SELECT ...
FROM Customer_Basic
JOIN Customer_Details ...

Old queries keep working.

Types of Views

1. Simple View

CREATE VIEW V_Employees
AS
SELECT Id, Name, Salary
FROM Employees

Characteristics:

• One table
• No joins
• No aggregations

✔ Can be updatable (with conditions)

2. Complex View

CREATE VIEW V_EmployeeDepartments
AS
SELECT e.Name, d.Name AS Department
FROM Employees e
JOIN Departments d ON e.DeptId = d.Id

Or:

SELECT CustomerId, SUM(Amount)
FROM Orders
GROUP BY CustomerId

Characteristics:

• Joins
• Aggregations
• Subqueries

✔ Usually read-only Because updates become ambiguous.

Indexed | Materialized Views (This Is Where Things Get Serious)

Normal views don’t store data. Indexed Views do.

View + Unique Clustered Index = Stored Result on Disk

Example

CREATE VIEW V_SalesSummary
WITH SCHEMABINDING
AS
SELECT CustomerId, SUM(Amount) AS TotalAmount
FROM dbo.Orders
GROUP BY CustomerId

CREATE UNIQUE CLUSTERED INDEX IX_V_SalesSummary
ON V_SalesSummary(CustomerId)

Now:

• Data is physically stored
• Queries become much faster

What Happens on INSERT?

INSERT INTO Orders VALUES (1, 100)

SQL Server:

1. Updates Orders table
2. Updates its indexes
3. Updates V_SalesSummary (incrementally)

→ Adds 100 to the existing total No full recompute.

Supported Aggregations

✔ Allowed:

• SUM
• COUNT_BIG

❌ Not allowed:

• MAX
• MIN
• AVG

Restrictions (Important)

You cannot use:

• OUTER JOIN
• UNION
• DISTINCT
• TOP / OFFSET
• GETDATE(), NEWID()

Everything must be:

Deterministic + Incrementally maintainable

Tradeoff

• Reads → Fast
• Writes → Slower

Because every write updates the view too.

Partitioned Views

Used to split large tables manually.

Example:

Sales_2024
Sales_2025
Sales_2026

Each table:

CHECK (Year >= 2024 AND Year < 2025)

Then:

CREATE VIEW V_AllSales
AS
SELECT * FROM Sales_2024
UNION ALL
SELECT * FROM Sales_2025
UNION ALL
SELECT * FROM Sales_2026

What Happens in Query?

SELECT * FROM V_AllSales WHERE Year = 2024

SQL Server: → Reads only Sales_2024

This is called:

Partition Elimination

Updatable Views

Some views allow:

• INSERT
• UPDATE
• DELETE

Example

CREATE VIEW V_Employees
AS
SELECT Id, Name, Salary
FROM Employees

UPDATE V_Employees
SET Salary = 6000
WHERE Id = 1

Works.

When It Fails

If the view has:

• JOIN
• GROUP BY
• DISTINCT

→ SQL Server rejects it (ambiguous).

Advanced: INSTEAD OF TRIGGER

CREATE TRIGGER trg_UpdateView
ON V_Complex
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Employees
    SET Salary = inserted.Salary
    FROM inserted
    WHERE Employees.Id = inserted.Id
END

Now you control the behavior.

WITH ENCRYPTION

CREATE VIEW V_Secure
WITH ENCRYPTION
AS
SELECT * FROM Employees

Prevents viewing the definition.

But:

It’s not strong security — just obfuscation.

Views vs CTE vs Temp Table

CTE

WITH Temp AS (...)
SELECT * FROM Temp

• One query only
• For readability

View

SELECT * FROM V_Data

• Reusable
• Centralized logic

Temp Table

SELECT * INTO #Temp FROM Orders

• Stores actual data
• Can be indexed
• Good for intermediate steps

Final Thoughts

Views are not just “saved queries”.

They’re:

• Abstraction layer
• Security layer
• Stability layer
• Performance tool (with Indexed Views)

Rule of Thumb

• CTE → organize query
• View → reuse logic
• Indexed View → optimize heavy reads
• Temp Table → handle intermediate data

Thanks for reading