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

Explanation: A view stores a SELECT statement that can join multiple tables, allowing applications to query the view instead of repeatedly writing the same JOIN logic.

Question 2

Which statement correctly describes a standard SQL Server view?

A. It always stores its data physically.

B. It stores the SELECT statement that defines the virtual table.

C. It automatically creates a clustered index.

D. It requires a PRIMARY KEY.

Answer: B

Explanation: A standard view stores only its definition (the SELECT statement). Data is retrieved from the underlying tables each time the view is queried.

Question 3

A database administrator wants to prevent changes to the underlying tables that would invalidate a view. Which option should be used when creating the view?

A. WITH CHECK OPTION

B. ENCRYPTION

C. WITH SCHEMABINDING

D. RECOMPILE

Answer: C

Explanation: WITH SCHEMABINDING binds the view to the schema of the referenced tables, preventing changes that would invalidate the view.

Question 4

Which statement about indexed views is correct?

A. They require a unique clustered index before additional indexes can be created.

B. They automatically update only once per day.

C. They cannot reference aggregate functions.

D. They never increase the cost of data modifications.

Answer: A

Explanation: An indexed view requires WITH SCHEMABINDING and a unique clustered index as the first index. Data modifications to underlying tables also update the indexed view.

Question 5

A view is defined with the following clause:

WITH CHECK OPTION

What is the primary purpose of this clause?

A. To encrypt the view definition.

B. To ensure that INSERT and UPDATE operations through the view continue to satisfy the view’s filtering criteria.

C. To improve query performance.

D. To automatically rebuild indexes.

Answer: B

Explanation: WITH CHECK OPTION prevents modifications through the view that would produce rows no longer visible through that view.

Question 6

Which characteristic generally allows a view to be updatable?

A. It contains GROUP BY and aggregate functions.

B. It contains a UNION operator.

C. It references a single base table without aggregate operations.

D. It contains DISTINCT and calculated aggregates.

Answer: C

Explanation: Simple views that reference a single base table and avoid constructs such as GROUP BY, DISTINCT, and UNION are often updatable.

Question 7

Which statement best describes an indexed view?

A. It always executes more slowly than a standard view.

B. It physically stores the results of the view after a unique clustered index is created.

C. It can only reference one table.

D. It cannot be queried using SELECT statements.

Answer: B

Explanation: Indexed views materialize their data through a unique clustered index, improving performance for certain read-heavy workloads.

Question 8

Why are views commonly used to improve database security?

A. They automatically encrypt data.

B. They replace the need for permissions.

C. They allow administrators to expose only selected columns and rows while restricting access to the underlying tables.

D. They prevent all updates to data.

Answer: C

Explanation: Views can limit the data users see, making them an effective way to implement the principle of least privilege.

Question 9

A developer modifies an existing view by using ALTER VIEW. What happens to the permissions already granted on that view?

A. They are automatically removed.

B. They are transferred to the underlying tables.

C. They are preserved.

D. They are converted to DENY permissions.

Answer: C

Explanation: Using ALTER VIEW changes the view definition while preserving existing permissions on the view.

Question 10

Which statement best explains why views are useful in AI-enabled database solutions?

A. They automatically generate machine learning models.

B. They replace the need for indexes.

C. They eliminate all data transformations.

D. They provide consistent, reusable, and secure datasets that simplify AI data preparation.

Answer: D

Explanation: Views provide a stable abstraction layer that exposes curated, consistent datasets while helping to restrict sensitive data, making them valuable for analytics and AI workloads.

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%)
   --> Design and implement database objects
      --> Design and Implement SEQUENCES

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

Many database applications require automatically generated numeric values for records such as order numbers, invoice numbers, customer identifiers, shipment IDs, and transaction references. While SQL Server developers have traditionally relied on IDENTITY columns to generate sequential numbers, SQL Server also provides a more flexible object called a SEQUENCE.

A SEQUENCE is a user-defined database object that generates a sequence of numeric values according to rules that you specify. Unlike an IDENTITY column, a SEQUENCE is independent of any table and can be shared across multiple tables or applications.

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

