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