Tag: Databases

Data Development, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Write queries that include window functions (DP-800 Exam Prep)

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%)
   --> Write advanced T-SQL code
      --> Write queries that include window 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

Window functions are among the most powerful features in Transact-SQL (T-SQL). They enable calculations across a set of rows related to the current row without collapsing the results into a single row, as traditional aggregate functions do. Window functions are widely used in reporting, analytics, business intelligence, financial analysis, and AI-enabled database solutions.

Unlike GROUP BY, which returns one row per group, window functions preserve the individual rows while providing additional calculated values based on a defined “window” of rows.

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

What window functions are
The OVER clause
Partitioning data
Ordering data within windows
Aggregate window functions
Ranking functions
Offset functions
Window frames
Practical business scenarios
Performance considerations
Best practices

Window functions are heavily tested because they allow developers to perform sophisticated calculations efficiently while maintaining readable and maintainable SQL code.

What Is a Window Function?

A window function performs a calculation across a set of rows that are related to the current row.

Unlike aggregate functions used with GROUP BY, a window function does not reduce the number of rows returned.

General syntax:

			
Function(...) OVER
(
    [PARTITION BY column]
    [ORDER BY column]
)

		

The OVER clause defines the window over which the calculation occurs.

The OVER Clause

The OVER clause is required for window functions.

It can contain:

PARTITION BY
ORDER BY
Window frame definitions (ROWS or RANGE)

Example:

			
SELECT
    EmployeeID,
    Salary,
    AVG(Salary) OVER() AS AverageSalary
FROM HumanResources.Employee;

		

The average salary is calculated across all employees while each employee row remains visible.

PARTITION BY

PARTITION BY divides the result set into logical groups.

Example:

			
SELECT
    DepartmentID,
    EmployeeID,
    Salary,
    AVG(Salary)
        OVER(PARTITION BY DepartmentID)
        AS DepartmentAverage
FROM HumanResources.Employee;

		

Each department receives its own average salary.

ORDER BY Within OVER

The ORDER BY clause defines the order of rows within each partition.

Example:

			
SELECT
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

		

The highest salary receives row number 1.

Aggregate Window Functions

Many aggregate functions can operate as window functions.

Common examples include:

SUM()
AVG()
MIN()
MAX()
COUNT()

Example:

			
SELECT
    CustomerID,
    OrderDate,
    TotalAmount,
    SUM(TotalAmount)
        OVER(PARTITION BY CustomerID)
        AS CustomerTotal
FROM Sales.Orders;

		

Each order row displays the customer’s total sales without grouping the results.

Running Totals

A common use of window functions is calculating running totals.

Example:

			
SELECT
    OrderDate,
    TotalAmount,
    SUM(TotalAmount)
        OVER
        (
            ORDER BY OrderDate
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        )
        AS RunningTotal
FROM Sales.Orders;

		

Each row contains the cumulative total through the current row.

Moving Averages

Window functions simplify moving averages.

Example:

			
SELECT
    OrderDate,
    SalesAmount,
    AVG(SalesAmount)
        OVER
        (
            ORDER BY OrderDate
            ROWS BETWEEN 2 PRECEDING
            AND CURRENT ROW
        )
        AS ThreeDayAverage
FROM Sales.DailySales;

		

This example calculates a rolling average over three rows.

Ranking Functions

SQL Server includes several ranking window functions.

These include:

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

ROW_NUMBER()

Assigns a unique sequential number.

Example:

			
SELECT
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER(ORDER BY Salary DESC)
        AS RowNum
FROM HumanResources.Employee;

		

Even rows with equal salaries receive different numbers.

RANK()

Assigns rankings while allowing gaps after ties.

Example:

			
SELECT
    EmployeeID,
    Salary,
    RANK()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

		

If two employees tie for first place, the next rank is 3.

DENSE_RANK()

Assigns rankings without gaps.

Example:

			
SELECT
    EmployeeID,
    Salary,
    DENSE_RANK()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

		

If two employees tie for first place, the next rank is 2.

ROW_NUMBER vs. RANK vs. DENSE_RANK

Function	Duplicate Values	Gaps in Ranking
ROW_NUMBER	No	No
RANK	Yes	Yes
DENSE_RANK	Yes	No

Understanding these differences is a common DP-800 exam objective.

NTILE()

NTILE() divides rows into approximately equal groups.

Example:

			
SELECT
    EmployeeID,
    Salary,
    NTILE(4)
        OVER(ORDER BY Salary DESC)
        AS Quartile
FROM HumanResources.Employee;

		

Employees are divided into four salary quartiles.

Offset Functions

Offset functions compare one row to another.

Common functions include:

LAG()
LEAD()

LAG()

Returns a value from a previous row.

Example:

			
SELECT
    OrderDate,
    SalesAmount,
    LAG(SalesAmount)
        OVER(ORDER BY OrderDate)
        AS PreviousDaySales
FROM Sales.DailySales;

		

LEAD()

Returns a value from a following row.

Example:

			
SELECT
    OrderDate,
    SalesAmount,
    LEAD(SalesAmount)
        OVER(ORDER BY OrderDate)
        AS NextDaySales
FROM Sales.DailySales;

		

FIRST_VALUE()

Returns the first value in the window.

Example:

			
SELECT
    EmployeeID,
    Salary,
    FIRST_VALUE(Salary)
        OVER(ORDER BY Salary DESC)
        AS HighestSalary
FROM HumanResources.Employee;

		

LAST_VALUE()

Returns the last value within the current window frame.

Example:

			
SELECT
    EmployeeID,
    Salary,
    LAST_VALUE(Salary)
        OVER
        (
            ORDER BY Salary
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND UNBOUNDED FOLLOWING
        )
        AS HighestSalary
FROM HumanResources.Employee;

		

Because LAST_VALUE() respects the current window frame, explicitly specifying the frame is often necessary to obtain the expected result.

Window Frames

Window frames define which rows participate in a calculation.

Common options include:

CURRENT ROW
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
n PRECEDING
n FOLLOWING

Example:

			
ROWS BETWEEN 5 PRECEDING
AND CURRENT ROW

This frame includes the current row plus the previous five rows.

ROWS vs. RANGE

ROWS	RANGE
Uses physical row positions	Uses logical value ranges
Predictable row counts	May include multiple tied rows
Often preferred for running totals	Useful for value-based calculations

For most reporting scenarios, ROWS provides more predictable behavior.

Combining PARTITION BY and ORDER BY

Example:

			
SELECT
    DepartmentID,
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER
        (
            PARTITION BY DepartmentID
            ORDER BY Salary DESC
        )
        AS DepartmentRank
FROM HumanResources.Employee;

		

Ranking restarts for each department.

Practical Business Uses

Window functions are commonly used for:

Sales rankings
Running totals
Financial reporting
Trend analysis
Customer segmentation
Inventory analysis
Employee rankings
Time-series analysis
Rolling averages
Year-over-year comparisons

Performance Considerations

Window functions can require sorting operations.

Performance depends on:

Index design
Partition size
Number of rows
ORDER BY columns
Available memory

To improve performance:

Index columns used in PARTITION BY and ORDER BY.
Avoid unnecessary sorting.
Limit returned rows when appropriate.
Review execution plans.
Consider filtered datasets before applying window functions.

AI-Enabled Database Scenarios

Window functions are valuable for preparing data used by AI applications.

Examples include:

Ranking search results before intelligent retrieval
Identifying the latest customer interactions for Retrieval-Augmented Generation (RAG)
Calculating rolling metrics for machine learning features
Detecting trends in IoT sensor data
Selecting the top-N records for embedding generation
Comparing current values with previous observations using LAG() and LEAD()
Preparing time-series datasets for AI forecasting models

These capabilities help organize and enrich data before it is consumed by AI pipelines.

Best Practices

Always specify an appropriate ORDER BY clause when required.
Use PARTITION BY only when logical grouping is needed.
Understand the differences among ranking functions.
Specify window frames explicitly for running totals and functions such as LAST_VALUE().
Create indexes on frequently partitioned or sorted columns.
Test performance with production-sized datasets.
Avoid unnecessary nested window calculations.
Review execution plans for expensive sorts.

Common Exam Tips

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

Window functions require the OVER clause.
PARTITION BY divides rows into logical groups.
ORDER BY defines the order within each window.
Window functions preserve individual rows.
ROW_NUMBER() always returns unique sequential numbers.
RANK() allows gaps after ties.
DENSE_RANK() does not leave gaps after ties.
LAG() retrieves values from previous rows.
LEAD() retrieves values from subsequent rows.
Running totals commonly use SUM() with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Practice Exam Questions

Question 1

Which clause is required for every SQL Server window function?

A. GROUP BY

B. HAVING

C. OVER

D. PARTITION

Answer: C

Explanation: Every window function must include the OVER clause, which defines the window over which the calculation is performed.

Question 2

What is the primary advantage of a window function over a traditional aggregate function?

A. It always executes faster.

B. It preserves individual rows while performing calculations across related rows.

C. It automatically creates indexes.

D. It eliminates the need for sorting.

Answer: B

Explanation: Unlike aggregate functions with GROUP BY, window functions return calculations while preserving each row in the result set.

Question 3

Which window function assigns a unique sequential number to every row, even when duplicate values exist?

A. RANK()

B. DENSE_RANK()

C. NTILE()

D. ROW_NUMBER()

Answer: D

Explanation: ROW_NUMBER() always assigns unique sequential numbers, regardless of duplicate values.

Question 4