• What SEQUENCE objects are
• How SEQUENCES differ from IDENTITY columns
• How to create and use SEQUENCES
• Sequence options such as START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, and CACHE
• Performance considerations
• Best practices and common use cases

Understanding SEQUENCES is important because they provide greater flexibility for generating unique numeric values across modern SQL Server and Azure SQL Database solutions.

What Is a SEQUENCE?

A SEQUENCE is a schema-bound database object that generates a series of numeric values.

Unlike an IDENTITY column:

• It is independent of tables.
• Multiple tables can use the same SEQUENCE.
• Values can be generated before an INSERT occurs.
• Applications can request values whenever needed.

The database maintains the current value of the sequence.

Common Use Cases

SEQUENCES are commonly used for:

• Invoice numbers
• Purchase order numbers
• Ticket numbers
• Customer IDs across multiple tables
• Order tracking numbers
• Shipment numbers
• Financial transaction identifiers
• Distributed applications
• Data warehouse surrogate keys

SEQUENCE vs. IDENTITY

A common DP-800 exam objective is knowing when to choose a SEQUENCE instead of an IDENTITY column.

Creating a SEQUENCE

Basic syntax:

CREATE SEQUENCE dbo.OrderSequence
AS INT
START WITH 1
INCREMENT BY 1;

This sequence:

• Starts at 1
• Increments by 1
• Generates INT values

Using NEXT VALUE FOR

Values are generated using the NEXT VALUE FOR function.

Example:

SELECT NEXT VALUE FOR dbo.OrderSequence;

Output:

1

The next execution returns:

2

Then:

3

Each call advances the sequence.

Using a SEQUENCE During INSERT

Example:

INSERT INTO Orders
(
    OrderID,
    CustomerID
)
VALUES
(
    NEXT VALUE FOR dbo.OrderSequence,
    1001
);

The generated sequence value becomes the OrderID.

Sharing a SEQUENCE Across Multiple Tables

One of the biggest advantages of SEQUENCES is that multiple tables can use the same object.

Example:

OrderSequence
       │
 ┌─────┴─────┐
 │           │
Orders   ArchivedOrders

Both tables generate identifiers from the same sequence.

This guarantees unique values across both tables.

Choosing the Data Type

Supported numeric types include:

• TINYINT
• SMALLINT
• INT
• BIGINT
• DECIMAL
• NUMERIC

Example:

CREATE SEQUENCE dbo.InvoiceSequence
AS BIGINT;

Choose a type large enough for expected future growth.

START WITH

The START WITH clause specifies the first value.

Example:

CREATE SEQUENCE dbo.InvoiceSequence
AS INT
START WITH 1000;

Generated values:

1000
1001
1002

INCREMENT BY

Defines how much the sequence changes.

Example:

INCREMENT BY 10

Generated values:

10
20
30
40

Negative increments are also supported.

Example:

INCREMENT BY -1

Produces:

100
99
98
97

MINVALUE and MAXVALUE

A sequence can define minimum and maximum values.

Example:

CREATE SEQUENCE dbo.SmallSequence
AS INT
MINVALUE 1
MAXVALUE 100;

After reaching the maximum value, behavior depends on whether CYCLE is enabled.

CYCLE Option

The CYCLE option restarts the sequence after reaching its maximum (or minimum for descending sequences).

Example:

CREATE SEQUENCE dbo.TestSequence
AS INT
START WITH 1
MAXVALUE 5
CYCLE;

Generated values:

1
2
3
4
5
1
2

Without CYCLE, requesting another value after reaching the limit results in an error.

Use CYCLE only when reused values are acceptable.

NO CYCLE

NO CYCLE is the default behavior.

Example:

CREATE SEQUENCE dbo.OrderSequence
AS INT
NO CYCLE;

Once the maximum value is reached, SQL Server raises an error rather than restarting.

This is appropriate for identifiers that must remain unique.

CACHE Option

To improve performance, SQL Server can cache sequence values in memory.

