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