30. Views & Materialized Views -- Simplified Queries

Level: Guided beginner to advanced

ℹ️ What You'll Learn

• View syntax (CREATE VIEW)
• Simplifying complex queries
• Security (restrict column access)
• Updating through views (WITH CHECK OPTION)
• Indexed views (materialized)
• View performance
• Common view mistakes
• Best practices

Views simplify complex queries. Restrict sensitive data. This article teaches views for abstraction and security.

Why Views Matter for Backend Developers

APIs need simplified, secure access:

Frontend: "Get student with class, fees, exam average"
    v
Without view: Complex JOIN, GROUP BY query
WITH view:    Simple: SELECT * FROM StudentSummary
    v
Maintainable, secure, reusable

Views abstract complexity.

View Syntax

CREATE VIEW ViewName AS
SELECT Column1, Column2 FROM Table
WHERE Condition;

-- Use like table:
SELECT * FROM ViewName;

Types:

• Regular view (virtual table, no data stored)
• Indexed view (materialized, data stored, faster)

Example 1: Simplify Complex Query

Create student summary view:

CREATE VIEW StudentSummary AS
SELECT 
    s.StudentId,
    s.Name,
    s.ClassName,
    s.RollNumber,
    COUNT(r.ExamResultId) AS ExamsTaken,
    AVG(r.MarksObtained) AS AvgMarks,
    MAX(r.MarksObtained) AS MaxMarks,
    f.TotalFees,
    f.PaidAmount,
    f.TotalFees - f.PaidAmount AS DueAmount
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
LEFT JOIN FeeAccount f ON s.StudentId = f.StudentId
GROUP BY s.StudentId, s.Name, s.ClassName, s.RollNumber, f.TotalFees, f.PaidAmount;

-- Use view:
SELECT * FROM StudentSummary WHERE ClassName = '10-A';
-- Instead of: 10-line complex query

View hides complexity. Frontend queries simple.

Example 2: Security (Restrict Columns)

Hide sensitive data:

-- Full table
SELECT * FROM User;
-- Returns: UserId, Name, Email, PasswordHash, Role

-- View exposes safe columns only
CREATE VIEW UserPublic AS
SELECT UserId, Name, Email, Role
FROM User;
-- Omits PasswordHash

-- Frontend queries:
SELECT * FROM UserPublic;
-- Returns: UserId, Name, Email, Role (no passwords)

View restricts access to sensitive columns.

Example 3: Security (Restrict Rows)

Show only current academic year data:

CREATE VIEW CurrentAcademicYearFees AS
SELECT 
    f.FeeAccountId,
    s.Name AS Student,
    s.ClassName,
    f.TotalFees,
    f.PaidAmount,
    f.DueDate,
    f.Status
FROM FeeAccount f
INNER JOIN Student s ON f.StudentId = s.StudentId
WHERE f.AcademicYear = YEAR(GETDATE());
-- Always shows current year

-- Frontend:
SELECT * FROM CurrentAcademicYearFees;
-- Only current year fees shown

View auto-filters to current year (no manual WHERE needed).

Example 4: View with WHERE (WITH CHECK OPTION)

Prevent invalid updates through view:

CREATE VIEW StudentClass10 AS
SELECT StudentId, Name, ClassName, Status
FROM Student
WHERE ClassName = '10-A'
WITH CHECK OPTION;
-- WITH CHECK OPTION: Enforce WHERE in updates

-- Valid update:
UPDATE StudentClass10 SET Name = 'Updated' WHERE StudentId = 1;
-- Works: Row still in class 10-A

-- Invalid update:
UPDATE StudentClass10 SET ClassName = '11-A' WHERE StudentId = 1;
-- ERROR: Violates CHECK OPTION (no longer in 10-A)

WITH CHECK OPTION: Prevent updates that violate view's WHERE.

Example 5: Indexed View (Materialized)

Pre-compute expensive aggregate:

CREATE VIEW vw_ClassStatistics
WITH SCHEMABINDING  -- Required for indexed view
AS
SELECT 
    s.ClassName,
    COUNT(*) AS StudentCount,
    AVG(r.MarksObtained) AS AvgMarks,
    MAX(r.MarksObtained) AS MaxMarks,
    MIN(r.MarksObtained) AS MinMarks
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
GROUP BY s.ClassName;

