Views and Functions – SQL Server | Lesson 21

Published / updated4 Oct 2026

Introduction

In Module 4 you wrote the Class 10-A query with four joins. Every screen that shows a class list needs those same joins. Copying them everywhere means copying their mistakes too.

In this lesson, you save queries inside the database. A view is a saved query you use like a table. A function is saved logic that returns a value or a table.

By the end of this lesson, you will be able to:

Create views with CREATE OR ALTER VIEW and query them like tables.
Use a view to hide soft-deleted rows everywhere at once.
Write a scalar function that returns one value, such as a grade.
Write an inline table-valued function that takes parameters.
Explain when functions help and when they slow queries down.

Why this matters

Lesson 14 warned that soft delete has a cost: every query must remember WHERE IsDeleted = 0. One report that forgets it counts deleted students, and the principal sees the wrong number.

A view solves this once. Reports read vw_StudentDetails, which already filters deleted rows, so no report can forget. The same goes for grades: one function decides "91 is A1", and every report card agrees.

The concept

Example: what a view is

A view is a SELECT saved under a name. It stores no data of its own: each time you query the view, SQL Server runs the saved query against the real tables.

Think of a window in a classroom wall. The window holds nothing; it shows you part of the room. Different windows can show different parts, and some parts stay hidden.

Example
CREATE OR ALTER VIEW dbo.vw_StudentDetails
AS
SELECT Id, AdmissionNumber, FirstName, LastName
FROM dbo.Students
WHERE IsDeleted = 0;

CREATE OR ALTER creates the view, or replaces it if it exists, so the script stays re-runnable. It must be the first statement in its batch, which is why scripts put GO before it.

Example: two kinds of function

Kind	Returns	Called like	SMS example
Scalar function	One value	`dbo.fn_GetGrade(92)`	Grade letter for a percentage
Inline table-valued function	A table	`FROM dbo.fn_GetSectionResults(1, 19)`	Report-card rows for one exam and section

An inline table-valued function is like a view that takes parameters. A view cannot take parameters; a function can.

Example: views and functions compared

Question	View	Inline function	Scalar function
Takes parameters?	No	Yes	Yes
Used in `FROM`?	Yes	Yes	No, used in `SELECT` or `WHERE`
Typical use	Hide joins and filters	Reusable report with inputs	Reusable calculation
Speed risk	Low	Low	Can be high on many rows

Apply it to the SMS

Save the script below as database\021-views-functions.sql and run it. It creates two views and two functions.

Show database/021-views-functions.sql

database/021-views-functions.sql
-- Lesson 21: views and functions.
-- Safe to run more than once: CREATE OR ALTER replaces an existing object.
USE SchoolDb;
GO

-- Students that are not soft-deleted, with a ready-made full name.
CREATE OR ALTER VIEW dbo.vw_StudentDetails
AS
SELECT
    s.Id,
    s.AdmissionNumber,
    s.FirstName,
    s.LastName,
    CONCAT(s.FirstName, N' ', s.LastName) AS FullName,
    s.DateOfBirth,
    s.Gender,
    s.Status
FROM dbo.Students AS s
WHERE s.IsDeleted = 0;
GO

-- Current-year enrollments with class and section names.
CREATE OR ALTER VIEW dbo.vw_CurrentEnrollments
AS
SELECT
    e.Id AS EnrollmentId,
    e.StudentId,
    sd.AdmissionNumber,
    sd.FullName,
    c.Id AS ClassId,
    c.Name AS ClassName,
    sec.Id AS SectionId,
    sec.Name AS SectionName,
    e.RollNumber
FROM dbo.Enrollments AS e
INNER JOIN dbo.AcademicYears AS ay ON ay.Id = e.AcademicYearId
INNER JOIN dbo.vw_StudentDetails AS sd ON sd.Id = e.StudentId
INNER JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
INNER JOIN dbo.Classes AS c ON c.Id = sec.ClassId
WHERE ay.IsCurrent = 1;
GO