Which ranking function assigns the same rank to tied rows without leaving gaps in subsequent rankings?

A. ROW_NUMBER()

B. NTILE()

C. DENSE_RANK()

D. RANK()

Answer: C

Explanation: DENSE_RANK() assigns the same rank to tied rows and continues with the next consecutive rank without gaps.

Question 5

What is the purpose of the PARTITION BY clause?

A. To permanently divide a table into partitions.

B. To group rows into logical partitions for window function calculations.

C. To sort the final result set.

D. To filter rows before processing.

Answer: B

Explanation: PARTITION BY creates logical groups within the result set so calculations are performed independently for each partition.

Question 6

Which function returns the value from the previous row within the defined window?

A. LEAD()

B. FIRST_VALUE()

C. LAST_VALUE()

D. LAG()

Answer: D

Explanation: LAG() retrieves a value from a preceding row within the same window.

Question 7

A developer needs to calculate a running total ordered by transaction date. Which feature is most appropriate?

A. SUM() with OVER and a window frame

B. GROUP BY

C. DISTINCT

D. UNION

Answer: A

Explanation: Running totals are typically calculated using SUM() with the OVER clause and a window frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Question 8

What is the default behavior of RANK() when multiple rows have the same value?

A. Each tied row receives a different rank.

B. Tied rows receive the same rank, and the next rank contains a gap.

C. Tied rows are ignored.

D. Tied rows receive sequential ranks without gaps.

Answer: B

Explanation: RANK() assigns identical ranks to tied rows and skips the next ranking number accordingly.

Question 9

Which statement about window frames is correct?

A. They are used only with ranking functions.

B. They define which rows participate in a window calculation.

C. They permanently partition a table.

D. They replace the ORDER BY clause.

Answer: B

Explanation: Window frames specify the subset of rows within the window that contribute to the calculation, making them especially useful for running totals and moving averages.

Question 10

How are window functions commonly used in AI-enabled database solutions?

A. They directly generate embeddings from text.

B. They replace vector indexes.

C. They prepare and enrich data by calculating rankings, rolling metrics, and historical comparisons before it is consumed by AI models, intelligent search, or Retrieval-Augmented Generation (RAG) pipelines.

D. They eliminate the need for data preprocessing.

Answer: C

Explanation: Window functions help organize and enrich datasets by calculating analytical metrics, rankings, and trends that serve as valuable inputs to AI workflows and machine learning processes.

Go to the DP-800 Exam Prep Hub main page

Data Development, Databases, Microsoft Certification, SQL August 1, 2026

Write Common Table Expressions (CTEs) (DP-800 Exam Prep)

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%)
   --> Write advanced T-SQL code
      --> Write common table expressions (CTEs)

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

A Common Table Expression (CTE) is a temporary, named result set that exists only for the duration of a single SQL statement. CTEs simplify complex queries by breaking them into logical, readable components. They can be referenced in SELECT, INSERT, UPDATE, DELETE, and MERGE statements and are particularly useful for hierarchical queries, recursive operations, and improving query readability.

Unlike temporary tables or table variables, CTEs are not physically stored in the database. They are defined using the WITH keyword and exist only during the execution of the immediately following statement.

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

What CTEs are
How to create and use CTEs
Nonrecursive CTEs
Recursive CTEs
Multiple CTEs
Using CTEs with DML statements
Recursive query patterns
Performance considerations
CTE limitations
Best practices
AI-enabled database scenarios

Understanding CTEs is important because they are commonly used in enterprise SQL development to organize complex logic, traverse hierarchical data, and prepare datasets for reporting and AI workloads.

What Is a Common Table Expression?

A Common Table Expression is a temporary named query defined immediately before another SQL statement.

General syntax:

			
WITH CTE_Name AS
(
    SELECT ...
)
SELECT *
FROM CTE_Name;

		

The CTE is available only to the statement immediately following its definition.

Benefits of CTEs

CTEs offer several advantages:

Improve readability
Simplify complex queries
Replace deeply nested subqueries
Enable recursive queries
Make SQL easier to debug
Encourage modular query design
Improve maintainability
Support DML operations

Creating a Simple CTE

Example:

			
WITH HighValueOrders AS
(
    SELECT
        OrderID,
        CustomerID,
        TotalAmount
    FROM Sales.Orders
    WHERE TotalAmount > 5000
)
SELECT *
FROM HighValueOrders;

		

The CTE filters orders before the final query executes.

Referencing a CTE

A CTE behaves similarly to a temporary result set.

Example:

			
WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT *
FROM CustomerTotals
WHERE TotalSales > 10000;

		

CTEs and Query Readability

Without a CTE:

			
SELECT *
FROM
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS SalesTotals;

		

Using a CTE often makes the query easier to understand, particularly when multiple steps are involved.

Multiple CTEs

Multiple CTEs can be defined within a single WITH clause.

Example:

			
WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
),
TopCustomers AS
(
    SELECT *
    FROM CustomerTotals
    WHERE TotalSales > 10000
)
SELECT *
FROM TopCustomers;

		

Each CTE can reference earlier CTEs defined in the same WITH clause.

Recursive CTEs

A recursive CTE repeatedly references itself until a termination condition is met.

It consists of:

An anchor member
A recursive member

Example:

			
WITH Numbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT *
FROM Numbers;

		

Result:

Recursive CTE Structure

A recursive CTE has two parts:

Anchor member

Returns the initial result.

SELECT 1 AS Number

Recursive member

References the CTE itself.

			
SELECT Number + 1
FROM Numbers
WHERE Number < 10

The recursion ends when no additional rows are returned.

Hierarchical Queries

Recursive CTEs are ideal for hierarchical data such as:

Organizational charts
Employee-manager relationships
Bill of materials
Folder structures
Product categories

Example:

			
WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName
    FROM HumanResources.Employee e
        INNER JOIN EmployeeHierarchy h
            ON e.ManagerID = h.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

		

Using MAXRECURSION

SQL Server limits recursion to 100 levels by default.

To override the limit:

OPTION (MAXRECURSION 500);

Unlimited recursion:

OPTION (MAXRECURSION 0);

Using unlimited recursion should be done cautiously to avoid infinite loops.

CTEs with INSERT

Example:

			
WITH LargeOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE TotalAmount > 10000
)
INSERT INTO Sales.ArchiveOrders
SELECT *
FROM LargeOrders;

		

CTEs with UPDATE

Example:

			
WITH CustomerDiscounts AS
(
    SELECT
        CustomerID,
        Discount
    FROM Sales.Customers
    WHERE Discount < 0.05
)
UPDATE CustomerDiscounts
SET Discount = 0.05;

		

The CTE provides an updateable result set because it references a single base table without disqualifying constructs.

CTEs with DELETE

Example:

			
WITH OldOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE OrderDate < '2023-01-01'
)
DELETE
FROM OldOrders;

		

CTEs with MERGE

CTEs can simplify complex merge operations.

Example:

			
WITH UpdatedCustomers AS
(
    SELECT *
    FROM Sales.CustomerImport
)
MERGE Sales.Customers AS Target
USING UpdatedCustomers AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE
    SET CustomerName = Source.CustomerName
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName)
    VALUES (Source.CustomerID, Source.CustomerName);

		

CTEs vs. Subqueries

Feature	CTE	Subquery
Readability	High	Moderate
Supports recursion	Yes	No
Reusable within statement	Yes	Limited
Multiple logical steps	Excellent	Difficult
Hierarchical queries	Yes	No

CTEs vs. Temporary Tables

Feature	CTE	Temporary Table
Stored physically	No	Yes (tempdb)
Exists beyond one statement	No	Yes
Supports indexes	No	Yes
Good for complex multi-step processing	Sometimes	Yes
Good for readability	Excellent	Moderate

CTEs vs. Table Variables

Feature	CTE	Table Variable
Temporary object	Logical only	Physical object in tempdb
Exists after statement	No	Yes
Supports indexes	No (directly)	Limited (via constraints/indexes in newer versions)
Recursive queries	Yes	No

Performance Considerations

Although CTEs improve readability, they do not automatically improve performance.

Consider the following:

CTEs are expanded into the execution plan by the optimizer rather than materialized by default.
Large CTEs referenced multiple times may be re-evaluated.
Recursive CTEs can become expensive for deep hierarchies.
Temporary tables may outperform CTEs for large intermediate result sets reused across multiple statements.
Proper indexing on underlying tables remains critical.

Always review the execution plan when optimizing complex queries.

CTE Limitations

Developers should understand these limitations:

Scope is limited to one statement.
Cannot be referenced by subsequent statements.
Cannot include an ORDER BY clause unless used with TOP, OFFSET/FETCH, or FOR XML.
Cannot create indexes on a CTE.
Recursive CTEs require a termination condition.
Excessive recursion can impact performance or lead to errors if recursion limits are exceeded.

AI-Enabled Database Scenarios

CTEs are frequently used in AI-enabled database solutions to prepare data before AI processing.

Examples include:

Cleaning and filtering text before embedding generation
Building hierarchical product catalogs for Retrieval-Augmented Generation (RAG)
Preparing conversation histories for prompt construction
Identifying duplicate records before vectorization
Aggregating customer interactions for AI analysis
Transforming datasets before intelligent search indexing
Organizing graph-like relationships that feed AI models

CTEs provide a readable way to express complex transformations commonly required before AI workflows.

Best Practices