Example:

CREATE SEQUENCE dbo.OrderSequence
AS INT
CACHE 100;

Benefits:

• Fewer disk writes
• Higher throughput
• Better scalability

Trade-off:

If SQL Server stops unexpectedly, cached values that were not used are lost, resulting in gaps in the sequence.

NO CACHE

Disables sequence caching.

Example:

NO CACHE

Benefits:

• Reduces gaps caused by unexpected shutdowns

Trade-offs:

• Slightly slower performance
• Increased metadata updates

Restarting a SEQUENCE

A sequence can be restarted.

Example:

ALTER SEQUENCE dbo.OrderSequence
RESTART WITH 5000;

The next generated value will be 5000.

This is useful after data migrations or when implementing new numbering schemes.

Altering a SEQUENCE

Existing sequences can be modified.

Example:

ALTER SEQUENCE dbo.OrderSequence
INCREMENT BY 5;

Future values increase by 5.

Dropping a SEQUENCE

Example:

DROP SEQUENCE dbo.OrderSequence;

This removes the sequence object from the database.

Obtaining Multiple Sequence Values

Applications can retrieve sequence values before performing inserts.

Example:

DECLARE @OrderID INT;
SET @OrderID =
NEXT VALUE FOR dbo.OrderSequence;

This is useful when:

• Creating parent-child records
• Generating invoice numbers
• Passing identifiers between services
• Building distributed workflows

Sequence Gaps

An important exam concept is that SEQUENCES do not guarantee gap-free numbering.

Gaps may occur because of:

• Transaction rollbacks
• Application failures
• Cached values lost during server restart
• Deleted rows
• Unused generated values

Therefore, SEQUENCES should not be used when legal or regulatory requirements demand consecutive numbers with no gaps.

Performance Considerations

SEQUENCES generally perform very well.

Performance is improved by:

• Using CACHE
• Selecting appropriate data types
• Avoiding unnecessary contention
• Sharing sequences when appropriate

High-volume OLTP systems often use cached sequences for improved throughput.

SEQUENCES in Distributed Applications

Because SEQUENCES are independent objects, they are useful in distributed architectures.

Examples include:

• Microservices
• Azure Functions
• Event-driven systems
• Service Bus workflows
• Multi-table transactional systems

Applications can reserve identifiers before inserting data.

AI-Enabled Database Scenarios

Although SEQUENCES are not AI-specific, they are useful in AI-enabled database solutions for generating unique identifiers for:

• AI inference requests
• Prompt execution logs
• Conversation sessions
• Vector embedding batches
• Training jobs
• Experiment tracking
• Model evaluation records

Using a shared sequence ensures consistent identifiers across related AI components.

Best Practices

• Use SEQUENCES when multiple tables require a common numbering scheme.
• Use BIGINT if long-term growth is expected.
• Use CACHE for high-throughput transactional workloads.
• Avoid relying on sequence values being gap-free.
• Do not use CYCLE for primary keys or other values that must remain globally unique.
• Choose START WITH carefully to accommodate business requirements.
• Document shared sequences to prevent accidental reuse.
• Monitor sequence exhaustion when using small numeric data types.
• Restart sequences only after careful planning.

Common Exam Tips

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

• A SEQUENCE is a database object, not a table property.
• NEXT VALUE FOR retrieves the next sequence value.
• Multiple tables can share the same SEQUENCE.
• SEQUENCES can generate values before an INSERT statement.
• CACHE improves performance but may introduce gaps after an unexpected shutdown.
• Transaction rollbacks do not return consumed sequence values.
• CYCLE restarts a sequence after reaching its limit; NO CYCLE raises an error instead.
• SEQUENCES are often preferred over IDENTITY when values must be shared across tables or generated outside of INSERT operations.

Practice Exam Questions

Question 1

A developer needs a single numbering mechanism that can generate unique identifiers for both the Orders and ArchivedOrders tables. Which feature should be used?

A. A DEFAULT constraint

B. An IDENTITY column

C. A computed column

D. A SEQUENCE