-- Scalar function: the grade for a percentage (scale in the course reference).
CREATE OR ALTER FUNCTION dbo.fn_GetGrade (@Percentage DECIMAL(5, 2))
RETURNS VARCHAR(2)
AS
BEGIN
    RETURN CASE
        WHEN @Percentage IS NULL THEN NULL
        WHEN @Percentage >= 91 THEN 'A1'
        WHEN @Percentage >= 81 THEN 'A2'
        WHEN @Percentage >= 71 THEN 'B1'
        WHEN @Percentage >= 61 THEN 'B2'
        WHEN @Percentage >= 51 THEN 'C1'
        WHEN @Percentage >= 41 THEN 'C2'
        WHEN @Percentage >= 33 THEN 'D'
        ELSE 'E'
    END;
END;
GO

-- Inline table-valued function: one row per paper per student, for one exam and section.
CREATE OR ALTER FUNCTION dbo.fn_GetSectionResults (@ExamId INT, @SectionId INT)
RETURNS TABLE
AS
RETURN
    SELECT
        ce.RollNumber,
        ce.FullName,
        sub.Name AS Subject,
        m.MarksObtained,
        es.MaxMarks,
        m.IsAbsent,
        dbo.fn_GetGrade(100.0 * m.MarksObtained / es.MaxMarks) AS Grade
    FROM dbo.Marks AS m
    INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
    INNER JOIN dbo.Subjects AS sub ON sub.Id = es.SubjectId
    INNER JOIN dbo.vw_CurrentEnrollments AS ce ON ce.EnrollmentId = m.EnrollmentId
    WHERE es.ExamId = @ExamId
      AND ce.SectionId = @SectionId;
GO

-- Check: the objects this script created.
SELECT name AS ObjectName, type_desc AS ObjectType
FROM sys.objects
WHERE name IN ('vw_StudentDetails', 'vw_CurrentEnrollments', 'fn_GetGrade', 'fn_GetSectionResults')
ORDER BY name;
GO

The last query lists the four new objects:

ObjectName	ObjectType
fn_GetGrade	SQL_SCALAR_FUNCTION
fn_GetSectionResults	SQL_INLINE_TABLE_VALUED_FUNCTION
vw_CurrentEnrollments	VIEW
vw_StudentDetails	VIEW

Notice that vw_CurrentEnrollments is built on vw_StudentDetails. A view can use another view, so the soft-delete rule is written exactly once.

Example: the class list from a view

The four-join query from Lesson 17 becomes one short query:

Example
SELECT RollNumber, AdmissionNumber, FullName
FROM dbo.vw_CurrentEnrollments
WHERE ClassId = 10
  AND SectionName = 'A'
ORDER BY RollNumber;

RollNumber	AdmissionNumber	FullName
1	ADM-2017-0001	Aarav Sharma
2	ADM-2017-0002	Diya Patel
3	ADM-2017-0003	Kabir Singh
4	ADM-2017-0004	Meera Iyer

Example: the view hides deleted students

The demo script soft-deletes Meera inside a transaction, counts 10-A through the view, and rolls back:

Example
BEGIN TRANSACTION;

UPDATE dbo.Students
SET IsDeleted = 1, DeletedAt = SYSUTCDATETIME()
WHERE AdmissionNumber = 'ADM-2017-0004';  -- Meera

SELECT COUNT(*) AS StudentsIn10A
FROM dbo.vw_CurrentEnrollments
WHERE ClassId = 10
  AND SectionName = 'A';

ROLLBACK TRANSACTION;

The count is 3. No report that uses the view needs its own IsDeleted filter.

Example: the grade function

fn_GetGrade turns a percentage into a grade using the school's scale: 91 and above is A1, down to E below 33. NULL, for an absent student, gives NULL:

Grade95	Grade67	Grade33	Grade20	GradeAbsent
A1	B2	D	E	NULL

Example: reading the grade function

Open the script and read fn_GetGrade from top to bottom:

CREATE OR ALTER FUNCTION dbo.fn_GetGrade (@Percentage DECIMAL(5, 2)) names the function and its one parameter: an input value with a name and a type.
RETURNS VARCHAR(2) says what comes back: a short text such as A1.
RETURN CASE ... END checks the bands from the top. The first true line wins, so 92 stops at >= 91 and never reaches >= 81.
WHEN @Percentage IS NULL THEN NULL handles absent students first.

The whole function is one expression. That keeps it simple to test, and simple for SQL Server to inline into a query.

Example: a function that returns a table