-- Create unique clustered index (materializes view)
CREATE UNIQUE CLUSTERED INDEX idx_ClassStatistics
ON vw_ClassStatistics (ClassName);

-- Query now reads from indexed view (fast):
SELECT * FROM vw_ClassStatistics WHERE ClassName = '10-A';
-- Uses materialized data (computed once, not per query)

Indexed view: Stores computed data. Fast queries. Use for expensive aggregates.

Example 6: View for Reporting

Fee collection report:

CREATE VIEW FeeCollectionReport AS
SELECT 
    s.ClassName,
    COUNT(DISTINCT s.StudentId) AS StudentCount,
    SUM(f.TotalFees) AS TotalFees,
    SUM(f.PaidAmount) AS AmountCollected,
    SUM(f.TotalFees) - SUM(f.PaidAmount) AS OutstandingAmount,
    CAST(SUM(f.PaidAmount) * 100.0 / SUM(f.TotalFees) AS DECIMAL(5,2)) AS CollectionPercentage
FROM Student s
LEFT JOIN FeeAccount f ON s.StudentId = f.StudentId
GROUP BY s.ClassName;

-- Frontend report query:
SELECT * FROM FeeCollectionReport
ORDER BY CollectionPercentage DESC;
-- Simple, reports aggregated data

View encapsulates complex aggregation.

Example 7: Join View (Multiple Tables)

Student with teacher and exam info:

CREATE VIEW StudentExamDetail AS
SELECT 
    s.Name AS Student,
    s.ClassName,
    sub.Name AS Subject,
    t.Name AS Teacher,
    e.ExamName,
    r.MarksObtained,
    CAST(r.MarksObtained * 100.0 / e.MaxMarks AS DECIMAL(5,2)) AS Percentage
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId
INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId
INNER JOIN Teacher t ON sub.TeacherId = t.TeacherId;

-- Frontend:
SELECT * FROM StudentExamDetail WHERE Student = 'Ravi Kumar';
-- All related data in one query

View joins multiple tables. Frontend gets complete context.

---

Common View Mistakes

Mistake 1: View with ORDER BY (meaningless)

Wrong:

CREATE VIEW StudentsByMarks AS
SELECT * FROM Student
ORDER BY Name;
-- ORDER BY in view doesn't persist

Result: View always returns unsorted data.

Fix: ORDER BY in outer SELECT:

SELECT * FROM StudentsByMarks ORDER BY Name;

Mistake 2: Indexed view without SCHEMABINDING

Wrong:

CREATE VIEW vw_ClassStatistics AS
SELECT ClassName, COUNT(*) FROM Student
GROUP BY ClassName;

-- Try to create index:
CREATE CLUSTERED INDEX idx ON vw_ClassStatistics (ClassName);
-- ERROR: SCHEMABINDING required

Fix: Add SCHEMABINDING:

CREATE VIEW vw_ClassStatistics
WITH SCHEMABINDING
AS
SELECT ClassName, COUNT(*) FROM dbo.Student
GROUP BY ClassName;

CREATE CLUSTERED INDEX idx ON vw_ClassStatistics (ClassName);