Answer: D

Explanation: A SEQUENCE is an independent database object that can be shared by multiple tables, making it ideal for generating unique identifiers across related tables.

Question 2

Which statement best describes a SEQUENCE object?

A. It is bound to a single table and generates values only during INSERT operations.

B. It can only generate BIGINT values.

C. It automatically creates a clustered index.

D. It is an independent database object that generates numeric values according to defined rules.

Answer: D

Explanation: A SEQUENCE is a standalone database object that can generate numeric values independently of any table and supports several numeric data types.

Question 3

Which function retrieves the next available value from a SQL Server SEQUENCE?

A. NEXT IDENTITY

B. GET NEXT

C. NEXT VALUE FOR

D. CURRENT VALUE

Answer: C

Explanation: The NEXT VALUE FOR function retrieves and advances a SEQUENCE to its next value.

Question 4

Why might a developer choose a SEQUENCE instead of an IDENTITY column?

A. Because SEQUENCES cannot contain gaps.

B. Because a SEQUENCE automatically enforces referential integrity.

C. Because a SEQUENCE can generate values before an INSERT and be shared across multiple tables.

D. Because SEQUENCES automatically create foreign keys.

Answer: C

Explanation: Unlike an IDENTITY column, a SEQUENCE is independent of tables and can generate values before inserts, making it useful across multiple tables or applications.

Question 5

What is the primary benefit of enabling the CACHE option on a SEQUENCE?

A. It guarantees gap-free numbering.

B. It improves performance by reducing metadata updates.

C. It automatically encrypts sequence values.

D. It prevents transaction rollbacks.

Answer: B

Explanation: Caching sequence values reduces the frequency of metadata updates, improving throughput. However, cached values may be lost during an unexpected shutdown, creating gaps.

Question 6

Which statement about sequence values is correct?

A. Sequence values are returned to the pool if a transaction rolls back.

B. Sequence values are always consecutive with no gaps.

C. Transaction rollbacks do not reclaim sequence values that have already been generated.

D. Sequence values can only be generated during INSERT statements.

Answer: C

Explanation: Once a sequence value is generated, it is consumed. If a transaction later rolls back, that value is not reused, so gaps are expected.

Question 7

A SEQUENCE is created with MAXVALUE 5 and the CYCLE option enabled. What happens after the value 5 is generated?

A. SQL Server raises an error.

B. The sequence automatically restarts at its minimum (or starting) value.

C. The sequence becomes read-only.

D. SQL Server automatically increases the maximum value.

Answer: B

Explanation: The CYCLE option causes a sequence to restart after reaching its maximum value rather than generating an error.

Question 8

Which statement about the NO CYCLE option is correct?

A. It causes sequence values to restart automatically.

B. It caches all generated values.

C. It allows duplicate sequence values.

D. It prevents the sequence from restarting after reaching its limit and raises an error instead.

Answer: D

Explanation: NO CYCLE is the default behavior. Once the sequence reaches its maximum or minimum value, SQL Server raises an error instead of restarting the sequence.

Question 9

Which of the following is a common use case for a SEQUENCE?

A. Automatically maintaining historical versions of rows

B. Enforcing referential integrity

C. Generating invoice numbers shared across multiple applications

D. Validating JSON documents

Answer: C

Explanation: SEQUENCES are frequently used to generate shared numbering schemes, such as invoice numbers, order numbers, or ticket identifiers across multiple systems.

Question 10

A developer uses a cached SEQUENCE to generate order numbers. After an unexpected SQL Server restart, several sequence values are missing. What is the most likely explanation?

A. The PRIMARY KEY constraint removed duplicate values.

B. Transaction rollbacks deleted the missing values.

C. The sequence automatically renumbered existing rows.

D. Cached sequence values that had not yet been issued were lost during the restart.

Answer: D

Explanation: Cached sequence values are stored in memory to improve performance. If SQL Server stops unexpectedly, any unused cached values are lost, resulting in gaps in the generated sequence.

Go to the DP-800 Exam Prep Hub main page