fn_GetSectionResults(1, 19) returns Unit Test 1 results for section 19, which is 10-A:

RollNumber	FullName	Subject	MarksObtained	Grade
1	Aarav Sharma	Mathematics	92.00	A1
1	Aarav Sharma	Science	88.00	A2
2	Diya Patel	Mathematics	85.00	A2
2	Diya Patel	Science	91.00	A1
3	Kabir Singh	Mathematics	67.00	B2
3	Kabir Singh	Science	72.00	B1
4	Meera Iyer	Mathematics	95.00	A1
4	Meera Iyer	Science	NULL	NULL

Change the exam or section number, and the same function produces any report card. In Module 11, the API's report-card endpoint (Lesson 82) builds the same report with its own query, and keeps the pass and grade rules in C#, where unit tests can check them.

Show queries/021-views-functions.sql

queries/021-views-functions.sql
-- Lesson 21: using views and functions. Read-only: the soft-delete test is rolled back.
USE SchoolDb;
GO

-- 1. A view is queried like a table: the 10-A list in one short query.
SELECT RollNumber, AdmissionNumber, FullName
FROM dbo.vw_CurrentEnrollments
WHERE ClassId = 10
  AND SectionName = 'A'
ORDER BY RollNumber;

-- 2. The view hides soft-deleted students automatically.
BEGIN TRANSACTION;

UPDATE dbo.Students
SET IsDeleted = 1, DeletedAt = SYSUTCDATETIME()
WHERE AdmissionNumber = 'ADM-2017-0004';  -- Meera

SELECT COUNT(*) AS StudentsIn10A
FROM dbo.vw_CurrentEnrollments
WHERE ClassId = 10
  AND SectionName = 'A';

ROLLBACK TRANSACTION;

-- 3. A scalar function, called for single values.
SELECT
    dbo.fn_GetGrade(95) AS Grade95,
    dbo.fn_GetGrade(67) AS Grade67,
    dbo.fn_GetGrade(33) AS Grade33,
    dbo.fn_GetGrade(20) AS Grade20,
    dbo.fn_GetGrade(NULL) AS GradeAbsent;

-- 4. A table-valued function, used like a table with parameters:
--    Unit Test 1 (exam 1) results for 10-A (section 19).
SELECT RollNumber, FullName, Subject, MarksObtained, Grade
FROM dbo.fn_GetSectionResults(1, 19)
ORDER BY RollNumber, Subject;
GO

Example: the cost of scalar functions

A scalar function runs once for every row it is used on. Calling fn_GetGrade for eight marks is instant; calling it in a WHERE clause over 120,000 marks makes SQL Server evaluate it 120,000 times and stops it from using an index on that column.

SQL Server 2019 and later can inline simple scalar functions, copying their logic into the query so they run as fast as plain SQL. Ours is one CASE, so it qualifies. Functions with loops or table access may not, and Lesson 27 shows how to spot them in a plan.

Example: read a saved definition

SQL Server keeps the text of every view and function, including the comment lines written just before it in the same batch. To read it back:

Example
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.vw_StudentDetails');

In SSMS you can also right-click the view under Views, then choose Script View as > CREATE To > New Query Editor Window. Both show the code that is really running, which is not always the same as the copy in someone's folder.

Example: views as a security boundary

A view can show some columns and hide others. A reports user who may read vw_StudentDetails sees names and admission numbers, but not addresses or the IsDeleted flag, because the view does not include them.

In Lesson 28, you give the reports role permission on the views only, never on the tables. The view then decides exactly what reports can see, and the tables stay private.

Example: changing data through a view

A simple view on one table can accept UPDATE and INSERT, which change the table underneath. A view with joins, DISTINCT, or GROUP BY usually cannot, because SQL Server cannot tell which table a change belongs to.

In this course, views are for reading. Changes go through the tables directly or through stored procedures (Lesson 22), where the rules are visible in one place.

Example: indexed views

A normal view runs its query every time. An indexed view stores its result, and SQL Server keeps that copy up to date on every insert, update, and delete. Reads become fast; writes to the base tables become slower.

Indexed views have strict rules, such as WITH SCHEMABINDING and no outer joins. Use one only when a measured, frequently run aggregate is too slow, which is Module 5's performance story in Lessons 26 and 27.