Give CTEs meaningful names.
Use CTEs to simplify complex queries.
Prefer CTEs over deeply nested subqueries.
Use recursive CTEs only when recursion is required.
Always include a termination condition in recursive CTEs.
Test recursive queries with realistic datasets.
Review execution plans for large queries.
Consider temporary tables for large reusable intermediate results.
Keep CTE definitions focused on a single logical task.
Avoid excessive nesting of multiple CTEs.

Common Exam Tips

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

CTEs begin with the WITH keyword.
A CTE exists only for the immediately following statement.
Recursive CTEs consist of an anchor member and a recursive member.
Recursive CTEs are commonly used for hierarchical data.
SQL Server limits recursion to 100 levels by default.
OPTION (MAXRECURSION n) changes the recursion limit.
CTEs can be used with SELECT, INSERT, UPDATE, DELETE, and MERGE.
CTEs are not stored as database objects.
CTEs improve readability but do not guarantee better performance.
Recursive CTEs must include a termination condition.

Practice Exam Questions

Question 1

Which keyword is used to define a Common Table Expression?

A. WITH

B. TEMP

C. DEFINE

D. AS

Answer: A

Explanation: Every Common Table Expression begins with the WITH keyword followed by the CTE name and query definition.

Question 2

How long does a Common Table Expression exist?

A. Until the database connection closes

B. Until the current transaction completes

C. Only for the execution of the immediately following SQL statement

D. Until it is explicitly dropped

Answer: C

Explanation: A CTE exists only for the single statement immediately following its definition.

Question 3

Which capability distinguishes a recursive CTE from a nonrecursive CTE?

A. It can reference itself.

B. It creates a permanent table.

C. It automatically creates indexes.

D. It stores intermediate results in tempdb.

Answer: A

Explanation: Recursive CTEs reference themselves to repeatedly process data until a termination condition is reached.

Question 4

Which type of data is most appropriate for a recursive CTE?

A. Monthly sales totals

B. Customer invoices

C. Product pricing

D. Organizational hierarchies

Answer: D

Explanation: Recursive CTEs are commonly used for hierarchical data such as organizational charts, bill of materials, and category trees.

Question 5

Which statement about recursive CTEs is correct?

A. They do not require an anchor member.

B. They require both an anchor member and a recursive member.

C. They can only return numeric data.

D. They cannot use UNION ALL.

Answer: B

Explanation: Every recursive CTE contains an anchor member that produces the initial rows and a recursive member that references the CTE itself.

Question 6

What is the default maximum recursion level in SQL Server?

A. 10

B. 50

C. 100

D. Unlimited

Answer: C

Explanation: SQL Server limits recursive CTE execution to 100 levels by default unless the MAXRECURSION query hint is specified.

Question 7

Which statement correctly describes CTE performance?

A. CTEs always execute faster than temporary tables.

B. CTEs are always materialized into temporary storage.

C. CTEs automatically create indexes.

D. CTEs primarily improve query readability, while performance depends on the execution plan and underlying data.

Answer: D

Explanation: CTEs improve readability and maintainability, but the query optimizer determines how they are executed. They do not inherently improve performance.

Question 8

Which DML operation can use a Common Table Expression?

A. SELECT only

B. SELECT and INSERT only

C. SELECT, INSERT, UPDATE, DELETE, and MERGE

D. UPDATE only

Answer: C

Explanation: CTEs can precede and be referenced by SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Question 9

When should a temporary table typically be preferred over a CTE?

A. When a readable single-statement query is needed

B. When recursion is required

C. When the intermediate result set must be reused across multiple statements or indexed

D. When querying hierarchical data

Answer: C

Explanation: Temporary tables persist beyond a single statement, can be indexed, and are often more efficient when intermediate results are reused multiple times.

Question 10

How are CTEs commonly used in AI-enabled database solutions?

A. They directly generate vector embeddings.

B. They replace vector indexes.

C. They eliminate the need for application logic.

D. They simplify complex data preparation tasks such as filtering, aggregating, and organizing data before embedding generation, intelligent search, or Retrieval-Augmented Generation (RAG) workflows.

Answer: D

Explanation: CTEs are commonly used to prepare and transform datasets before downstream AI processing, improving readability and maintainability of complex SQL used in AI-enabled database solutions.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Create triggers (DP-800 Exam Prep)

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 triggers

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

Triggers are special types of stored procedures that automatically execute (or “fire”) in response to specific database events. Unlike stored procedures, which must be executed explicitly by a user or application, triggers are invoked automatically by SQL Server when certain Data Manipulation Language (DML), Data Definition Language (DDL), or logon events occur.

Triggers are commonly used to enforce complex business rules, maintain audit trails, synchronize related data, validate changes, and perform automated actions that occur whenever data or database objects are modified. While triggers are powerful, they should be used judiciously because they can add complexity and affect database performance if not carefully designed.

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

What triggers are
DML triggers
DDL triggers
AFTER and INSTEAD OF triggers
The inserted and deleted logical tables
Creating, altering, disabling, enabling, and dropping triggers
Nested and recursive triggers
Performance considerations
Best practices
AI-enabled database scenarios

Understanding triggers is important because they provide automatic execution of business logic while helping maintain data integrity and automate administrative tasks.

What Is a Trigger?

A trigger is a database object that automatically executes when a specified event occurs.

Triggers are associated with:

Tables
Views
Databases
SQL Server instances (for certain DDL and logon events)

Triggers cannot be executed directly using the EXEC statement.

Instead, SQL Server executes them automatically when the triggering event occurs.

Types of Triggers

SQL Server supports several types of triggers:

DML triggers
DDL triggers
Logon triggers

The DP-800 exam primarily focuses on DML and DDL triggers.

DML Triggers

Data Manipulation Language (DML) triggers fire when data is modified.

They respond to:

INSERT
UPDATE
DELETE

Typical uses include:

Auditing data changes
Enforcing business rules
Validating updates
Synchronizing tables
Recording historical information

AFTER Triggers

An AFTER trigger executes only after the triggering statement completes successfully.

Example:

			
CREATE TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
    INSERT INTO Sales.CustomerAudit
    (
        CustomerID,
        AuditDate
    )
    SELECT
        CustomerID,
        GETDATE()
    FROM inserted;
END;

		

The trigger records newly inserted customers after the insert operation succeeds.

INSTEAD OF Triggers

An INSTEAD OF trigger executes in place of the triggering action.

Example:

			
CREATE TRIGGER trgPreventDelete
ON Sales.Customers
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Deleting customers is not permitted.';
END;

		

The DELETE statement never executes because the trigger replaces it.

INSTEAD OF triggers are commonly used on:

Views
Complex update scenarios
Custom validation logic

DDL Triggers

DDL triggers respond to schema changes.

Common events include:

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE PROCEDURE
ALTER PROCEDURE
DROP PROCEDURE

Example:

			
CREATE TRIGGER trgAuditDDL
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    PRINT 'A table was created.';
END;

		

DDL triggers help monitor or prevent unauthorized schema modifications.

Logon Triggers

Logon triggers execute when a user establishes a SQL Server session.

Typical uses include:

Restricting connections
Recording login activity
Enforcing security policies

Logon triggers are created at the server level and are not supported in Azure SQL Database.

The inserted Logical Table

Whenever rows are inserted or updated, SQL Server creates a temporary logical table named inserted.

It contains the new version of affected rows.

Example:

			
SELECT *
FROM inserted;

The inserted table exists only during trigger execution.

The deleted Logical Table

Whenever rows are deleted or updated, SQL Server creates a logical table named deleted.

It contains the original version of affected rows.

Example:

			
SELECT *
FROM deleted;

For UPDATE operations:

deleted contains old values.
inserted contains new values.

Auditing Changes

Triggers are frequently used to create audit trails.

Example:

			
CREATE TRIGGER trgAuditSalary
ON HumanResources.Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO HumanResources.SalaryAudit
    (
        EmployeeID,
        OldSalary,
        NewSalary,
        ChangeDate
    )
    SELECT
        d.EmployeeID,
        d.Salary,
        i.Salary,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i
        ON d.EmployeeID = i.EmployeeID;
END;

		

This trigger records salary changes for auditing purposes.

Enforcing Business Rules

Triggers can prevent invalid operations.

Example:

			
CREATE TRIGGER trgNoNegativeInventory
ON Inventory.Products
AFTER UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM inserted
        WHERE Quantity < 0
    )
    BEGIN
        RAISERROR
        (
            'Inventory cannot be negative.',
            16,
            1
        );
        ROLLBACK TRANSACTION;
    END;
END;

		

The trigger rolls back the transaction if inventory becomes negative.

Multi-Row Operations

Triggers execute once per SQL statement, not once per affected row.

For example:

			
UPDATE Sales.Customers
SET City = 'Miami';

If 10,000 rows are updated, the trigger executes only once.

The inserted and deleted tables contain all affected rows.

Developers should always write triggers using set-based logic, not assumptions that only one row is affected.

Nested Triggers

A trigger can cause another trigger to fire.

Example:

Trigger A updates Table B.
Table B has Trigger B.
Trigger B executes automatically.

This behavior is called nested triggers.

SQL Server supports nested triggers up to a configurable limit.

Recursive Triggers

A recursive trigger fires itself either directly or indirectly.

Example:

Trigger updates its own table.
That update causes the same trigger to execute again.

Recursive triggers are disabled by default in many environments and should be used with caution to avoid infinite loops.

Enabling and Disabling Triggers

Disable a trigger:

			
DISABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Enable it:

			
ENABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Disabling a trigger preserves its definition while preventing it from firing.

Modifying a Trigger

