This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create table-valued functions

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Table-valued functions (TVFs) are user-defined database objects in SQL Server and Azure SQL Database that return a table rather than a single scalar value. They enable developers to encapsulate reusable query logic that can be invoked like a table within SQL statements.

Unlike scalar functions, which return a single value, table-valued functions return a result set that can be filtered, joined, aggregated, and queried just like a regular table or view. They are widely used to simplify complex queries, implement reusable business logic, parameterize data retrieval, and support reporting, analytics, and AI-enabled database solutions.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What table-valued functions are
• Inline table-valued functions (iTVFs)
• Multi-statement table-valued functions (MSTVFs)
• How to create, modify, and delete TVFs
• Parameters and return tables
• Performance differences between iTVFs and MSTVFs
• Appropriate use cases
• Best practices

Understanding TVFs is important because they provide reusable, parameterized query logic while integrating seamlessly into T-SQL queries.

What Is a Table-Valued Function?

A table-valued function is a user-defined function that returns a table.

Unlike stored procedures, TVFs can be referenced directly in the FROM clause of a query.

Example:

SELECT *
FROM dbo.fnActiveCustomers();

The returned table behaves like any other table expression.

Benefits of Table-Valed Functions

TVFs provide numerous advantages:

• Encapsulate reusable query logic
• Accept input parameters
• Return structured result sets
• Simplify complex SQL
• Improve maintainability
• Support modular application design
• Can be joined with other tables
• Can be filtered and aggregated

Types of Table-Valued Functions

SQL Server supports two types:

1. Inline Table-Valued Functions (iTVFs)
2. Multi-Statement Table-Valued Functions (MSTVFs)

Understanding the differences is important for the DP-800 exam.

Inline Table-Valued Functions (iTVFs)

An inline TVF consists of a single SELECT statement.

General syntax:

CREATE FUNCTION dbo.FunctionName
(
    @Parameter DataType
)
RETURNS TABLE
AS
RETURN
(
    SELECT ...
);

Example:

CREATE FUNCTION dbo.fnOrdersByCustomer
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Usage:

SELECT *
FROM dbo.fnOrdersByCustomer(100);

Characteristics of Inline TVFs

Inline TVFs:

• Return a single SELECT statement
• Do not declare table variables
• Are generally optimized like parameterized views
• Typically offer the best performance
• Can participate fully in query optimization

For most scenarios, Microsoft recommends using inline TVFs whenever possible.

Multi-Statement Table-Valued Functions (MSTVFs)

A multi-statement TVF allows multiple T-SQL statements.

Unlike inline TVFs, it declares and populates a table variable.

Example:

CREATE FUNCTION dbo.fnLargeOrders
(
    @MinimumAmount MONEY
)
RETURNS @Orders TABLE
(
    OrderID INT,
    CustomerID INT,
    TotalAmount MONEY
)
AS
BEGIN
    INSERT INTO @Orders
    SELECT
        OrderID,
        CustomerID,
        TotalAmount
    FROM Sales.Orders
    WHERE TotalAmount >= @MinimumAmount;
    RETURN;
END;

Characteristics of Multi-Statement TVFs

MSTVFs:

• Support multiple SQL statements
• Allow procedural logic
• Can declare variables
• Can perform multiple INSERT operations into the return table
• Are generally slower than inline TVFs because the optimizer has less information about the returned data

Comparing iTVFs and MSTVFs

Using Parameters

TVFs commonly accept parameters.

Example:

SELECT *
FROM dbo.fnOrdersByCustomer(25);

Parameters make TVFs reusable across different queries.

Joining a TVF with Tables

Because TVFs return tables, they can participate in joins.

Example:

SELECT
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM dbo.fnOrdersByCustomer(100) AS o
INNER JOIN Sales.Customers AS c
    ON o.CustomerID = c.CustomerID;

Using CROSS APPLY

TVFs are frequently used with CROSS APPLY.

Example:

SELECT
    c.CustomerID,
    o.OrderID,
    o.TotalAmount