Common mistakes

SELECT * inside a view. Columns added to the table later do not appear, and the view's columns can shift. Fix: list the columns in every view.
Views on views on views. Each layer adds joins nobody remembers. Fix: keep at most one or two layers, and read the plan when it gets slow.
Scalar functions in WHERE on big tables. Every row runs the function. Fix: compare the bare column, or use an inline table-valued function.
Expecting a view to store data. It runs its query every time. Fix: for stored results, use a summary table or an indexed view, chosen deliberately.
Forgetting GO before CREATE OR ALTER VIEW. It must start its batch. Fix: put GO before each view, function, procedure, and trigger.

In real projects

From 16 years of .NET projects

The most useful view on any project I have led is the "current, not deleted" view, exactly like vw_CurrentEnrollments. Once reports, APIs, and exports all read from it, bugs about deleted or last year's students stop appearing.

The most expensive mistake I have seen with functions was a scalar function that ran a query inside it, called in a report over 2 million rows. The report took 40 minutes.

Rewriting it as a join took it to 6 seconds. Functions are great for calculations; a function that reads tables deserves a second look.

Best practices

Put shared filters, such as soft delete and current year, in one view.
List columns explicitly in views and functions.
Prefer inline table-valued functions over scalar functions that read tables.
Keep view layers shallow, at most two deep.
Create every view and function with CREATE OR ALTER, after a GO.
Use views for reading; send changes through tables or stored procedures.
Check the real definition in sys.sql_modules before changing a view in production.

Recap

A view is a saved SELECT; it stores no data.
Views hide joins and filters, such as soft-deleted rows, in one place.
A scalar function returns one value; an inline table-valued function returns a table.
Inline table-valued functions are like views with parameters.
Scalar functions run once per row; watch them on large tables.
CREATE OR ALTER keeps scripts re-runnable.

Homework

Library module task: Create vw_AvailableBooks, showing each copy that can be issued right now, with its book title and author. Leave out deleted books and copies that are issued or lost.

A correct result looks like: three rows: "Malgudi Days" copy 1 and both copies of "Wings of Fire". "The Jungle Book" is missing because Advika has it.

Show the solution

library-assignment/021-library-view.sql
-- Library homework, Lesson 21: a view of the copies that can be issued now.
USE SchoolDb;
GO

CREATE OR ALTER VIEW dbo.vw_AvailableBooks
AS
SELECT
    b.Id AS BookId,
    b.Title,
    b.Author,
    c.Id AS BookCopyId,
    c.CopyNumber
FROM dbo.Books AS b
INNER JOIN dbo.BookCopies AS c ON c.BookId = b.Id
WHERE b.IsDeleted = 0
  AND c.Status = 'Available';
GO

SELECT Title, CopyNumber
FROM dbo.vw_AvailableBooks
ORDER BY Title, CopyNumber;
GO

Quiz

What does a view store?
1. A copy of the data
2. A saved query that runs each time
3. An index
4. A backup of the table
Show answer
Answer: B. A saved query that runs each time A view is a named SELECT; it reads the real tables every time.
Why does vw_CurrentEnrollments show 3 students in 10-A after Meera is soft-deleted?
1. The view deletes rows
2. It is built on vw_StudentDetails, which filters IsDeleted = 0
3. Views cache old data
4. Meera was never in 10-A
Show answer
Answer: B. It is built on vw_StudentDetails, which filters IsDeleted = 0 The soft-delete filter lives in one view and every view built on it inherits it.
Which object can take parameters and be used in FROM?
1. A view
2. A scalar function
3. An inline table-valued function
4. A sequence
Show answer
Answer: C. An inline table-valued function An inline table-valued function is like a view with parameters.
What does dbo.fn_GetGrade(NULL) return?
1. E
2. 0
3. NULL
4. An error
Show answer
Answer: C. NULL Absent students have NULL marks, and the function returns NULL for them.
Why can a scalar function in a WHERE clause be slow on a big table?
1. Functions cannot use numbers
2. It runs once per row and can block index use
3. WHERE does not allow functions
4. Scalar functions lock the table
Show answer
Answer: B. It runs once per row and can block index use Row-by-row evaluation and hidden columns make SQL Server scan.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a view? concept