Use ALTER TRIGGER.

Example:

			
ALTER TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
    PRINT 'Customer inserted.';
END;

		

Deleting a Trigger

Use:

DROP TRIGGER trgCustomerAudit;

Viewing Trigger Definitions

Developers can inspect a trigger using:

sp_helptext 'trgCustomerAudit';

Or:

			
SELECT OBJECT_DEFINITION
(
    OBJECT_ID('trgCustomerAudit')
);

Triggers vs. Stored Procedures

Feature	Trigger	Stored Procedure
Executes automatically	Yes	No
Invoked by `EXEC`	No	Yes
Responds to database events	Yes	No
Accepts parameters	No	Yes
Returns result sets	Not intended for callers	Yes

Triggers vs. Constraints

Feature	Trigger	Constraint
Enforces simple rules	Possible	Yes
Enforces complex business logic	Yes	Limited
Can reference multiple tables	Yes	Limited
Executes automatically	Yes	Yes

Constraints should generally be preferred for simple validation rules because they are simpler and often more efficient.

Performance Considerations

Triggers execute within the same transaction as the triggering statement.

Poorly designed triggers can:

Increase transaction duration
Increase locking
Reduce concurrency
Consume additional CPU resources
Introduce blocking
Increase deadlock risk

Best practices include:

Keep trigger logic simple.
Use set-based operations.
Avoid unnecessary queries.
Avoid long-running operations.
Minimize external dependencies.
Do not assume only one row is affected.

Security Considerations

Triggers can:

Audit sensitive changes
Prevent unauthorized updates
Enforce compliance policies
Record administrative activity
Restrict schema modifications using DDL triggers

Proper permissions should be applied because trigger code executes in the database context.

AI-Enabled Database Scenarios

Triggers can support AI-enabled database solutions by automating actions whenever data changes.

Examples include:

Recording changes that require new embeddings to be generated
Logging modifications to AI training datasets
Flagging rows for downstream vectorization processes
Updating AI metadata tables after inserts or updates
Capturing prompt history for auditing
Initiating workflows that prepare data for intelligent search or Retrieval-Augmented Generation (RAG)

Although triggers cannot directly invoke external AI services, they can populate work queues or status tables that downstream applications or services process.

Best Practices

Prefer constraints for simple validation.
Use triggers only when automatic behavior is required.
Write triggers using set-based logic.
Minimize execution time.
Avoid recursive logic unless absolutely necessary.
Test triggers with multi-row operations.
Document business rules implemented by triggers.
Avoid unnecessary nested trigger chains.
Monitor trigger performance.
Audit only the information that is required.

Common Exam Tips

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

Triggers execute automatically in response to events.
DML triggers respond to INSERT, UPDATE, and DELETE statements.
DDL triggers respond to schema changes.
AFTER triggers execute after the triggering statement completes successfully.
INSTEAD OF triggers replace the triggering action.
inserted contains new row values.
deleted contains original row values.
Triggers fire once per statement, not once per row.
Use ALTER TRIGGER to modify a trigger.
Use DISABLE TRIGGER, ENABLE TRIGGER, and DROP TRIGGER to manage trigger lifecycle.

Practice Exam Questions

Question 1

A developer wants database logic to execute automatically whenever rows are inserted into a table. Which database object should be used?

A. Stored procedure

B. Trigger

C. View

D. Scalar function

Answer: B

Explanation: Triggers automatically execute in response to specified database events such as INSERT, UPDATE, or DELETE operations.

Question 2

Which type of trigger executes only after the triggering statement has completed successfully?

A. BEFORE trigger

B. INSTEAD OF trigger

C. AFTER trigger

D. LOGON trigger

Answer: C

Explanation: An AFTER trigger fires only after the triggering DML statement has completed successfully and any associated constraints have been processed.

Question 3

During an UPDATE operation, which logical table contains the original values of the modified rows?

A. inserted

B. updated

C. original

D. deleted

Answer: D

Explanation: During an UPDATE, the deleted logical table contains the original row values, while the inserted table contains the new values.

Question 4

Which trigger type replaces the original INSERT, UPDATE, or DELETE operation?

A. AFTER trigger

B. DDL trigger

C. INSTEAD OF trigger

D. Recursive trigger

Answer: C

Explanation: An INSTEAD OF trigger executes instead of the triggering statement, allowing custom processing or validation.

Question 5

A trigger is written assuming that only one row is updated at a time. Why is this a problem?

A. SQL Server executes one trigger for every row.

B. Triggers always execute asynchronously.

C. Triggers execute once per SQL statement and may process many affected rows.

D. UPDATE statements cannot affect multiple rows.

Answer: C

Explanation: SQL Server fires DML triggers once per statement, so developers must use set-based logic to correctly process all affected rows.

Question 6

Which statement disables a trigger while preserving its definition?

A. REMOVE TRIGGER

B. DROP TRIGGER

C. ALTER TRIGGER

D. DISABLE TRIGGER

Answer: D

Explanation: DISABLE TRIGGER prevents a trigger from firing without deleting it, allowing it to be re-enabled later.

Question 7

Which statement best describes a DDL trigger?

A. It responds to changes in table data.

B. It responds to schema modification events such as CREATE, ALTER, or DROP statements.

C. It executes only during user logins.

D. It replaces the execution of stored procedures.

Answer: B

Explanation: DDL triggers respond to schema-related events, making them useful for auditing or preventing structural database changes.

Question 8

Which object is generally preferred for enforcing a simple rule such as ensuring a value is greater than zero?

A. AFTER trigger

B. CHECK constraint

C. DDL trigger

D. Stored procedure

Answer: B

Explanation: CHECK constraints are simpler, easier to maintain, and generally more efficient than triggers for straightforward validation rules.

Question 9

Which statement correctly describes nested triggers?

A. They occur only with DDL triggers.

B. They allow a trigger to execute dynamic SQL.

C. They occur when one trigger causes another trigger to fire.

D. They are required whenever inserted and deleted tables are referenced.

Answer: C

Explanation: Nested triggers occur when the actions performed by one trigger cause another trigger to execute.

Question 10

How can triggers support AI-enabled database solutions?

A. They automatically generate embeddings by calling AI models directly.

B. They replace vector indexes.

C. They eliminate the need for application code.

D. They automatically detect data changes and populate work queues, audit tables, or status records that downstream AI processes use to generate embeddings, update indexes, or prepare RAG data.

Answer: D

Explanation: Triggers are well suited for detecting data changes and initiating downstream workflows by recording changes or updating processing queues. External applications or services can then consume these queues to perform AI-related tasks such as embedding generation or intelligent indexing.

Go to the DP-800 Exam Prep Hub main page

Data Development, Databases, DP-800, Microsoft Certification August 1, 2026

Create stored procedures (DP-800 Exam Prep)

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 stored procedures

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

Stored procedures are one of the most powerful programmability objects available in SQL Server and Azure SQL Database. A stored procedure is a precompiled collection of one or more Transact-SQL (T-SQL) statements that perform a specific task. Stored procedures allow developers to encapsulate business logic, automate repetitive database operations, improve application security, and optimize performance.

Stored procedures are widely used in enterprise applications for Create, Read, Update, Delete (CRUD) operations, reporting, data validation, data processing, ETL workflows, auditing, and integrating applications with databases. They are also valuable in AI-enabled database solutions where they can orchestrate data preparation, invoke AI-related operations, and manage workflows that interact with vector data, embeddings, and Retrieval-Augmented Generation (RAG) pipelines.

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

What stored procedures are
How to create, alter, execute, and delete stored procedures
Input and output parameters
Returning result sets and status codes
Error handling
Transactions
Dynamic SQL
Temporary objects
Security considerations
Performance optimization
Best practices

Understanding stored procedures is essential because they are one of the primary methods for implementing reusable business logic in SQL Server.

What Is a Stored Procedure?

A stored procedure is a named database object that contains one or more SQL statements that execute as a unit.

Unlike functions, stored procedures:

Can return zero, one, or multiple result sets
Can modify database data
Can execute DDL and DML statements
Can contain transactions
Can execute dynamic SQL
Can include error handling
Can call other stored procedures

Stored procedures are stored within the database and executed on demand.

Benefits of Stored Procedures

Stored procedures provide numerous advantages:

Encapsulate business logic
Reduce duplicate SQL code
Improve security
Simplify application development
Improve maintainability
Reduce network traffic
Support transaction management
Improve performance through execution plan reuse
Simplify administrative operations

Creating a Stored Procedure

Basic syntax:

			
CREATE PROCEDURE dbo.uspGetCustomers
AS
BEGIN
    SELECT
        CustomerID,
        CustomerName,
        City
    FROM Sales.Customers;
END;

		

Execute the procedure:

EXEC dbo.uspGetCustomers;

EXECUTE dbo.uspGetCustomers;

Creating a Stored Procedure with Parameters

Most stored procedures accept one or more parameters.

Example:

			
CREATE PROCEDURE dbo.uspGetCustomerOrders
(
    @CustomerID INT
)
AS
BEGIN
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END;

		

Execute:

			
EXEC dbo.uspGetCustomerOrders
    @CustomerID = 100;

Parameters make procedures reusable across many scenarios.

Using Multiple Parameters

Example:

			
CREATE PROCEDURE dbo.uspOrdersByDate
(
    @StartDate DATE,
    @EndDate DATE
)
AS
BEGIN
    SELECT *
    FROM Sales.Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

		

Optional Parameters

Parameters may have default values.