FROM Sales.Customers AS c
CROSS APPLY dbo.fnOrdersByCustomer(c.CustomerID) AS o;

CROSS APPLY invokes the function once for each row returned by the outer query.

This is one of the most common uses of TVFs.

Using OUTER APPLY

OUTER APPLY behaves similarly to a left outer join.

Example:

SELECT
    c.CustomerID,
    o.OrderID
FROM Sales.Customers AS c
OUTER APPLY dbo.fnOrdersByCustomer(c.CustomerID) AS o;

Customers without matching orders still appear in the result set, with NULL values for the function’s columns.

Modifying a TVF

Use ALTER FUNCTION.

Example:

ALTER FUNCTION dbo.fnOrdersByCustomer
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Dropping a TVF

Example:

DROP FUNCTION dbo.fnOrdersByCustomer;

Viewing a Function Definition

Developers can inspect the function definition using:

sp_helptext 'dbo.fnOrdersByCustomer';

Or:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.fnOrdersByCustomer'));

Schema Binding

TVFs can be created using WITH SCHEMABINDING.

Benefits include:

• Prevents incompatible schema changes
• Improves object stability
• Can be required for certain database features

Deterministic vs. Nondeterministic Functions

A TVF may be:

Deterministic

• Same inputs produce the same outputs.

Nondeterministic

• Results may change between executions.
• Examples include functions using GETDATE() or NEWID().

Deterministic functions are generally preferred for predictable behavior and optimization.

TVFs vs. Views

One of the biggest advantages of a TVF over a view is its ability to accept parameters.

TVFs vs. Stored Procedures

TVFs vs. Scalar Functions

Performance Considerations

Performance is an important exam topic.

Inline TVFs

• Usually have excellent performance.
• The optimizer expands them into the calling query.
• Can benefit from accurate cardinality estimation.
• Often perform similarly to parameterized views.

Multi-Statement TVFs

• Use a table variable internally.
• Historically provided limited cardinality estimates, which could result in less efficient execution plans.
• May perform more slowly on large result sets.

Whenever possible, use an inline TVF unless procedural logic requires a multi-statement implementation.

AI-Enabled Database Scenarios

TVFs are useful in AI-enabled database solutions because they provide reusable, parameterized datasets.

Examples include:

• Returning embeddings associated with a specific document or tenant
• Filtering vectors by category before similarity searches
• Returning AI-ready feature sets for model inference
• Preparing Retrieval-Augmented Generation (RAG) context based on user or document parameters
• Returning standardized datasets for prompt construction
• Producing reusable search result sets for intelligent search

Parameterized TVFs help AI applications retrieve only the data relevant to a specific request.

Best Practices

• Prefer inline TVFs whenever possible.
• Keep functions focused on a single responsibility.
• Use meaningful names such as fnOrdersByCustomer.
• Avoid unnecessary procedural logic.
• Return only the columns required.
• Keep functions deterministic whenever practical.
• Test performance with realistic workloads.
• Use CROSS APPLY or OUTER APPLY appropriately.
• Document business logic within the function.
• Review execution plans when TVFs are used in critical queries.

Common Exam Tips

For the DP-800 exam, remember these key points:

• Table-valued functions return a table.
• TVFs can accept parameters.
• Inline TVFs contain a single SELECT statement.
• Multi-statement TVFs populate a table variable using multiple statements.
• Inline TVFs generally outperform multi-statement TVFs.
• TVFs can be used in the FROM clause.
• TVFs can participate in JOIN, CROSS APPLY, and OUTER APPLY operations.
• Views cannot accept parameters, but TVFs can.
• ALTER FUNCTION modifies an existing TVF.
• DROP FUNCTION removes a TVF.

Practice Exam Questions

Question 1

A developer needs a reusable database object that accepts parameters and returns a result set that can be queried like a table. Which object should be used?

A. Stored procedure

B. Table-valued function

C. Scalar function

D. Trigger

Answer: B