A view is a saved SELECT with a name. It stores no data; every time you query it, SQL Server runs its query against the real tables. Views hide joins and filters, such as vw_CurrentEnrollments hiding four joins and the soft-delete filter.

See the example · Permalink

F What is the difference between a scalar function and a table-valued function? concept

A scalar function returns one value and is used in SELECT or WHERE, such as dbo.fn_GetGrade(92). A table-valued function returns a table and is used in FROM, such as dbo.fn_GetSectionResults(1, 19). An inline table-valued function is like a view that takes parameters.

See the example · Permalink

F Why use a view to hide soft-deleted rows? concept

So the IsDeleted = 0 filter is written once. Every report and API that reads the view automatically leaves deleted students out, and nobody can forget the filter. In the SMS, soft-deleting Meera drops the 10-A count from 4 to 3 through the view.

See the example · Permalink

Mid (3-5 yrs)

M What is the difference between a view and an inline table-valued function? concept

Both are saved queries used in FROM. A view takes no parameters, while an inline table-valued function does, such as an exam and section. SQL Server expands both into the calling query, so both perform like the query written out in full.

See the example · Permalink

M Why can a scalar function make a query slow? concept

It runs once for every row it is applied to, and when used on a column in WHERE, it hides that column from indexes. SQL Server 2019 and later can inline simple scalar functions, but functions that read tables or use loops often cannot be inlined.

See the example · Permalink

M Can you insert or update through a view? concept

Yes, if the change affects one base table and the view has no DISTINCT, GROUP BY, or aggregates. Views with joins usually cannot accept changes to more than one table. In the SMS, views are for reading; changes go through tables or procedures.

See the example · Permalink

Senior (6-9 yrs)

S A report using a scalar function over 2 million rows takes 40 minutes. How do you fix it? scenario

Look at what the function does. If it reads tables, rewrite that logic as a join or an inline table-valued function so SQL Server processes all rows as one set. Compare the plans and timings before and after; this kind of rewrite often turns minutes into seconds.

How to answer: Explain the row-by-row cost and why the optimizer cannot see inside a non-inlined function. Describe the rewrite and how you would prove it with logical reads and duration. Mention checking whether SQL Server's scalar UDF inlining applies first.

See the example · Permalink

S How can views be used as a security boundary? design

Grant reporting users SELECT on views only, never on the tables. The view exposes only the columns and rows they may see, and because the view and tables have the same owner, SQL Server does not check table permissions (ownership chaining). The tables stay private.

How to answer: Combine the design (views as the public shape of the data) with the permission model (GRANT on views, nothing on tables). Use the SMS reports role. Note the limit: a view is not row-level security per user; for that, use filters or Row-Level Security policies.

See the example · Permalink

Architect / Lead (10+ yrs)

A When would you use an indexed view, and what does it cost? design

When a frequently run aggregate over large tables is too slow and must be up to date, such as daily attendance totals. The indexed view stores its result and SQL Server maintains it on every change to the base tables, so reads are fast but writes become slower, and strict rules apply.

How to answer: Present it as one option among summary tables, caching, and better indexes. Weigh freshness against write cost, and mention the restrictions (schema binding, no outer joins). Decide from measured numbers on the real workload.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-21-views-functions (code repository coming soon)
Files: database/021-views-functions.sql, queries/021-views-functions.sql, library-assignment/021-library-view.sql
Video: coming soon

What's next

Views read data. Next, stored procedures change it safely, with checks and a receipt number. Lesson 22: Stored procedures

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: what a view is​

Example: two kinds of function​

Example: views and functions compared​

Apply it to the SMS​

Example: the class list from a view​

Example: the view hides deleted students​

Example: the grade function​

Example: reading the grade function​

Example: a function that returns a table​

Example: the cost of scalar functions​

Example: read a saved definition​

Example: views as a security boundary​

Example: changing data through a view​

Example: indexed views​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: what a view is

Example: two kinds of function

Example: views and functions compared

Apply it to the SMS

Example: the class list from a view

Example: the view hides deleted students

Example: the grade function

Example: reading the grade function

Example: a function that returns a table

Example: the cost of scalar functions

Example: read a saved definition

Example: views as a security boundary

Example: changing data through a view

Example: indexed views

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next