SCHEMABINDING: Locks schema (columns can't change).

Mistake 3: View with expensive computation (per query)

Wrong:

CREATE VIEW StudentPercentiles AS
SELECT 
    StudentId,
    MarksObtained,
    PERCENTILE_CONT(0.5) OVER (ORDER BY MarksObtained) AS Median
FROM ExamResult;
-- Computed every query (if 1M rows: slow)

Fix: Use indexed view:

CREATE VIEW vw_StudentPercentiles
WITH SCHEMABINDING
AS
SELECT ... ;

CREATE UNIQUE CLUSTERED INDEX idx ON vw_StudentPercentiles ...;
-- Computed once, stored

Mistake 4: View with functions (prevents indexing)

Wrong:

CREATE VIEW ActiveStudents AS
SELECT 
    StudentId,
    Name,
    YEAR(GETDATE()) - YEAR(DateOfBirth) AS Age
FROM Student
WHERE Status = 'Active';

-- Try to index:
CREATE CLUSTERED INDEX idx ON ActiveStudents ...;
-- ERROR: Functions prevent indexing

Fix: Remove function from view, apply in query:

CREATE VIEW ActiveStudents AS
SELECT StudentId, Name, DateOfBirth
FROM Student
WHERE Status = 'Active';

-- Query:
SELECT *, YEAR(GETDATE()) - YEAR(DateOfBirth) AS Age
FROM ActiveStudents;

Mistake 5: Over-relying on views (nesting)

Wrong:

CREATE VIEW v1 AS SELECT * FROM Student WHERE ...;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE ...;
CREATE VIEW v3 AS SELECT * FROM v2 WHERE ...;
-- Nested views: Slow, hard to optimize

Fix: Flat views:

CREATE VIEW StudentActive10A AS
SELECT * FROM Student WHERE Status = 'Active' AND ClassName = '10-A';

Avoid nesting views.

---

Best Practices for Views

1. Use for simplification -- Hide complex JOINs
2. Use for security -- Restrict columns/rows
3. Avoid deep nesting -- Flat queries faster
4. Don't ORDER BY in view -- Apply in SELECT
5. Indexed views for expensive aggregates -- Pre-compute
6. SCHEMABINDING for indexed views -- Required
7. Document purpose -- Why view exists
8. Test performance -- Monitor view query cost

---

🎯 Q1: What's the difference between view and table?

Table: Stores data physically. View: Virtual table (query stored, not data).

Table: SELECT * FROM Student;  -- Reads stored rows
View: SELECT * FROM StudentSummary;  -- Runs query, returns result

View doesn't store data, computed on demand.

🎯 Q2: Can I update data through a view?

Yes, if view follows rules:

• Single table (or with simple JOIN)
• No aggregate functions
• No GROUP BY, DISTINCT
• No expressions in SELECT

UPDATE StudentPublic SET Name = 'New Name' WHERE StudentId = 1;
-- Works: Simple view on single table

Complex view can't update:

UPDATE StudentSummary SET AvgMarks = 85;  -- ERROR: aggregates can't update

Use simple views for updates.

🎯 Q3: What does WITH CHECK OPTION do?

Prevents updates violating view's WHERE:

CREATE VIEW StudentClass10 AS
SELECT * FROM Student
WHERE ClassName = '10-A'
WITH CHECK OPTION;

UPDATE StudentClass10 SET ClassName = '11-A';  -- ERROR
-- Violates WHERE (no longer in 10-A)

Enforces view's WHERE in updates.

🎯 Q4: What's an indexed view (materialized view)?

View with UNIQUE CLUSTERED INDEX:

CREATE VIEW vw_Stats WITH SCHEMABINDING AS ...;
CREATE UNIQUE CLUSTERED INDEX idx ON vw_Stats ...;

Stores computed data. Fast queries (no computation per query).

Use for expensive aggregates.

🎯 Q5: Why use SCHEMABINDING?

Locks schema: Columns in view can't change/drop.

CREATE VIEW vw_Student WITH SCHEMABINDING AS
SELECT StudentId, Name FROM dbo.Student;

-- Can't do:
ALTER TABLE Student DROP COLUMN Name;  -- ERROR: View depends on it

Required for indexed views. Protects schema.

🎯 Q6: Should views be in reports or data access layer?

Views are data layer:

Database: Defines views (StudentSummary, FeeReport)
Backend API: SELECT * FROM StudentSummary
Frontend: Gets simplified data

Views simplify backend queries. Use them in backend SELECT.

Keep complex logic in views, not backend code.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Views for query simplification and security in SQL Server. Try these prompts:

• "Show view to simplify complex student summary query"
• "How do I use views to hide sensitive columns (passwords)?"
• "Explain WITH CHECK OPTION in view updates"
• "What's an indexed view? When should I use one?"
• "Quiz me: ask 5 questions about views, security, indexed views"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

31. Backup, Restore & Disaster Recovery ->