Explanation: A table-valued function returns a table and can be referenced in the FROM clause while accepting input parameters.

Question 2

Which statement correctly describes an inline table-valued function?

A. It populates a table variable using multiple INSERT statements.

B. It returns exactly one scalar value.

C. It consists of a single SELECT statement that defines the returned table.

D. It cannot accept parameters.

Answer: C

Explanation: Inline TVFs are defined by a single SELECT statement and generally provide better performance because they integrate well with the query optimizer.

Question 3

What is one primary advantage of a table-valued function over a view?

A. It always performs faster.

B. It can automatically create indexes.

C. It can accept input parameters.

D. It can modify database schema.

Answer: C

Explanation: Unlike views, table-valued functions support input parameters, making them reusable for parameterized queries.

Question 4

A developer needs to use procedural logic and multiple INSERT statements to build a returned result set. Which type of function should be created?

A. Inline table-valued function

B. Scalar function

C. View

D. Multi-statement table-valued function

Answer: D

Explanation: Multi-statement TVFs allow multiple T-SQL statements and populate a table variable before returning it.

Question 5

Which operator is commonly used to invoke a table-valued function for each row returned by an outer query?

A. UNION

B. CROSS APPLY

C. EXISTS

D. PIVOT

Answer: B

Explanation: CROSS APPLY executes the TVF for each row in the outer query, making it ideal for parameterized row-by-row processing.

Question 6

Why do inline table-valued functions generally outperform multi-statement table-valued functions?

A. They automatically create clustered indexes.

B. They execute in parallel regardless of the query.

C. They are optimized similarly to parameterized views, allowing better query optimization.

D. They always return fewer rows.

Answer: C

Explanation: The SQL Server optimizer can expand inline TVFs into the calling query, producing more efficient execution plans than are typically possible with multi-statement TVFs.

Question 7

Which statement about table-valued functions is correct?

A. They can only return one column.

B. They cannot participate in JOIN operations.

C. They cannot accept parameters.

D. They can be queried in the FROM clause just like a table.

Answer: D

Explanation: TVFs return a table and can be used in the FROM clause, joined with other tables, and filtered like regular tables.

Question 8

Which statement should be used to modify an existing table-valued function?

A. UPDATE FUNCTION

B. MODIFY FUNCTION

C. CREATE FUNCTION

D. ALTER FUNCTION

Answer: D

Explanation: ALTER FUNCTION changes the definition of an existing table-valued function while preserving the object.

Question 9

A developer wants all customers returned, even if a table-valued function produces no matching rows for some customers. Which operator should be used?

A. INNER JOIN

B. CROSS APPLY

C. OUTER APPLY

D. INTERSECT

Answer: C

Explanation: OUTER APPLY behaves similarly to a left outer join, returning all rows from the outer query and NULL values when the TVF produces no matching rows.

Question 10

How can table-valued functions support AI-enabled database solutions?

A. They automatically train machine learning models.

B. They replace vector indexes.

C. They provide reusable, parameterized datasets that simplify intelligent search, feature retrieval, and Retrieval-Augmented Generation (RAG) workflows.

D. They eliminate the need for indexes.

Answer: C

Explanation: TVFs encapsulate reusable, parameterized query logic, making them well suited for AI scenarios such as feature retrieval, intelligent search, and RAG, where filtered and consistent datasets are required.

Go to the DP-800 Exam Prep Hub main page

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create scalar functions

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Scalar functions are reusable database objects that encapsulate business logic and return a single scalar value (such as an integer, string, date, or decimal). They allow developers to centralize calculations, formatting rules, and validation logic, reducing code duplication and improving maintainability.

Scalar functions are widely used in SQL Server and Azure SQL Database applications for tasks such as calculating discounts, formatting names, determining tax amounts, converting units, and implementing business rules. They are also useful in AI-enabled database solutions for standardizing data transformations and feature calculations before data is consumed by analytics or AI models.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What scalar functions are
• How to create, modify, and delete scalar functions
• User-defined scalar functions versus built-in functions
• Function parameters and return values
• Deterministic and nondeterministic functions
• Inline scalar function optimization (scalar UDF inlining)
• Performance considerations
• Best practices for implementation