Example:

			
CREATE PROCEDURE dbo.uspGetOrders
(
    @Status VARCHAR(20) = 'Open'
)
AS
BEGIN
    SELECT *
    FROM Sales.Orders
    WHERE Status = @Status;
END;

		

Now the procedure can be executed with or without specifying the parameter.

Output Parameters

Stored procedures can return values through output parameters.

Example:

			
CREATE PROCEDURE dbo.uspGetOrderCount
(
    @CustomerID INT,
    @OrderCount INT OUTPUT
)
AS
BEGIN
    SELECT
        @OrderCount = COUNT(*)
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END;

		

Execution:

			
DECLARE @Count INT;
EXEC dbo.uspGetOrderCount
    @CustomerID = 100,
    @OrderCount = @Count OUTPUT;
SELECT @Count;

		

Returning Status Codes

Stored procedures may return an integer status code.

Example:

			
CREATE PROCEDURE dbo.uspExample
AS
BEGIN
    RETURN 0;
END;

		

Execution:

			
DECLARE @ReturnCode INT;
EXEC @ReturnCode = dbo.uspExample;
SELECT @ReturnCode;

A return code is commonly used to indicate success or failure.

Returning Result Sets

Stored procedures frequently return result sets.

Example:

			
CREATE PROCEDURE dbo.uspTopCustomers
AS
BEGIN
    SELECT TOP (10)
        CustomerName,
        TotalSales
    FROM Sales.CustomerTotals
    ORDER BY TotalSales DESC;
END;

		

Applications can consume the returned rows directly.

Data Modification

Stored procedures commonly perform INSERT, UPDATE, DELETE, and MERGE operations.

Example:

			
CREATE PROCEDURE dbo.uspInsertCustomer
(
    @CustomerName NVARCHAR(100),
    @City NVARCHAR(50)
)
AS
BEGIN
    INSERT INTO Sales.Customers
    (
        CustomerName,
        City
    )
    VALUES
    (
        @CustomerName,
        @City
    );
END;

		

Using Transactions

Stored procedures frequently include explicit transactions.

Example:

			
CREATE PROCEDURE dbo.uspTransferFunds
AS
BEGIN
    BEGIN TRANSACTION;
    -- Debit account
    -- Credit account
    COMMIT TRANSACTION;
END;

		

Transactions ensure that all related operations either succeed together or fail together.

Error Handling

SQL Server supports structured error handling with TRY...CATCH.

Example:

			
CREATE PROCEDURE dbo.uspExample
AS
BEGIN
    BEGIN TRY
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE();
    END CATCH
END;

		

Functions cannot use TRY...CATCH, making this an important distinction between stored procedures and functions.

Dynamic SQL

Stored procedures can execute dynamic SQL.