Understanding scalar functions is important because they promote reusable, maintainable, and consistent business logic.

What Is a Scalar Function?

A scalar function is a database object that:

• Accepts zero or more input parameters
• Executes one or more T-SQL statements
• Returns exactly one scalar value

Examples of scalar values include:

• Integer
• Decimal
• Date
• Time
• String
• Bit
• Uniqueidentifier

Unlike stored procedures, scalar functions always return a single value and can often be used within SQL expressions.

Built-In vs. User-Defined Scalar Functions

SQL Server includes many built-in scalar functions.

Examples include:

• UPPER()
• LOWER()
• LEN()
• ROUND()
• ABS()
• YEAR()
• MONTH()
• DATEADD()
• ISNULL()
• COALESCE()

Developers can also create user-defined scalar functions (UDFs) when built-in functionality does not meet business requirements.

Creating a Scalar Function

Basic syntax:

CREATE FUNCTION dbo.fnCalculateTax
(
    @Amount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Amount * 0.07;
END;

The function accepts one parameter and returns the calculated tax amount.

Calling a Scalar Function

A scalar function is invoked by referencing its name.

Example:

SELECT dbo.fnCalculateTax(100.00);

Result:

7.00

Scalar functions can also be used within SELECT statements.

Example:

SELECT
    InvoiceID,
    Amount,
    dbo.fnCalculateTax(Amount) AS TaxAmount
FROM Sales.Invoices;

Using Multiple Parameters

Functions can accept multiple parameters.

Example:

CREATE FUNCTION dbo.fnCalculateTotal
(
    @Price DECIMAL(10,2),
    @Quantity INT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * @Quantity;
END;

Usage:

SELECT dbo.fnCalculateTotal(25.00,4);

Returns:

100.00

Using Local Variables

Functions may declare local variables.

Example:

CREATE FUNCTION dbo.fnGetDiscount
(
    @Amount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Discount DECIMAL(10,2);
    IF @Amount >= 1000
        SET @Discount = @Amount * 0.10;
    ELSE
        SET @Discount = @Amount * 0.05;
    RETURN @Discount;
END;

Returning Character Values

Scalar functions frequently return strings.

Example:

CREATE FUNCTION dbo.fnFullName
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

Usage:

SELECT dbo.fnFullName('John','Smith');

Returns:

John Smith

Returning Dates

Functions can return date values.

Example:

CREATE FUNCTION dbo.fnNextYear
(
    @CurrentDate DATE
)
RETURNS DATE
AS
BEGIN
    RETURN DATEADD(YEAR,1,@CurrentDate);
END;

Using Functions in Queries

Scalar functions may appear in:

• SELECT
• WHERE
• ORDER BY
• GROUP BY (when appropriate)
• HAVING
• Computed columns
• CHECK constraints (subject to determinism and other SQL Server rules)

Example:

SELECT
    EmployeeID,
    dbo.fnFullName(FirstName,LastName) AS EmployeeName
FROM HumanResources.Employees;

Deterministic Functions

A deterministic function always returns the same result when given the same input values.

Example:

Input: 10
Output: 20

The result never changes.

Examples include:

• Mathematical calculations
• String manipulation
• Unit conversions

Deterministic functions are important for indexed views, persisted computed columns, and other features that require predictable results.

Nondeterministic Functions

A nondeterministic function can return different results even with identical inputs.

Examples:

• GETDATE()
• SYSDATETIME()
• NEWID()
• RAND() (without a fixed seed)

User-defined scalar functions that rely on nondeterministic functions also become nondeterministic.

Scalar UDF Inlining (SQL Server 2019 and Later)

Prior to SQL Server 2019, scalar UDFs often introduced significant performance overhead because they executed row by row.

SQL Server 2019 introduced scalar UDF inlining, allowing eligible scalar functions to be automatically transformed into relational expressions during query optimization.

Benefits include:

• Reduced CPU usage
• Improved query performance
• Better parallelism
• Fewer context switches

Not every scalar function qualifies for inlining. SQL Server considers factors such as the function’s logic, supported constructs, and compatibility level.

For the DP-800 exam, it is important to understand that scalar UDF inlining can significantly improve performance for eligible functions.

Altering a Function

Functions can be modified using ALTER FUNCTION.

Example:

ALTER FUNCTION dbo.fnCalculateTax
(
    @Amount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Amount * 0.08;
END;

Dropping a Function

Example:

DROP FUNCTION dbo.fnCalculateTax;

This removes the function from the database.

Viewing a Function Definition

Developers can examine a function’s definition using:

sp_helptext 'dbo.fnCalculateTax';

Or:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.fnCalculateTax'));

Schema Binding

Scalar functions may use WITH SCHEMABINDING.

Example:

CREATE FUNCTION dbo.fnSquare
(
    @Value INT
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN @Value * @Value;
END;

Schema binding:

• Prevents incompatible schema changes
• Helps ensure object consistency
• May be required for certain database features

Error Handling

Scalar functions have several limitations compared to stored procedures.

For example:

• They cannot use TRY...CATCH.
• They cannot execute dynamic SQL (EXEC or sp_executesql).
• They cannot modify database state (such as inserting into permanent user tables).
• They cannot start or commit transactions.

Because of these restrictions, scalar functions should contain focused, side-effect-free logic.

Scalar Functions vs. Stored Procedures

Scalar Functions vs. Table-Valued Functions

AI-Enabled Database Scenarios

Scalar functions are useful in AI-enabled database solutions for standardizing calculations and transformations.

Examples include:

• Calculating confidence score categories
• Normalizing numeric values
• Formatting prompts before storage
• Standardizing feature values
• Creating reusable text-cleaning logic
• Calculating similarity thresholds
• Generating reusable business metrics
• Computing feature engineering values

Using reusable functions helps ensure consistent preprocessing across AI workflows.

Performance Considerations

Although scalar functions improve code reuse, developers should be aware of performance implications.

Consider the following:

• Excessive scalar function calls on millions of rows can impact performance.
• Scalar UDF inlining in SQL Server 2019 and later can significantly reduce overhead for eligible functions.
• Keep functions simple and deterministic whenever possible.
• Avoid unnecessary computations inside frequently executed functions.
• Test execution plans when scalar functions are used in large queries.

Best Practices

• Keep scalar functions focused on a single task.
• Use descriptive naming conventions such as fnCalculateTax.
• Keep functions deterministic when practical.
• Avoid unnecessary complexity.
• Take advantage of scalar UDF inlining where applicable.
• Document business logic contained in functions.
• Reuse functions rather than duplicating code.
• Test functions with representative data volumes.
• Monitor execution plans for performance bottlenecks.

Common Exam Tips

For the DP-800 exam, remember these key points:

• Scalar functions return exactly one value.
• Scalar functions can accept zero or more parameters.
• They can be used within SELECT, WHERE, ORDER BY, and other SQL expressions.
• Built-in scalar functions differ from user-defined scalar functions.
• ALTER FUNCTION modifies an existing function.
• DROP FUNCTION removes a function.
• Deterministic functions always return the same result for the same inputs.
• SQL Server 2019 introduced scalar UDF inlining to improve the performance of eligible scalar functions.
• Scalar functions cannot modify permanent database objects or execute dynamic SQL.

Practice Exam Questions

Question 1

A developer wants to create a reusable database object that accepts one or more parameters and returns a single calculated value. Which object should be created?

A. Scalar function

B. View

C. Stored procedure

D. Table-valued function

Answer: A

Explanation: A scalar function is designed to accept parameters and return a single scalar value that can be used within SQL expressions.

Question 2

Which statement correctly describes a user-defined scalar function?

A. It always returns a table.

B. It returns exactly one scalar value.

C. It cannot accept parameters.

D. It automatically creates an index.

Answer: B

Explanation: A scalar function returns a single scalar value, such as an integer, string, date, or decimal, and may accept zero or more parameters.

Question 3

A developer wants to use a custom calculation directly within a SELECT statement for every returned row. Which database object is most appropriate?

A. Trigger

B. View

C. Scalar function

D. Sequence

Answer: C

Explanation: Scalar functions can be called directly within SELECT statements and expressions, making them suitable for reusable row-level calculations.

Question 4

Which SQL Server version introduced scalar UDF inlining to improve the performance of eligible scalar functions?

A. SQL Server 2012

B. SQL Server 2014

C. SQL Server 2019

D. SQL Server 2017

Answer: C

Explanation: SQL Server 2019 introduced scalar UDF inlining, allowing eligible scalar functions to be optimized into relational expressions during query compilation.

Question 5

Which of the following is an example of a nondeterministic function?

A. ABS()

B. LEN()

C. GETDATE()

D. UPPER()

Answer: C

Explanation: GETDATE() returns the current system date and time, which changes over time, making it nondeterministic.

Question 6

A developer needs to modify the definition of an existing scalar function while keeping the same object. Which statement should be used?

A. CREATE FUNCTION

B. ALTER FUNCTION

C. UPDATE FUNCTION

D. MODIFY FUNCTION

Answer: B

Explanation: ALTER FUNCTION changes the definition of an existing function without requiring it to be dropped and recreated.

Question 7

Which statement about scalar functions is correct?

A. They can insert rows into permanent user tables.

B. They can execute dynamic SQL.

C. They can begin and commit transactions.

D. They return a single scalar value and can be used in SQL expressions.

Answer: D

Explanation: Scalar functions return one value and can be used in SELECT, WHERE, ORDER BY, and other SQL expressions. They cannot perform operations such as modifying permanent tables or executing dynamic SQL.

Question 8

Why are deterministic scalar functions important?

A. They always create clustered indexes.

B. They always execute faster than built-in functions.

C. They always return the same result for the same input values and are required for certain SQL Server features such as indexed views and persisted computed columns.

D. They automatically improve query parallelism.

Answer: C

Explanation: Deterministic functions consistently return the same output for the same inputs, making them suitable for features that require predictable results.

Question 9

Which statement best describes the relationship between scalar functions and stored procedures?

A. Both can always be used interchangeably within a SELECT statement.

B. Scalar functions return a single value and can be used in SQL expressions, whereas stored procedures are designed for procedural operations and are not used as expressions.

C. Stored procedures always return a single scalar value.

D. Scalar functions can modify permanent tables just like stored procedures.

Answer: B

Explanation: Scalar functions are expression-oriented and return one value, while stored procedures are intended for broader procedural tasks and cannot be invoked as scalar expressions in queries.

Question 10

How can scalar functions benefit AI-enabled database solutions?

A. They automatically train machine learning models.

B. They replace vector indexes.

C. They eliminate the need for ETL processes.

D. They provide reusable and consistent data transformations that help standardize inputs for analytics and AI workloads.

Answer: D

Explanation: Scalar functions encapsulate reusable business logic and transformations, helping ensure that AI models and analytical processes receive consistently prepared data.

Go to the DP-800 Exam Prep Hub main page

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create views

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Views are one of the most commonly used database objects in SQL Server and Azure SQL Database. A view is a virtual table whose contents are defined by a SQL query. Unlike a physical table, a standard view does not store data itself. Instead, it stores the SELECT statement that retrieves data from one or more underlying tables or other views.

Views simplify complex queries, improve security, promote code reuse, and provide an abstraction layer between applications and the underlying database schema. They are frequently used in reporting solutions, business intelligence applications, APIs, and AI-enabled database solutions where consistent access to curated data is essential.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What views are and when to use them
• How to create and modify views
• Simple vs. complex views
• Updatable views
• Schema binding
• Indexed views
• Security considerations
• Best practices for view design

Understanding views is an important skill because they simplify application development while improving maintainability and security.

What Is a View?

A view is a stored SELECT statement that presents data as though it were a table.

Applications can query a view just like a table.

Example:

SELECT *
FROM dbo.vwCustomerOrders;

Although it behaves like a table, the data is retrieved from the underlying objects each time the view is queried (unless the view is indexed).

Benefits of Views

Views provide numerous advantages, including:

• Simplifying complex queries
• Hiding unnecessary columns
• Restricting sensitive data
• Providing a consistent interface for applications
• Improving code maintainability
• Supporting data abstraction
• Enabling reusable business logic
• Simplifying report development

Views help separate the logical database model from the physical implementation.

Creating a View

Basic syntax:

CREATE VIEW dbo.vwCustomers
AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    EmailAddress
FROM dbo.Customers;

Applications can query the view:

SELECT *
FROM dbo.vwCustomers;

Creating a View from Multiple Tables

Views commonly combine data from related tables.

Example:

CREATE VIEW dbo.vwCustomerOrders
AS
SELECT
    c.CustomerID,
    c.FirstName,
    c.LastName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID;

This view simplifies reporting by eliminating the need for repeated JOIN statements.

Simple Views

A simple view references a single table and contains little or no additional logic.

Example:

CREATE VIEW dbo.vwActiveProducts
AS
SELECT
    ProductID,
    ProductName,
    Price
FROM dbo.Products
WHERE IsActive = 1;

Simple views are often updatable.

Complex Views

A complex view may include:

• Multiple tables
• JOINs
• GROUP BY
• Aggregate functions
• UNION
• DISTINCT
• Calculated columns
• Subqueries

Example:

CREATE VIEW dbo.vwMonthlySales
AS
SELECT
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    SUM(TotalAmount) AS MonthlySales
FROM dbo.Orders
GROUP BY
    YEAR(OrderDate),
    MONTH(OrderDate);

Complex views are primarily used for reporting and analytics.

Using Aliases

Column aliases improve readability.

Example:

SELECT
    CustomerID,
    FirstName + ' ' + LastName AS FullName
FROM dbo.Customers;

Meaningful column names make views easier to consume.

Filtering Data

Views frequently filter rows.

Example:

CREATE VIEW dbo.vwOpenOrders
AS
SELECT *
FROM dbo.Orders
WHERE Status = 'Open';

Applications automatically see only open orders.

Using Calculated Columns

Views can include calculated values.

Example:

SELECT
    ProductName,
    UnitPrice,
    Quantity,
    UnitPrice * Quantity AS ExtendedPrice
FROM dbo.OrderDetails;

Calculated columns eliminate repeated calculations across applications.

Modifying a View

Views can be modified using:

ALTER VIEW dbo.vwCustomers
AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    EmailAddress,
    PhoneNumber
FROM dbo.Customers;

ALTER VIEW updates the stored definition while preserving permissions.

Deleting a View

To remove a view:

DROP VIEW dbo.vwCustomers;

Only the view is removed; the underlying tables remain unchanged.

Viewing the Definition of a View

Developers can inspect a view’s definition using:

sp_helptext 'dbo.vwCustomers';

Or:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.vwCustomers'));

This is useful for maintenance and troubleshooting.

Updatable Views

Many views support INSERT, UPDATE, and DELETE operations.

Generally, a view is updatable when it:

• References a single base table
• Does not contain aggregate functions
• Does not contain GROUP BY
• Does not contain DISTINCT
• Does not contain UNION
• Does not contain calculated aggregate results

Example:

UPDATE dbo.vwCustomers
SET EmailAddress = 'newemail@example.com'
WHERE CustomerID = 100;

The underlying table is updated.

WITH CHECK OPTION

WITH CHECK OPTION prevents updates or inserts that would cause rows to no longer satisfy the view’s filter.

Example:

CREATE VIEW dbo.vwActiveEmployees
AS
SELECT *
FROM dbo.Employees
WHERE IsActive = 1
WITH CHECK OPTION;

This ensures that modifications through the view maintain its filtering criteria.

Schema Binding

Views can be created using WITH SCHEMABINDING.

Example:

CREATE VIEW dbo.vwCustomerSales
WITH SCHEMABINDING
AS
SELECT
    CustomerID,
    COUNT_BIG(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerID;

Benefits include:

• Prevents changes to referenced tables that would invalidate the view
• Required for indexed views
• Improves schema stability

When schema binding is used:

• Table names must include the schema name.
• Referenced tables cannot be dropped or modified in ways that break the view until the view is altered or dropped.

Indexed Views

Normally, views do not store data.

An indexed view stores the results of the view physically by creating a unique clustered index on the view.

Benefits:

• Faster query performance
• Reduced computation for expensive aggregations
• Useful for reporting workloads

Requirements include:

• WITH SCHEMABINDING
• Deterministic expressions
• Additional SQL Server restrictions
• A unique clustered index created first

Example:

CREATE UNIQUE CLUSTERED INDEX IX_vwCustomerSales
ON dbo.vwCustomerSales(CustomerID);

Keep in mind that indexed views can improve read performance but may increase the cost of INSERT, UPDATE, and DELETE operations because the indexed view must also be maintained.

Views and Security

Views are commonly used to restrict access to sensitive information.

Example:

Base table:

EmployeeID
Name
Salary
SocialSecurityNumber

View:

EmployeeID
Name

Users receive access to the view instead of the underlying table.

This supports the principle of least privilege by exposing only the necessary columns and rows.

Nested Views

A view can reference another view.

Example:

Orders
   │
vwOpenOrders
   │
vwRecentOpenOrders

Although supported, excessive nesting can:

• Reduce performance
• Complicate troubleshooting
• Make execution plans harder to understand

Microsoft generally recommends minimizing unnecessary layers of nested views.

Limitations of Views

Standard views:

• Do not normally store data
• Cannot accept parameters (use table-valued functions if parameters are required)
• May become invalid if underlying objects change (unless schema binding is used)
• May not always improve performance
• Can become difficult to maintain if overly complex

Views vs. Tables

Views vs. Stored Procedures

AI-Enabled Database Scenarios

Views are valuable in AI-enabled database solutions because they provide a consistent and secure layer over operational data.

Common uses include:

• Creating curated datasets for machine learning
• Exposing only relevant columns for AI models
• Simplifying feature engineering queries
• Combining business data with vector metadata
• Preparing reporting datasets for model evaluation
• Restricting sensitive information before AI processing

Views help ensure that AI applications consume consistent, high-quality data while reducing the complexity of application queries.

Best Practices

• Use meaningful and consistent naming conventions (for example, vwCustomerOrders).
• Keep views focused on a single business purpose.
• Avoid unnecessary nested views.
• Use aliases to improve readability.
• Use WITH SCHEMABINDING when appropriate, especially for indexed views.
• Consider indexed views only when query performance benefits outweigh maintenance costs.
• Grant permissions to views instead of base tables when restricting data access.
• Document complex business logic contained within views.
• Periodically review execution plans to ensure views are not introducing unnecessary overhead.

Common Exam Tips

For the DP-800 exam, remember these key points:

• A view is a stored SELECT statement that behaves like a virtual table.
• Standard views do not physically store data.
• Indexed views physically store data after a unique clustered index is created.
• ALTER VIEW modifies an existing view while preserving permissions.
• WITH CHECK OPTION ensures rows modified through a view continue to satisfy the view’s filter.
• WITH SCHEMABINDING prevents incompatible schema changes and is required for indexed views.
• Simple views are often updatable; views containing aggregates, GROUP BY, DISTINCT, or UNION generally are not.
• Views are commonly used to simplify queries and improve security.

Practice Exam Questions

Question 1

A developer wants to simplify a frequently used query that joins the Customers and Orders tables without duplicating the JOIN logic throughout an application. Which database object should be created?

A. A stored procedure

B. A trigger

C. A view

D. A sequence

Answer: C