Example:

			
CREATE PROCEDURE dbo.uspDynamicSearch
(
    @TableName SYSNAME
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL =
        N'SELECT * FROM ' + QUOTENAME(@TableName);
    EXEC sp_executesql @SQL;
END;

		

Using sp_executesql is generally preferred over EXEC() because it supports parameterization and helps reduce SQL injection risks.

Temporary Tables

Stored procedures often use temporary tables.

Example:

			
CREATE PROCEDURE dbo.uspSalesReport
AS
BEGIN
    CREATE TABLE #Sales
    (
        OrderID INT,
        Total MONEY
    );
    INSERT INTO #Sales
    SELECT
        OrderID,
        TotalAmount
    FROM Sales.Orders;
    SELECT *
    FROM #Sales;
END;

		

Temporary tables exist only during the session or procedure execution.

Table Variables

Stored procedures may also use table variables.

Example:

			
DECLARE @Orders TABLE
(
    OrderID INT,
    Total MONEY
);

		

Table variables are useful for storing smaller intermediate result sets.

Modifying a Stored Procedure

Use ALTER PROCEDURE.

Example:

			
ALTER PROCEDURE dbo.uspGetCustomers
AS
BEGIN
    SELECT
        CustomerID,
        CustomerName,
        City,
        Country
    FROM Sales.Customers;
END;

		

Deleting a Stored Procedure

Use:

DROP PROCEDURE dbo.uspGetCustomers;

Viewing a Stored Procedure Definition

Developers can inspect a procedure using:

sp_helptext 'dbo.uspGetCustomers';

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

Stored Procedures vs. Functions

Feature	Stored Procedure	Function
Returns a table	May return result sets	Table-valued functions only
Returns a scalar value	Via OUTPUT or RETURN	Scalar functions return one value
Used in SELECT	No	Yes (functions)
Can modify data	Yes	Limited; functions cannot modify permanent user tables
Supports transactions	Yes	No
Supports TRY…CATCH	Yes	No
Supports dynamic SQL	Yes	No

Stored Procedures vs. Views

Feature	Stored Procedure	View
Accepts parameters	Yes	No
Modifies data	Yes	No (except through updatable views under certain conditions)
Contains procedural logic	Yes	No
Executes transactions	Yes	No

Security Benefits

Stored procedures improve security by:

Granting EXECUTE permission instead of direct table access
Encapsulating sensitive business rules
Reducing the application’s need for elevated privileges
Supporting ownership chaining in many scenarios
Helping minimize SQL injection risks through parameterized queries

Performance Considerations

Stored procedures often improve performance because:

Execution plans can be reused.
SQL is compiled and optimized by the query optimizer.
Network traffic is reduced because only the procedure call is transmitted.
Business logic executes close to the data.

However, developers should also understand parameter sniffing, where SQL Server creates an execution plan based on the parameter values supplied during compilation. In some cases, this plan may not be optimal for different parameter values. Techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, or carefully designed query patterns may help address parameter-sensitive performance issues.

AI-Enabled Database Scenarios

Stored procedures play an important role in AI-enabled database solutions.

Examples include:

Preparing data before generating embeddings
Coordinating vector insert and update operations
Executing intelligent search workflows
Managing Retrieval-Augmented Generation (RAG) data preparation
Orchestrating multi-step AI pipelines
Logging AI requests and responses
Performing batch updates for AI-generated content
Calling Azure services from application workflows that interact with the database

Stored procedures provide a reliable way to centralize business logic that supports AI applications.

Best Practices

Use descriptive names such as uspGetCustomerOrders.
Keep procedures focused on a single responsibility.
Use parameterized queries whenever possible.
Prefer sp_executesql over concatenated dynamic SQL.
Include proper error handling with TRY...CATCH.
Use transactions only when necessary and keep them as short as possible.
Return only the data that callers require.
Document business logic clearly.
Test procedures with realistic workloads.
Monitor execution plans and optimize when needed.

Common Exam Tips

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

Stored procedures encapsulate reusable business logic.
Procedures can accept input parameters and output parameters.
They can return one or more result sets.
They support transactions, dynamic SQL, and error handling.
CREATE PROCEDURE creates a procedure.
ALTER PROCEDURE modifies an existing procedure.
DROP PROCEDURE removes a procedure.
Stored procedures can modify database data.
Procedures cannot be referenced directly in the FROM clause like table-valued functions.
Stored procedures are commonly used for CRUD operations, reporting, ETL, and AI workflow orchestration.

Practice Exam Questions

Question 1

A developer needs a reusable database object that can contain multiple SQL statements, modify data, and execute transactions. Which object should be created?

A. View

B. Scalar function

C. Stored procedure

D. Table-valued function

Answer: C

Explanation: Stored procedures are designed to encapsulate reusable business logic, modify data, execute transactions, and perform procedural operations.

Question 2

Which statement is used to execute an existing stored procedure?

A. EXEC

B. RUN

C. CALLPROC

D. EXECUTEQUERY

Answer: A

Explanation: SQL Server executes stored procedures using EXEC or its equivalent keyword EXECUTE.

Question 3

A stored procedure needs to return a calculated value to the calling application without including it in a result set. Which feature should be used?

A. CHECK constraint

B. OUTPUT parameter

C. DEFAULT constraint

D. Computed column

Answer: B

Explanation: OUTPUT parameters allow a stored procedure to return one or more values directly to the caller in addition to any result sets.

Question 4

Which capability is supported by stored procedures but not by user-defined functions?

A. Accepting parameters

B. Returning values

C. Executing TRY...CATCH error handling

D. Being stored in the database

Answer: C

Explanation: Stored procedures support structured error handling with TRY...CATCH, whereas user-defined functions do not.

Question 5

A developer needs to generate SQL statements dynamically while minimizing SQL injection risks. Which approach is recommended?

A. Use concatenated SQL with EXEC()

B. Store SQL in a view

C. Use a scalar function

D. Use sp_executesql with parameters

Answer: D

Explanation: sp_executesql supports parameterized dynamic SQL, improving security and enabling better plan reuse.

Question 6

Which statement modifies an existing stored procedure?

A. MODIFY PROCEDURE

B. ALTER PROCEDURE

C. UPDATE PROCEDURE

D. CHANGE PROCEDURE

Answer: B

Explanation: ALTER PROCEDURE changes the definition of an existing stored procedure without dropping and recreating it.

Question 7

Which statement about stored procedures is correct?

A. They can be referenced directly in the FROM clause of a SELECT statement.

B. They cannot accept parameters.

C. They can contain transactions and modify database data.

D. They always return exactly one scalar value.

Answer: C

Explanation: Stored procedures support transactions, data modification, and complex procedural logic. They cannot be queried directly in the FROM clause.

Question 8

What is parameter sniffing?

A. Encrypting stored procedure parameters.

B. Automatically validating parameter data types.

C. Caching parameter values for auditing.

D. Creating an execution plan based on parameter values supplied during compilation, which may not always be optimal for future executions.

Answer: D

Explanation: Parameter sniffing occurs when SQL Server optimizes a query using the initial parameter values, which can lead to less efficient plans for different parameter values.

Question 9

Which security benefit is commonly associated with stored procedures?

A. They automatically encrypt all stored data.

B. They eliminate the need for authentication.

C. They prevent all SQL injection attacks regardless of implementation.

D. They allow users to receive EXECUTE permission without requiring direct access to underlying tables.

Answer: D

Explanation: Granting EXECUTE permission on stored procedures helps restrict direct access to tables while encapsulating business logic and access patterns.

Question 10

How are stored procedures commonly used in AI-enabled database solutions?

A. They automatically generate embeddings without application logic.

B. They replace vector indexes.

C. They orchestrate reusable workflows such as preparing data, managing vector operations, logging AI activity, and supporting Retrieval-Augmented Generation (RAG) pipelines.

D. They eliminate the need for application code.

Answer: C

Explanation: Stored procedures centralize business logic and coordinate multi-step operations, making them well suited for AI-related workflows that prepare data, manage vectors, and support RAG processes.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification August 1, 2026

Create table-valued functions (DP-800 Exam Prep)

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:

Inline Table-Valued Functions (iTVFs)
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

Feature	Inline TVF	Multi-Statement TVF
Single SELECT	Yes	No
Multiple statements	No	Yes
Table variable	No	Yes
Better optimizer support	Yes	Limited
Better performance	Usually	Usually slower
Procedural logic	Limited	Yes

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

Feature	TVF	View
Accepts parameters	Yes	No
Returns a table	Yes	Yes
Reusable	Yes	Yes
Can be parameterized	Yes	No

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

TVFs vs. Stored Procedures

Feature	TVF	Stored Procedure
Returns a table	Yes	Can return result sets but not as a table expression
Used in FROM clause	Yes	No
Accepts parameters	Yes	Yes
Can participate in joins	Yes	No

TVFs vs. Scalar Functions

Feature	TVF	Scalar Function
Returns a table	Yes	No
Returns one value	No	Yes
Used in FROM clause	Yes	No
Used in expressions	No	Yes

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

Databases, DP-800, Microsoft Certification August 1, 2026

Create scalar functions (DP-800 Exam Prep)

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

Feature	Scalar Function	Stored Procedure
Returns one value	Yes	No
Can be used in SELECT statements	Yes	No
Can accept parameters	Yes	Yes
Returns a scalar data type	Yes	No
Performs complex procedural operations	Limited	Yes
Can modify database state	No	Yes

Scalar Functions vs. Table-Valued Functions

Feature	Scalar Function	Table-Valued Function
Returns one value	Yes	No
Returns a table	No	Yes
Used in expressions	Yes	Limited
Suitable for row calculations	Yes	No

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

Databases, DP-800, Microsoft Certification August 1, 2026

Create views (DP-800 Exam Prep)

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

Feature	View	Table
Stores data	No (except indexed views)	Yes
Contains rows physically	Normally no	Yes
Can join multiple tables	Yes	No
Can simplify queries	Yes	No
Used for abstraction	Yes	Limited

Views vs. Stored Procedures

Feature	View	Stored Procedure
Returns result sets	Yes	Yes
Accepts parameters	No	Yes
Can perform data modifications	Limited	Yes
Reusable in SELECT statements	Yes	No

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

Databases, DP-800, Microsoft Certification, Uncategorized August 1, 2026

Design and implement partitioning for tables and indexes (DP-800 Exam Prep)

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 partitioning for tables and indexes

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

As databases grow from thousands to millions or even billions of rows, managing and querying data efficiently becomes increasingly challenging. Large tables can lead to longer query execution times, larger maintenance windows, slower backups, and increased index fragmentation. SQL Server and Azure SQL provide table and index partitioning to help address these challenges.

Partitioning divides a large table or index into smaller, more manageable pieces called partitions. Although users and applications continue to view the data as a single table, SQL Server stores and manages the data in separate partitions based on a defined partitioning strategy.

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

What partitioning is
Benefits and limitations of partitioning
Partition functions
Partition schemes
Partition elimination
Partition switching
Partitioned indexes
Maintenance strategies
Best practices

Partitioning is especially valuable in AI-enabled database solutions that store large volumes of historical, telemetry, or transactional data.

What Is Table Partitioning?

Table partitioning divides one logical table into multiple physical partitions.

Applications continue to query the table normally:

			
SELECT *
FROM Sales;

Internally, SQL Server stores the data across multiple partitions.

Example:

			
Sales Table
│
├── Partition 1 (2022)
├── Partition 2 (2023)
├── Partition 3 (2024)
└── Partition 4 (2025)

		

Each partition contains only a subset of the rows.

Why Partition Tables?

Partitioning improves the manageability of very large tables.

Benefits include:

Faster maintenance
Easier archival
Improved query performance through partition elimination
Faster index maintenance
Improved data loading
Simplified backup strategies
Better scalability

It is important to understand that partitioning alone does not automatically improve every query. Benefits are greatest when queries filter on the partitioning column.

Common Partitioning Scenarios

Partitioning is commonly used for:

Sales history
Financial transactions
IoT telemetry
Sensor data
Event logs
AI inference logs
Audit records
Web clickstream data
Time-series databases

Most implementations partition by date.

Horizontal vs. Vertical Partitioning

Horizontal Partitioning

Rows are divided across partitions.

Example:

			
Sales
-----------------------
2022 rows
2023 rows
2024 rows
2025 rows

		

SQL Server table partitioning is horizontal partitioning.

Vertical Partitioning

Columns are divided into separate tables.

Example:

Customer Table

CustomerID
Name
City

CustomerDetails Table

CustomerID
Biography
Photo

Vertical partitioning is a database design technique, not SQL Server table partitioning.

Partition Functions

A partition function determines how rows are assigned to partitions.

It defines boundary values.

Example:

			
CREATE PARTITION FUNCTION pfSalesDate
(DATE)
AS RANGE RIGHT
FOR VALUES
(
('2023-01-01'),
('2024-01-01'),
('2025-01-01')
);

		

The partition function divides data according to the specified boundary values.

RANGE LEFT vs. RANGE RIGHT

Partition functions support two boundary options.

RANGE LEFT

Boundary value belongs to the partition on the left.

Example:

Boundary:

Value 100 belongs to:

Partition 1

RANGE RIGHT

Boundary value belongs to the partition on the right.

Example:

Boundary:

Value 100 belongs to:

Partition 2

Candidates should understand the difference because it frequently appears in certification exams.

Partition Schemes

A partition function determines how rows are divided.

A partition scheme determines where those partitions are stored.

Example:

			
CREATE PARTITION SCHEME psSales
AS PARTITION pfSalesDate
ALL TO ([PRIMARY]);

Alternatively, different partitions may reside on different filegroups.

Example:

			
→ FG_2022
→ FG_2023
→ FG_2024
→ FG_2025

Creating a Partitioned Table

Example:

			
CREATE TABLE Sales
(
    SaleID INT,
    SaleDate DATE,
    Amount MONEY
)
ON psSales(SaleDate);

		

Rows are automatically placed into the appropriate partition based on the SaleDate value.

Filegroups

Partitions can be stored in different filegroups.

Benefits include:

Independent backup
Independent restore
Better storage management
Distribution across storage devices

Although many Azure SQL Database deployments use the PRIMARY filegroup, understanding filegroups remains important for SQL Server and the DP-800 exam.

Partition Elimination

One of the biggest advantages of partitioning is partition elimination.

Instead of scanning every partition, SQL Server reads only the partitions needed for the query.

Example:

			
SELECT *
FROM Sales
WHERE SaleDate
BETWEEN '2025-01-01'
AND '2025-01-31';

		

SQL Server may read only the partition containing January 2025 data.

Benefits include:

Reduced I/O
Faster execution
Lower CPU usage

Partition elimination works best when predicates reference the partitioning column.

Partitioned Indexes

Indexes can also be partitioned.

Types include:

Clustered indexes
Nonclustered indexes
Columnstore indexes

A partitioned index aligns with the table partitions.

Aligned Indexes

An aligned index uses:

The same partition function
The same partition scheme

Benefits:

Easier maintenance
Faster partition switching
Simplified index rebuilds

Microsoft generally recommends aligned indexes whenever possible.

Non-Aligned Indexes

A non-aligned index uses different partitioning than the underlying table or is not partitioned at all.

Advantages:

Flexibility

Disadvantages:

More complex maintenance
Cannot participate in some partition operations
May reduce the benefits of partition switching

Partition Switching

Partition switching is one of SQL Server’s most powerful maintenance features.

Instead of copying millions of rows, SQL Server simply changes metadata.

Example:

			
Current Table
│
├── Partition 2024
├── Partition 2025
└── Partition 2026
↓
Switch 2024
↓
Archive Table

		

The operation completes very quickly because no data movement occurs.

Benefits of Partition Switching

Typical uses include:

Archiving old data
Loading new data
ETL processing
Data warehouse maintenance
Rolling window scenarios

Large tables can be maintained with minimal downtime.

Sliding Window Technique

Many databases maintain a rolling time window.

Example:

			
Keep:
2023
2024
2025
Remove:
2022
Add:
2026

		

Partition switching makes this process extremely efficient.

Index Maintenance

Large indexes can be rebuilt one partition at a time.

Example:

			
ALTER INDEX IX_Sales
ON Sales
REBUILD PARTITION = 4;

Benefits:

Shorter maintenance windows
Less locking
Reduced resource consumption

Statistics

Each partition maintains its own data distribution statistics.

Accurate statistics help the SQL Server Query Optimizer generate efficient execution plans.

Regular statistics updates remain important for partitioned tables.

Choosing a Partition Key

The partition key should:

Be commonly filtered
Divide data evenly
Support partition elimination
Match maintenance requirements

Good candidates include:

TransactionDate
OrderDate
EventDate
CustomerRegion
FiscalYear

Date columns are the most common partition keys.

When Not to Partition

Partitioning is not appropriate for every table.

Avoid partitioning when:

Tables are small.
Queries rarely filter on the partition key.
Maintenance requirements are minimal.
Administrative complexity outweighs the benefits.

Partitioning introduces additional design and maintenance considerations.

AI-Enabled Database Scenarios

Partitioning is valuable in AI-enabled solutions because AI systems often generate large volumes of data.

Examples include:

Prompt history
Chat logs
Model inference records
Telemetry
IoT streams
Sensor data
Feature store history
Training datasets
Experiment tracking

Partitioning enables efficient archival, querying, and maintenance of these growing datasets.

Best Practices

Partition only large tables that benefit from improved manageability or query performance.
Choose a partition key that aligns with common filtering patterns.
Use aligned indexes whenever practical.
Partition by date for most time-series workloads.
Use partition elimination to reduce unnecessary I/O.
Use partition switching for fast archival and data loading.
Monitor partition sizes to avoid skewed data distribution.
Keep statistics updated on partitioned tables.
Test execution plans to confirm partition elimination is occurring.

Common Exam Tips

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

A partition function defines how rows are divided into partitions.
A partition scheme maps partitions to filegroups.
Partition elimination allows SQL Server to read only the necessary partitions when queries filter on the partition key.
Partition switching is a metadata operation and does not physically copy data.
Aligned indexes use the same partition function and partition scheme as the underlying table.
RANGE LEFT and RANGE RIGHT determine which partition contains the boundary value.
Partitioning improves manageability and can improve query performance, but it does not automatically make every query faster.

Practice Exam Questions

Question 1

A company stores ten years of sales data and frequently queries only the current month’s transactions. Which SQL Server feature can help reduce the amount of data scanned by these queries?

A. Database mirroring

B. Table partitioning

C. Row-level security

D. Dynamic data masking

Answer: B

Explanation: Table partitioning, combined with partition elimination, enables SQL Server to access only the relevant partition when queries filter on the partitioning column, reducing I/O and improving performance.

Question 2

What is the primary purpose of a partition function?

A. To define the physical storage location of partitions

B. To create indexes for each partition

C. To determine how rows are assigned to partitions based on boundary values

D. To rebuild fragmented indexes

Answer: C

Explanation: A partition function defines the partition boundaries and determines which partition stores each row.

Question 3

Which SQL Server object maps partitions to one or more filegroups?

A. Partition scheme

B. Partition function

C. File stream

D. Sequence

Answer: A

Explanation: A partition scheme associates the partitions defined by a partition function with specific filegroups.

Question 4

A query filters on the partitioning column of a partitioned table. Which optimization allows SQL Server to read only the required partitions?

A. Predicate pushdown

B. Partition elimination

C. Batch mode execution

D. Adaptive joins

Answer: B

Explanation: Partition elimination enables SQL Server to skip partitions that cannot contain qualifying rows, reducing I/O and improving performance.

Question 5

Which statement accurately describes partition switching?

A. It copies data row by row between tables.

B. It compresses partitions before moving them.

C. It moves an entire partition using a metadata operation without copying the data.

D. It permanently merges two partitions into one.

Answer: C

Explanation: Partition switching is a metadata-only operation that quickly transfers a partition between compatible tables without physically moving the data.

Question 6

Which partitioning strategy is most commonly used for large transactional and historical databases?

A. Partitioning by customer name

B. Partitioning by transaction date

C. Partitioning by product description

D. Partitioning by postal code

Answer: B

Explanation: Date-based partitioning is common because it supports efficient querying, maintenance, archival, and sliding-window scenarios.

Question 7

Which statement about aligned indexes is correct?

A. They always use a different partition scheme than the table.

B. They cannot be rebuilt independently.

C. They use the same partition function and partition scheme as the underlying table.

D. They eliminate the need for clustered indexes.

Answer: C

Explanation: An aligned index shares the same partition function and partition scheme as its table, simplifying maintenance and enabling features such as partition switching.

Question 8

What is the primary benefit of rebuilding an index one partition at a time?

A. It automatically repartitions the table.

B. It reduces maintenance impact by limiting the work to the affected partition.

C. It converts nonclustered indexes into clustered indexes.

D. It eliminates the need to update statistics.

Answer: B

Explanation: Rebuilding only the affected partition reduces resource usage, shortens maintenance windows, and minimizes locking compared to rebuilding the entire index.

Question 9

Which statement best describes RANGE RIGHT in a partition function?

A. Boundary values belong to the partition on the left.

B. Boundary values are ignored.

C. Boundary values are stored in every partition.

D. Boundary values belong to the partition on the right.

Answer: D

Explanation: With RANGE RIGHT, rows containing the boundary value are placed into the partition to the right of the boundary.

Question 10

A company maintains five years of historical telemetry data and archives the oldest year every January while adding a new year’s partition. Which partitioning technique best supports this maintenance strategy?

A. Computed columns

B. Filtered indexes

C. Sliding window partitioning using partition switching

D. Indexed views

Answer: C

Explanation: A sliding-window strategy combined with partition switching enables administrators to efficiently archive old partitions and add new ones with minimal downtime because the operation is metadata-based.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Design and Implement SEQUENCES (DP-800 Exam Prep)

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

Feature	SEQUENCE	IDENTITY
Independent database object	✔	✖
Bound to a table	✖	✔
Shared across multiple tables	✔	✖
Generate values before INSERT	✔	✖
Can restart	✔	Limited (DBCC CHECKIDENT)
Supports cycling	✔	✖
Supports caching	✔	Internal only
Retrieved explicitly	✔	Automatically during INSERT

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:

The next execution returns:

Then:

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:

INCREMENT BY

Defines how much the sequence changes.

Example:

INCREMENT BY 10

Generated values:

Negative increments are also supported.

Example:

INCREMENT BY -1

Produces:

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:

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

Data Development, Databases, DP-800, Microsoft Certification August 1, 2026

Design and implement database constraints, including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT (DP-800 Exam Prep)

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 database constraints, including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT

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

Database constraints are one of the most important mechanisms for maintaining data integrity in SQL Server and Azure SQL Database. A constraint is a rule that SQL Server automatically enforces whenever data is inserted, updated, or deleted. Instead of relying solely on application logic, constraints ensure that only valid, consistent, and meaningful data is stored in the database.

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

The purpose of each constraint type
When to use each constraint
How constraints enforce data integrity
How constraints affect performance and database design
Best practices for implementing constraints

Proper use of constraints improves application reliability, reduces programming errors, and helps maintain high-quality data for reporting, analytics, and AI-enabled applications.

What Are Database Constraints?

A database constraint is a rule applied to a table or column that restricts the type of data that can be stored.

Constraints help ensure:

Entity integrity
Referential integrity
Domain integrity
Data consistency
Data accuracy

Without constraints, invalid or inconsistent data can easily enter the database, leading to application errors and unreliable reports.

Types of Constraints

The primary constraint types covered on the DP-800 exam include:

Constraint	Purpose
PRIMARY KEY	Uniquely identifies each row
FOREIGN KEY	Maintains relationships between tables
UNIQUE	Prevents duplicate values
CHECK	Restricts acceptable values
DEFAULT	Automatically supplies a value when none is provided

PRIMARY KEY Constraint

Purpose

A PRIMARY KEY uniquely identifies every row in a table.

Characteristics:

Values must be unique.
NULL values are not allowed.
Only one PRIMARY KEY can exist per table.
SQL Server automatically creates a unique index to enforce the constraint (clustered by default unless otherwise specified).

Example

			
CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

		

Every customer must have a unique CustomerID.

Composite Primary Keys

A PRIMARY KEY may consist of multiple columns.

Example:

			
CREATE TABLE OrderDetails
(
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

		

The combination of OrderID and ProductID must be unique.

Composite keys are commonly used in junction (bridge) tables.

Natural vs. Surrogate Keys

Natural Key

A value that already exists in the business domain.

Examples:

Social Security Number
Email address
Vehicle Identification Number (VIN)

Advantages:

Business meaning
No additional column required

Disadvantages:

May change
Can be lengthy
May not always be unique globally

Surrogate Key

An artificial identifier created solely for the database.

Example:

CustomerID INT IDENTITY(1,1)

Advantages:

Stable
Compact
Efficient for indexing
Easy to join

Most SQL Server applications use surrogate keys as PRIMARY KEY values.

FOREIGN KEY Constraint

Purpose

A FOREIGN KEY maintains referential integrity between related tables.

It ensures that values in one table correspond to existing values in another.

Example

			
CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

		

An order cannot reference a customer that does not exist.

Referential Integrity

Foreign keys prevent:

Orphan records
Invalid relationships
Accidental inconsistencies

Example:

Customers

CustomerID
1
2
3

Orders

CustomerID
2

CustomerID 5 cannot be inserted because it does not exist.

Cascade Actions

Foreign keys support optional cascading behavior.

CASCADE DELETE

Deleting the parent automatically deletes child rows.

Example:

			
FOREIGN KEY(CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE

CASCADE UPDATE

Updates to the parent key automatically update child rows.

Example:

ON UPDATE CASCADE

SET NULL

When the parent row is deleted, the child foreign key becomes NULL.

ON DELETE SET NULL

Requires the foreign key column to allow NULL values.

SET DEFAULT

When the parent row is deleted, the child receives its default value.

ON DELETE SET DEFAULT

Requires a DEFAULT constraint on the foreign key column.

NO ACTION (Default)

SQL Server prevents deletion or update if related child rows exist.

This is the default behavior.

UNIQUE Constraint

Purpose

A UNIQUE constraint prevents duplicate values while allowing the column to serve as an alternate key.

Unlike a PRIMARY KEY:

Multiple UNIQUE constraints may exist in a table.
A UNIQUE constraint is not the table’s primary identifier.

Example

			
CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    EmailAddress NVARCHAR(255) UNIQUE
);

		

No two employees can have the same email address.

UNIQUE and NULL Values

A UNIQUE constraint allows at most one NULL value in SQL Server.

Example:

Email
alice@example.com
bob@example.com
NULL

A second NULL violates the UNIQUE constraint.

Composite UNIQUE Constraints

Example:

UNIQUE (FirstName, LastName, BirthDate)

Only the combination must be unique.

CHECK Constraint

Purpose

A CHECK constraint restricts allowable values.

It enforces business rules directly within the database.

Example

CHECK (Salary > 0)

Negative salaries cannot be inserted.

Multiple Conditions

Example:

			
CHECK
(
Age >= 18
AND Age <= 65
)

		

Character Validation

Example:

			
CHECK
(
Status IN
('New','Active','Closed')
)

		

Only approved values are permitted.

Date Validation

Example:

			
CHECK
(
HireDate <= GETDATE()
)

Preventing future hire dates may be appropriate depending on business requirements. (Be aware that nondeterministic functions such as GETDATE() can affect certain indexing scenarios, but they are permitted in CHECK constraints.)

DEFAULT Constraint

Purpose

DEFAULT automatically supplies a value when none is specified.

Example

			
Status NVARCHAR(20)
DEFAULT 'Pending'

If Status is omitted:

			
INSERT INTO Orders
(OrderID)
VALUES
(101);

SQL Server inserts:

Pending

Using Functions

Defaults often use built-in functions.

Example:

			
CreatedDate DATETIME2
DEFAULT SYSDATETIME()

Other common examples include:

DEFAULT NEWID()

DEFAULT SUSER_SNAME()

Named Constraints

Rather than allowing SQL Server to generate names automatically, explicitly naming constraints simplifies administration.

Example:

			
CONSTRAINT PK_Customers
PRIMARY KEY(CustomerID)

Benefits include:

Easier troubleshooting
Easier scripting
Easier deployment
Easier maintenance

Adding Constraints to Existing Tables

Example:

			
ALTER TABLE Employees
ADD CONSTRAINT CK_Salary
CHECK (Salary > 0);

Example:

			
ALTER TABLE Employees
ADD CONSTRAINT UQ_Email
UNIQUE (EmailAddress);

Removing Constraints

Example:

			
ALTER TABLE Employees
DROP CONSTRAINT CK_Salary;

Constraint Evaluation

Constraints are enforced whenever data modifications occur.

Examples include:

INSERT
UPDATE
MERGE

If a constraint is violated:

The statement fails.
The transaction may be rolled back depending on transaction handling.
SQL Server returns an error.

Constraints vs. Indexes

Although related, constraints and indexes serve different purposes.

Constraint	Index
Enforces business rules	Improves query performance
Maintains data integrity	Speeds data retrieval
May automatically create an index	Does not enforce business rules (except unique indexes)

For example:

PRIMARY KEY creates a unique index.
UNIQUE creates a unique index.
CHECK does not create an index.
DEFAULT does not create an index.

Constraints and AI-Enabled Applications

AI applications depend on high-quality, trustworthy data.

Constraints help ensure:

Clean training data
Accurate feature engineering
Reliable vector metadata
Consistent model inputs
Reduced preprocessing effort

For example:

CHECK constraints can prevent impossible values (such as negative ages).
FOREIGN KEY constraints ensure relationships remain valid.
DEFAULT constraints automatically populate timestamps used for AI event tracking.
UNIQUE constraints prevent duplicate identities that could bias analytics.

Best Practices

Define PRIMARY KEY constraints for every table.
Prefer surrogate keys for most transactional systems.
Use FOREIGN KEY constraints to enforce relationships instead of relying solely on application logic.
Use UNIQUE constraints for alternate keys such as email addresses or account numbers.
Apply CHECK constraints to enforce business rules whenever practical.
Use DEFAULT constraints for common initial values such as timestamps and statuses.
Explicitly name constraints using consistent naming conventions.
Avoid disabling constraints except during carefully managed bulk loading scenarios.
Review cascade actions carefully to avoid unintended data loss.
Validate existing data before adding new constraints to production tables.

Common Exam Tips

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

Every table can have only one PRIMARY KEY, but it may consist of one or more columns.
A PRIMARY KEY cannot contain NULL values.
A FOREIGN KEY enforces referential integrity between related tables.
NO ACTION is the default behavior for FOREIGN KEY delete and update operations.
A table can have multiple UNIQUE constraints.
A CHECK constraint enforces domain or business rules.
A DEFAULT constraint supplies a value only when one is not explicitly provided during an INSERT.
PRIMARY KEY and UNIQUE constraints automatically create unique indexes to enforce uniqueness.
CHECK and DEFAULT constraints do not create indexes.

Practice Exam Questions

Question 1

A database designer needs to ensure that every customer record has a unique identifier that cannot contain NULL values. Which constraint should be used?

A. UNIQUE

B. CHECK

C. PRIMARY KEY

D. FOREIGN KEY

Answer: C

Explanation: A PRIMARY KEY uniquely identifies every row in a table and does not allow NULL values. Each table can have only one PRIMARY KEY.

Question 2

An Orders table contains a CustomerID column that must always reference an existing customer in the Customers table. Which constraint enforces this relationship?

A. FOREIGN KEY

B. DEFAULT

C. UNIQUE

D. CHECK

Answer: A

Explanation: A FOREIGN KEY enforces referential integrity by ensuring that values in one table correspond to existing values in another table.

Question 3

A company wants every new order to receive a status of “Pending” unless another value is explicitly supplied during insertion. Which constraint should be implemented?

A. CHECK

B. UNIQUE

C. FOREIGN KEY

D. DEFAULT

Answer: D

Explanation: A DEFAULT constraint automatically assigns a value when one is not provided in an INSERT statement.

Question 4

A database must prevent employees from having duplicate email addresses, but EmployeeID already serves as the table’s PRIMARY KEY. Which constraint should be added to the EmailAddress column?

A. PRIMARY KEY

B. DEFAULT

C. UNIQUE

D. CHECK

Answer: C

Explanation: A UNIQUE constraint enforces uniqueness for a column without making it the table’s primary identifier.

Question 5

Which constraint is best suited to ensure that an employee’s salary is always greater than zero?

A. CHECK

B. UNIQUE

C. DEFAULT

D. FOREIGN KEY

Answer: A

Explanation: A CHECK constraint validates that column values satisfy a specified logical condition, such as Salary > 0.

Question 6

Which statement about PRIMARY KEY constraints is correct?

A. A table can contain multiple PRIMARY KEY constraints.

B. PRIMARY KEY values may contain NULL values.

C. PRIMARY KEY constraints automatically enforce uniqueness and prevent NULL values.

D. PRIMARY KEY constraints cannot be referenced by FOREIGN KEY constraints.

Answer: C

Explanation: A PRIMARY KEY enforces uniqueness and does not permit NULL values. It is also commonly referenced by FOREIGN KEY constraints.

Question 7

What is the default action if a parent row referenced by a FOREIGN KEY is deleted without specifying any cascade option?

A. CASCADE

B. SET NULL

C. SET DEFAULT

D. NO ACTION

Answer: D

Explanation: Unless another referential action is specified, SQL Server uses NO ACTION, preventing deletion when related child rows exist.

Question 8

A junction table contains OrderID and ProductID, and each combination must be unique. Which design is most appropriate?

A. Add separate UNIQUE constraints to each column.

B. Create a composite PRIMARY KEY using OrderID and ProductID.

C. Create a DEFAULT constraint on both columns.

D. Use a CHECK constraint to compare the values.

Answer: B

Explanation: A composite PRIMARY KEY ensures that the combination of OrderID and ProductID is unique while allowing each individual value to appear multiple times as part of different combinations.

Question 9

Which statement best describes a UNIQUE constraint?

A. It prevents duplicate values and can be defined multiple times within a table.

B. It automatically creates foreign key relationships.

C. It validates numeric ranges.

D. It supplies default values during inserts.

Answer: A

Explanation: A table may contain multiple UNIQUE constraints, each preventing duplicate values in a column or combination of columns.

Question 10

Why are database constraints particularly valuable in AI-enabled database solutions?

A. They automatically generate machine learning models.

B. They improve graphics rendering performance.

C. They eliminate the need for indexes.

D. They help ensure high-quality, consistent data for analytics and AI workloads.

Answer: D

Explanation: Constraints improve data quality by enforcing consistency, valid relationships, and business rules, which reduces data cleansing and improves the reliability of AI models and analytical processes.

Go to the DP-800 Exam Prep Hub main page