Category: Data Development

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

Write queries that include regular expressions, such as REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE (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 regular expressions, such as REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE

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

Regular expressions (regex) are powerful pattern-matching expressions used to search, validate, extract, replace, and manipulate text. They have been available in many programming languages for years and are now available in SQL Server 2025 (17.x) Preview and Azure SQL Database through native T-SQL regular expression functions.

For developers, regex significantly simplifies many text-processing tasks that previously required combinations of LIKE, PATINDEX, CHARINDEX, SUBSTRING, REPLACE, and custom T-SQL logic.

For the DP-800: Developing AI-Enabled Database Solutions exam, understanding these functions is increasingly important because AI-enabled applications frequently process:

User prompts
Chat conversations
Log files
Emails
Product descriptions
Documents
JSON data
Metadata
Search indexes

Regular expressions allow SQL Server to efficiently validate, search, and transform this semi-structured text.

What is a Regular Expression?

A regular expression is a sequence of characters that defines a search pattern.

For example:

Pattern	Meaning
`\d`	Any digit
`[A-Z]`	Uppercase letter
`[a-z]`	Lowercase letter
`[A-Za-z]`	Any letter
`.`	Any character
`.*`	Zero or more characters
`+`	One or more occurrences
`?`	Optional occurrence
`^`	Beginning of string
`$`	End of string
`\s`	Whitespace
`\w`	Word character
`[^0-9]`	Anything except digits

Example:

^\d{5}$

Matches exactly five digits.

Examples:

			
12345 ✔
98765 ✔
1234 ✘
123456 ✘
ABCDE ✘

		

SQL Server Regular Expression Functions

The newest T-SQL regular expression functions include:

REGEXP_LIKE()
REGEXP_REPLACE()
REGEXP_SUBSTR()
REGEXP_INSTR()
REGEXP_COUNT()
REGEXP_MATCHES()
REGEXP_SPLIT_TO_TABLE()

Each function serves a different purpose.

REGEXP_LIKE()

Purpose

Tests whether text matches a regular expression.

Syntax

REGEXP_LIKE(expression, pattern)

Example

			
SELECT CustomerEmail
FROM Customers
WHERE REGEXP_LIKE(
    CustomerEmail,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

		

This returns only rows containing valid email addresses.

Common Uses

Validate email addresses
Validate ZIP codes
Validate phone numbers
Validate product codes
Validate license numbers
Check AI-generated output

REGEXP_REPLACE()

Purpose

Replaces matching text.

Syntax

REGEXP_REPLACE(expression, pattern, replacement)

Example

Remove non-numeric characters from a phone number.

			
SELECT REGEXP_REPLACE(
'(555) 123-4567',
'[^0-9]',
''
);

		

Output

5551234567

Example

Replace multiple spaces with one space.

			
SELECT REGEXP_REPLACE(
'John     Smith',
'\s+',
' '
);

		

Output

John Smith

Common Uses

Data cleansing
Standardization
Removing punctuation
Removing HTML tags
Cleaning AI responses

REGEXP_SUBSTR()

Purpose

Returns the first substring that matches a pattern.

Syntax

REGEXP_SUBSTR(expression, pattern)

Example

			
SELECT REGEXP_SUBSTR(
'Invoice #INV-2025-1045',
'INV-[0-9-]+'
);

Output

INV-2025-1045

Useful for extracting:

Invoice numbers
Tracking numbers
Product IDs
URLs
Dates

REGEXP_INSTR()

Purpose

Returns the starting position of a pattern.

Syntax

REGEXP_INSTR(expression, pattern)

Example

			
SELECT REGEXP_INSTR(
'Customer ID: 12345',
'\d+'
);

Output

If no match exists, the function returns 0.

REGEXP_COUNT()

Purpose

Counts how many times a pattern occurs.

Syntax

REGEXP_COUNT(expression, pattern)

Example

			
SELECT REGEXP_COUNT(
'cat dog cat bird cat',
'cat'
);

Output

Useful for:

Counting hashtags
Counting keywords
Counting repeated words
Measuring AI response quality

REGEXP_MATCHES()

Purpose

Returns all substrings that match a pattern.

Unlike REGEXP_SUBSTR(), which returns only the first match, REGEXP_MATCHES() returns every match.

Example

			
SELECT *
FROM REGEXP_MATCHES(
'Phone: 555-1111 Office: 555-2222',
'\d{3}-\d{4}'
);

		

Output

			
555-1111
555-2222

Common uses include:

Finding all phone numbers
Extracting URLs
Extracting hashtags
Finding dates

REGEXP_SPLIT_TO_TABLE()

Purpose

Splits text into rows using a regular expression delimiter.

Example

			
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'SQL,Azure,AI,Python',
','
);

		

Output

Value
SQL
Azure
AI
Python

Example

Split on one or more spaces.

			
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'SQL   Azure   AI',
'\s+'
);

		

Comparing the Functions

Function	Purpose
REGEXP_LIKE()	Test whether text matches a pattern
REGEXP_REPLACE()	Replace matching text
REGEXP_SUBSTR()	Return first matching substring
REGEXP_INSTR()	Return position of first match
REGEXP_COUNT()	Count matches
REGEXP_MATCHES()	Return all matches
REGEXP_SPLIT_TO_TABLE()	Split text into rows

Common Regular Expression Patterns

Email

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

US ZIP Code

^\d{5}$

ZIP+4

^\d{5}-\d{4}$

Phone Number

^\(?\d{3}\)?[- ]?\d{3}[- ]?\d{4}$

GUID

^[0-9A-Fa-f-]{36}$

URL

https?://.*

Integer

^\d+$

Decimal Number

^\d+\.\d+$

AI-Enabled Database Scenarios

Regular expressions are especially valuable when AI applications generate or consume semi-structured text.

Examples include:

Validating AI-generated email addresses
Extracting invoice numbers from chatbot responses
Cleaning OCR text
Removing HTML from generated content
Parsing metadata
Detecting URLs in AI responses
Validating JSON fragments
Finding sensitive information before storage
Identifying product codes
Processing vector search metadata

Performance Considerations

Regular expressions are more computationally expensive than simple string comparisons.

To improve performance:

Use simple patterns whenever possible.
Filter rows before applying regex functions.
Avoid leading wildcards when simpler predicates suffice.
Avoid unnecessarily complex nested expressions.
Consider computed columns for frequently evaluated values.
Benchmark regex queries on large datasets.
Use indexes to reduce the number of rows that require regex evaluation.

Best Practices

Keep patterns simple and readable.
Test regex thoroughly using representative data.
Escape special characters when needed.
Validate user-supplied patterns to prevent errors.
Use anchors (^ and $) when matching an entire string.
Use character classes instead of long OR conditions.
Prefer regex only when simpler string functions cannot meet the requirement.
Document complex expressions for maintainability.
Handle NULL values appropriately.
Monitor performance on large datasets.

Common Exam Tips

For the DP-800 exam, remember:

REGEXP_LIKE() validates or filters text.
REGEXP_REPLACE() modifies text.
REGEXP_SUBSTR() extracts the first match.
REGEXP_INSTR() returns the position of a match.
REGEXP_COUNT() counts pattern occurrences.
REGEXP_MATCHES() returns all matches.
REGEXP_SPLIT_TO_TABLE() converts delimited text into rows.
Regular expressions are ideal for processing semi-structured text used by AI-enabled applications.
Regex offers significantly more flexibility than LIKE and PATINDEX for complex pattern matching.

Practice Exam Questions

Question 1

A developer needs to validate that a column contains only properly formatted email addresses. Which function should be used?

A. REGEXP_REPLACE()

B. REGEXP_LIKE()

C. REGEXP_SUBSTR()

D. REGEXP_COUNT()

Answer: B

Explanation: REGEXP_LIKE() evaluates whether a string matches a regular expression and is the appropriate function for validating email formats.

Question 2

You need to remove all punctuation from customer phone numbers before storing them. Which function is most appropriate?

A. REGEXP_REPLACE()

B. REGEXP_INSTR()

C. REGEXP_MATCHES()

D. REGEXP_COUNT()

Answer: A

Explanation: REGEXP_REPLACE() replaces matching characters or patterns, making it ideal for removing punctuation or formatting characters.

Question 3

A product description contains multiple serial numbers, and you need to return every matching serial number. Which function should you use?

A. REGEXP_SUBSTR()

B. REGEXP_COUNT()

C. REGEXP_MATCHES()

D. REGEXP_LIKE()

Answer: C

Explanation: REGEXP_MATCHES() returns all occurrences that satisfy the specified regular expression rather than just the first match.

Question 4

You need to extract the first invoice number from a block of text. Which function is the best choice?

A. REGEXP_SPLIT_TO_TABLE()

B. REGEXP_INSTR()

C. REGEXP_SUBSTR()

D. REGEXP_REPLACE()

Answer: C

Explanation: REGEXP_SUBSTR() extracts and returns the first substring that matches the specified regular expression.

Question 5

Which function returns the character position where the first match begins?

A. REGEXP_INSTR()

B. REGEXP_COUNT()

C. REGEXP_MATCHES()

D. REGEXP_REPLACE()

Answer: A

Explanation: REGEXP_INSTR() returns the starting position of the first occurrence of a pattern within a string.

Question 6

A developer needs to determine how many times the word “error” appears in a log entry. Which function should be used?

A. REGEXP_MATCHES()

B. REGEXP_COUNT()

C. REGEXP_SUBSTR()

D. REGEXP_LIKE()

Answer: B

Explanation: REGEXP_COUNT() counts the number of occurrences of a pattern within a string.

Question 7

A comma-separated list stored in a column must be converted into one row per value. Which function is designed for this task?

A. REGEXP_REPLACE()

B. REGEXP_INSTR()

C. REGEXP_SPLIT_TO_TABLE()

D. REGEXP_SUBSTR()

Answer: C

Explanation: REGEXP_SPLIT_TO_TABLE() divides a string into multiple rows using a regular expression as the delimiter.

Question 8

Which regular expression pattern matches exactly five digits?

A. \d+

B. ^\d{5}$

C. \d{5,}

D. [0-9]*

Answer: B

Explanation: ^\d{5}$ anchors the match to the beginning and end of the string and requires exactly five digits.

Question 9

Why are regular expressions particularly valuable in AI-enabled database solutions?

A. They automatically train AI models.

B. They replace JSON processing.

C. They eliminate the need for SQL indexes.

D. They efficiently validate, extract, and transform semi-structured text generated by AI systems.

Answer: D

Explanation: AI applications frequently exchange semi-structured text, and regex functions simplify validation, extraction, cleansing, and transformation directly within SQL.

Question 10

When should you prefer regular expressions over simple string functions such as LIKE?

A. For every text comparison.

B. Only when searching numeric columns.

C. When complex pattern matching or text extraction is required.

D. Only when working with JSON data.

Answer: C

Explanation: Regular expressions are best suited for sophisticated pattern matching, validation, and extraction tasks that cannot be easily implemented using simpler string functions.

Go to the DP-800 Exam Prep Hub main page

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

Write queries that include JSON 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 JSON 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

JSON (JavaScript Object Notation) has become one of the most common formats for exchanging and storing structured data in modern applications. SQL Server and Azure SQL Database provide native JSON support that allows developers to parse, query, modify, and generate JSON data without requiring a separate document database.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand how to use T-SQL JSON functions to work with JSON documents stored in SQL Server tables or received from external applications and services. JSON capabilities are particularly valuable when integrating relational databases with REST APIs, cloud services, AI applications, and modern web applications.

Unlike XML, SQL Server does not have a dedicated JSON data type (in generally available releases covered by the current DP-800 learning content). Instead, JSON documents are typically stored in nvarchar columns and processed using built-in JSON functions.

This article covers the JSON functionality emphasized in the current Microsoft Learn curriculum, including:

Understanding JSON in SQL Server
Validating JSON documents
Extracting scalar values
Extracting objects and arrays
Parsing JSON into relational rows
Modifying JSON documents
Returning JSON from queries
Performance considerations
AI-enabled database scenarios
Best practices

Understanding JSON in SQL Server

JSON represents data as key-value pairs and arrays.

Example JSON document:

			
{
  "CustomerID": 1001,
  "Name": "John Smith",
  "Email": "john@contoso.com",
  "Orders": [
    {
      "OrderID": 501,
      "Amount": 250.00
    },
    {
      "OrderID": 502,
      "Amount": 120.00
    }
  ]
}

		

SQL Server stores JSON as plain text but provides functions that understand the JSON structure.

JSON Support in SQL Server

The primary JSON features include:

ISJSON()
JSON_VALUE()
JSON_QUERY()
JSON_MODIFY()
OPENJSON
FOR JSON

These functions allow developers to:

Validate JSON
Retrieve values
Retrieve arrays and objects
Update JSON documents
Convert JSON into relational tables
Generate JSON output

ISJSON()

ISJSON() determines whether a string contains valid JSON.

Syntax:

ISJSON(expression)

Example:

SELECT ISJSON('{"Name":"John"}');

Result:

Invalid JSON returns:

Common use cases include:

Data validation
Import validation
Preventing malformed JSON from entering the database

JSON_VALUE()

JSON_VALUE() extracts a single scalar value from a JSON document.

Syntax:

JSON_VALUE(expression, path)

Example:

			
SELECT JSON_VALUE(
'{
   "Customer":
   {
      "Name":"John Smith"
   }
}',
'$.Customer.Name');

		

Result:

John Smith

JSON_VALUE() returns values such as:

Strings
Numbers
Dates
Booleans

It does not return JSON objects or arrays.

JSON Path Expressions

JSON functions use path expressions to locate data.

Examples:

Path	Meaning
`$`	Root object
`$.Customer`	Customer object
`$.Customer.Name`	Name property
`$.Orders[0]`	First order
`$.Orders[1].Amount`	Amount of second order

Understanding JSON path syntax is an important DP-800 exam objective.

JSON_QUERY()

JSON_QUERY() extracts an object or an array instead of a scalar value.

Example:

			
SELECT JSON_QUERY(
'{
   "Orders":
   [
      {"OrderID":1},
      {"OrderID":2}
   ]
}',
'$.Orders');

		

Result:

			
[
   {"OrderID":1},
   {"OrderID":2}
]

Use JSON_QUERY() whenever the requested value is another JSON object or array.

JSON_VALUE() vs. JSON_QUERY()

JSON_VALUE()	JSON_QUERY()
Returns a scalar value	Returns an object or array
Returns text	Returns JSON
Used for individual properties	Used for nested objects and arrays

Choosing the correct function is a common exam topic.

OPENJSON

OPENJSON converts JSON into relational rows and columns.

Example:

			
DECLARE @Orders nvarchar(max) =
'[
    {"OrderID":101,"Amount":150},
    {"OrderID":102,"Amount":250}
]';
SELECT *
FROM OPENJSON(@Orders);

		

Result:

Key	Value	Type
0	{…}	5
1	{…}	5

OPENJSON WITH Clause

The WITH clause maps JSON properties to columns.

Example:

			
DECLARE @Orders nvarchar(max) =
'[
    {"OrderID":101,"Amount":150},
    {"OrderID":102,"Amount":250}
]';
SELECT *
FROM OPENJSON(@Orders)
WITH
(
    OrderID int,
    Amount decimal(10,2)
);

		

Result:

OrderID	Amount
101	150.00
102	250.00

This is the preferred method when importing structured JSON into SQL tables.

JSON_MODIFY()

JSON_MODIFY() updates a JSON document.

Example:

			
DECLARE @Customer nvarchar(max)=
'{"Name":"John","City":"Seattle"}';
SELECT JSON_MODIFY(
@Customer,
'$.City',
'Orlando');

		

Result:

			
{
   "Name":"John",
   "City":"Orlando"
}

JSON_MODIFY() can:

Update values
Insert properties
Delete properties by assigning NULL

FOR JSON

FOR JSON converts SQL query results into JSON.

Example:

			
SELECT
    CustomerID,
    Name
FROM Customers
FOR JSON AUTO;

		

Output:

			
[
  {
      "CustomerID":1,
      "Name":"John"
  },
  {
      "CustomerID":2,
      "Name":"Mary"
  }
]

		

FOR JSON AUTO vs. FOR JSON PATH

FOR JSON AUTO

Automatically generates JSON based on table structure.

Example:

			
SELECT CustomerID, Name
FROM Customers
FOR JSON AUTO;

Little customization is available.

FOR JSON PATH

Provides complete control over the generated JSON structure.

Example:

			
SELECT
CustomerID AS "Customer.ID",
Name AS "Customer.Name"
FROM Customers
FOR JSON PATH;

		

This allows nested objects and custom property names.

Working with Nested JSON

Example:

			
{
   "Customer":
   {
      "Name":"John",
      "Address":
      {
         "City":"Orlando"
      }
   }
}

		

Retrieve the city:

			
SELECT JSON_VALUE(
@Customer,
'$.Customer.Address.City');

Loading JSON into Tables

Example:

			
INSERT INTO Orders(OrderID, Amount)
SELECT OrderID, Amount
FROM OPENJSON(@Orders)
WITH
(
    OrderID int,
    Amount decimal(10,2)
);

		

This approach is frequently used when consuming REST APIs.

Returning JSON from Stored Procedures

Stored procedures often return JSON to client applications.

Example:

			
SELECT *
FROM Customers
FOR JSON PATH;

Applications can consume the JSON without additional transformation.

JSON and Azure Services

JSON is widely used with:

Azure Functions
Azure Logic Apps
Azure App Service
Azure API Management
REST APIs
Power Apps
Power Automate

JSON enables efficient communication between SQL databases and cloud-based applications.

AI-Enabled Database Scenarios

JSON plays a significant role in AI-enabled solutions because many AI services exchange information using JSON documents.

Common scenarios include:

Receiving prompts from client applications
Storing AI model responses
Logging chatbot conversations
Storing document metadata
Integrating Azure AI services
Consuming REST APIs
Passing structured data to Retrieval-Augmented Generation (RAG) pipelines
Returning AI-generated content to applications

For example, a SQL stored procedure might accept a JSON request from an application, extract values with JSON_VALUE() or OPENJSON, query relational data, and return results as JSON using FOR JSON PATH.

Emerging JSON Functions (SQL Server 2025 and Azure SQL Database)

Recent versions of Azure SQL Database and SQL Server introduce additional JSON functions that make it easier to construct, aggregate, and search JSON data directly within SQL queries. While these functions are newer than the core JSON functions covered earlier, they represent the future direction of SQL Server’s native JSON capabilities and are useful to understand.

These functions include:

JSON_OBJECT()
JSON_ARRAY()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
JSON_CONTAINS()

JSON_OBJECT()

JSON_OBJECT() creates a JSON object directly from key-value pairs.

Instead of manually concatenating strings, SQL Server automatically generates properly formatted JSON.

Syntax:

			
JSON_OBJECT(
    'key1': value1,
    'key2': value2
)

Example:

			
SELECT JSON_OBJECT(
    'CustomerID': CustomerID,
    'Name': CustomerName,
    'City': City
)
FROM Customers;

		

Possible output:

			
{
    "CustomerID": 101,
    "Name": "John Smith",
    "City": "Seattle"
}

		

Benefits

Simpler than string concatenation
Automatically escapes special characters
Produces valid JSON
Easier to read and maintain

JSON_ARRAY()

JSON_ARRAY() creates a JSON array from one or more values.

Syntax:

JSON_ARRAY(value1, value2, value3)

Example:

			
SELECT JSON_ARRAY(
    'SQL',
    'Azure',
    'AI',
    'JSON'
);

		

Output:

			
[
    "SQL",
    "Azure",
    "AI",
    "JSON"
]

		

Arrays may contain:

Strings
Numbers
Boolean values
NULL values
Nested JSON objects

This function is particularly useful when returning lists to applications and APIs.

JSON_ARRAYAGG()

JSON_ARRAYAGG() aggregates multiple rows into a single JSON array.

It performs a role similar to STRING_AGG(), but returns properly formatted JSON instead of plain text.

Example:

			
SELECT JSON_ARRAYAGG(CustomerName)
FROM Customers;

Output:

			
[
    "John",
    "Mary",
    "Susan",
    "David"
]

		

It can also aggregate JSON objects.

Example:

			
SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'ID': CustomerID,
        'Name': CustomerName
    )
)
FROM Customers;

		

Output:

			
[
    {
        "ID":101,
        "Name":"John"
    },
    {
        "ID":102,
        "Name":"Mary"
    }
]

		

Common Uses

REST API responses
AI service payloads
Returning collections of objects
Building hierarchical JSON documents

JSON_OBJECTAGG()

JSON_OBJECTAGG() aggregates multiple rows into a single JSON object.

Each row contributes a key-value pair.

Example:

			
SELECT JSON_OBJECTAGG(
    DepartmentName : EmployeeCount
)
FROM DepartmentSummary;

Possible output:

			
{
    "Sales":42,
    "Finance":18,
    "HR":11
}

		

This function is useful when applications require lookup-style JSON objects rather than arrays.

Common scenarios include:

Configuration settings
Summary statistics
Name/value collections
Metadata dictionaries

JSON_CONTAINS()

JSON_CONTAINS() determines whether a JSON document contains a specified value or object.

Example:

			
SELECT JSON_CONTAINS(
    '{"Skills":["SQL","Azure","AI"]}',
    '"Azure"',
    '$.Skills'
);

		

Result:

A return value of:

1 indicates the value exists.
0 indicates it does not exist.

Unlike JSON_VALUE(), which retrieves a value, JSON_CONTAINS() is intended for searching JSON documents.

Typical uses include:

Searching arrays
Validating configuration values
Checking permissions stored as JSON
Verifying tags or categories
Filtering semi-structured data

Comparing the JSON Functions

Function	Purpose	Returns
ISJSON()	Validate JSON	Integer
JSON_VALUE()	Retrieve a scalar value	Scalar
JSON_QUERY()	Retrieve an object or array	JSON
JSON_MODIFY()	Update JSON	JSON
OPENJSON	Convert JSON to rows	Table
FOR JSON	Generate JSON from query results	JSON
JSON_OBJECT()	Create a JSON object	JSON
JSON_ARRAY()	Create a JSON array	JSON
JSON_ARRAYAGG()	Aggregate rows into a JSON array	JSON
JSON_OBJECTAGG()	Aggregate rows into a JSON object	JSON
JSON_CONTAINS()	Test whether JSON contains a value	Boolean (1/0)

AI-Enabled Database Scenarios

These newer JSON functions are especially useful in AI-enabled database solutions because AI applications frequently exchange complex JSON payloads.

Examples include:

Creating structured prompts for large language models (LLMs)
Returning Retrieval-Augmented Generation (RAG) results as JSON arrays
Building JSON responses for Azure AI Foundry or Azure OpenAI applications
Aggregating search results into JSON collections for APIs
Constructing metadata objects for vector search and embeddings
Verifying whether AI-generated JSON responses contain required fields or values

By generating JSON natively within SQL Server, these functions reduce the need for application-side serialization and simplify integrations with cloud services and AI workflows.

Performance Considerations

Because JSON is stored as text, SQL Server must parse the document during queries.

Performance can be improved by:

Storing only necessary JSON data
Using computed columns that extract frequently queried properties
Creating indexes on persisted computed columns
Avoiding repeated parsing of large JSON documents
Using OPENJSON with a WITH clause for structured imports

Best Practices

Validate incoming JSON using ISJSON().
Use JSON_VALUE() for scalar values.
Use JSON_QUERY() for arrays and objects.
Use OPENJSON to convert JSON into relational rows.
Use JSON_MODIFY() to update JSON documents.
Use FOR JSON PATH when customized output is required.
Store JSON only when relational columns are not appropriate.
Index frequently queried JSON properties through computed columns.
Validate JSON path expressions during development.
Keep JSON documents reasonably sized to improve performance.

Common Exam Tips

For the DP-800 exam, remember the following:

SQL Server stores JSON in nvarchar columns.
ISJSON() validates JSON.
JSON_VALUE() returns scalar values.
JSON_QUERY() returns objects and arrays.
JSON_MODIFY() updates JSON documents.
OPENJSON converts JSON into relational rows.
OPENJSON WITH maps JSON properties to typed columns.
FOR JSON AUTO automatically formats query results.
FOR JSON PATH provides greater control over the JSON output.
JSON is commonly used when integrating SQL Server with cloud services, APIs, and AI applications.

Practice Exam Questions

Question 1

Which function validates whether a string contains properly formatted JSON?

A. JSON_QUERY()

B. JSON_MODIFY()

C. OPENJSON

D. ISJSON()

Answer: D

Explanation: ISJSON() returns 1 for valid JSON and 0 for invalid JSON, making it useful for validating incoming data.

Question 2

Which function should you use to extract a single scalar value such as a customer’s name from a JSON document?

A. JSON_QUERY()

B. JSON_VALUE()

C. OPENJSON()

D. FOR JSON

Answer: B

Explanation: JSON_VALUE() returns a single scalar value such as a string, number, or Boolean from a specified JSON path.

Question 3

A developer needs to return an entire JSON array from a document. Which function is appropriate?

A. JSON_QUERY()

B. JSON_VALUE()

C. ISJSON()

D. JSON_MODIFY()

Answer: A

Explanation: JSON_QUERY() returns JSON objects and arrays, whereas JSON_VALUE() returns only scalar values.

Question 4

Which T-SQL feature converts JSON data into relational rows and columns?

A. JSON_VALUE()

B. JSON_QUERY()

C. OPENJSON

D. FOR JSON PATH

Answer: C

Explanation: OPENJSON parses JSON text and returns rows that can be further mapped into relational columns using the WITH clause.

Question 5

Which statement about FOR JSON PATH is correct?

A. It validates JSON documents.

B. It converts JSON into relational tables.

C. It provides control over the structure of generated JSON output.

D. It can only return scalar values.

Answer: C

Explanation: FOR JSON PATH allows developers to customize property names and create nested JSON structures.

Question 6

What is the primary purpose of JSON_MODIFY()?

A. Validate JSON syntax.

B. Retrieve a scalar value.

C. Return an array.

D. Update or insert values within a JSON document.

Answer: D

Explanation: JSON_MODIFY() changes JSON content by updating, inserting, or deleting properties.

Question 7

When importing data from a REST API into SQL Server, which approach provides the most structured mapping between JSON properties and SQL columns?

A. JSON_QUERY()

B. OPENJSON with a WITH clause

C. ISJSON()

D. FOR JSON AUTO

Answer: B

Explanation: The WITH clause allows OPENJSON to map JSON properties directly into strongly typed SQL columns.

Question 8

Which JSON path expression returns the value of the Name property within the Customer object?

A. $.Name.Customer

B. Customer.Name

C. $.Customer.Name

D. $[Customer][Name]

Answer: C

Explanation: JSON path expressions begin at the root ($) and navigate through object properties using dot notation.

Question 9

Why are computed columns often used with JSON data?

A. They convert JSON into XML.

B. They eliminate the need for JSON functions.

C. They allow frequently accessed JSON values to be indexed for improved query performance.

D. They automatically validate JSON syntax.

Answer: C

Explanation: Persisted computed columns can extract JSON properties using JSON_VALUE(), enabling indexes to improve query performance.

Question 10

How are SQL Server JSON functions commonly used in AI-enabled database solutions?

A. They replace relational tables entirely.

B. They create machine learning models directly.

C. They eliminate the need for APIs.

D. They parse, transform, and generate structured JSON exchanged between SQL databases, AI services, REST APIs, and applications.

Answer: D

Explanation: AI services commonly exchange structured JSON payloads. SQL Server JSON functions enable applications to consume, transform, store, and return this data efficiently.

Go to the DP-800 Exam Prep Hub main page

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

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

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

Data Development, DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Monitor data ingestion (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Monitor and optimize an analytics solution (30–35%)
   --> Monitor Fabric items
      --> Monitor data ingestion

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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Overview

Data ingestion is one of the most critical processes in any data engineering solution. Regardless of whether data is ingested through pipelines, Dataflows Gen2, Eventstreams, Spark notebooks, mirroring, shortcuts, or streaming solutions, engineers must ensure that ingestion processes are running successfully, efficiently, and reliably.

In Microsoft Fabric, monitoring data ingestion involves tracking data movement activities, identifying failures, measuring performance, validating data completeness, troubleshooting bottlenecks, and ensuring data arrives in the correct destination on schedule.

For the DP-700 exam, you should understand:

How ingestion monitoring works across Fabric workloads
Monitoring pipelines and Dataflows Gen2
Monitoring Spark jobs and notebooks
Monitoring streaming ingestion
Using monitoring hubs and run history
Detecting ingestion failures
Investigating performance issues
Monitoring data quality and completeness
Best practices for operational monitoring

Why Data Ingestion Monitoring Matters

A data engineering solution is only valuable if data arrives correctly and on time.

Poorly monitored ingestion processes can result in:

Missing data
Incomplete reports
Delayed analytics
Data quality issues
Failed downstream transformations
Business decision errors

Consider an hourly sales ingestion process:

If the process fails at 2:00 AM
No monitoring is in place
The issue is not discovered until business users report incorrect dashboards

Proper monitoring helps detect and resolve problems before they impact users.

Data Ingestion Components in Microsoft Fabric

Several Fabric services perform data ingestion:

Data Pipelines

Used for:

Copy activities
Data movement
Workflow orchestration
ETL/ELT execution

Pipelines often serve as the primary ingestion mechanism for batch data.

Dataflows Gen2

Used for:

Low-code data ingestion
Power Query transformations
ETL development

Dataflows commonly ingest data from SaaS applications, databases, and files.

Spark Notebooks

Used for:

Large-scale ingestion
Custom transformations
Lakehouse loading

Spark jobs frequently handle enterprise-scale ingestion workloads.

Eventstreams

Used for:

Streaming ingestion
Event processing
Real-time data pipelines

Mirroring

Used for:

Near real-time replication
Continuous synchronization
Operational system integration

Monitoring Hub

The Monitoring Hub is the central monitoring experience within Microsoft Fabric.

It allows administrators and engineers to monitor:

Pipeline executions
Dataflow refreshes
Notebook runs
Spark jobs
Warehouse activities
Real-Time Intelligence workloads

The Monitoring Hub provides:

Run status
Start time
End time
Duration
Error messages
Historical execution information

For DP-700, expect questions regarding how to investigate failures and review execution history.

Monitoring Pipeline Executions

Pipelines provide detailed execution tracking.

Each pipeline run includes:

Status
Activity-level details
Runtime metrics
Input/output information
Error details

Typical statuses include:

Status	Meaning
Succeeded	Completed successfully
Failed	One or more activities failed
In Progress	Currently executing
Cancelled	Stopped before completion

Activity-Level Monitoring

Pipeline monitoring drills into individual activities.

Examples:

Copy Data activity
Notebook activity
Dataflow activity
Stored Procedure activity

If a pipeline fails, reviewing activity-level details is often the fastest way to identify the root cause.

Common Pipeline Failures

Authentication Errors

Examples:

Expired credentials
Missing permissions
Invalid service principal access

Network Issues

Examples:

Source unavailable
Connectivity interruptions

Schema Changes

Examples:

Missing columns
Data type mismatches

Capacity Constraints

Examples:

Resource contention
Capacity throttling

Monitoring Dataflows Gen2

Dataflows Gen2 provide refresh history information.

Engineers can monitor:

Refresh success
Refresh failures
Execution duration
Row processing counts

Monitoring refresh history helps identify:

Slow transformations
Source system issues
Data quality problems

Dataflow Refresh History

Common metrics include:

Start time
End time
Duration
Refresh status
Error details

If refresh duration increases significantly over time, it may indicate:

Growing data volumes
Source performance degradation
Inefficient transformations

Monitoring Spark Ingestion Jobs

Spark workloads often support large-scale ingestion processes.

Monitoring includes:

Job execution status
Spark application logs
Resource utilization
Stage execution metrics

Spark Monitoring Metrics

Important metrics include:

Job Duration

Tracks overall execution time.

Executor Usage

Indicates cluster resource consumption.

Task Failures

Shows processing errors.

Data Skew

Identifies uneven partition distribution.

Shuffle Operations

Helps diagnose performance bottlenecks.

Monitoring Streaming Ingestion

Streaming solutions require continuous monitoring.

Common streaming workloads include:

Eventstreams
KQL databases
Real-Time Intelligence
Spark Structured Streaming

Key Streaming Metrics

Events Ingested

Measures throughput.

Example:

50,000 events per minute

Ingestion Latency

Measures delay between event creation and availability.

Lower latency generally indicates healthier streaming systems.

Failed Events

Tracks records that could not be processed.

Backlog Size

Measures unprocessed events waiting for ingestion.

Large backlogs may indicate:

Capacity issues
Slow downstream processing
Configuration problems

Monitoring Eventstreams

Eventstreams provide operational monitoring capabilities.

You can monitor:

Incoming event volume
Processing status
Transformation performance
Output destinations

Common issues include:

Source connectivity failures
Event schema mismatches
Destination write failures

Monitoring Mirroring

Mirroring continuously replicates source data into Fabric.

Monitoring focuses on:

Replication status
Synchronization delays
Replication failures
Data freshness

Important concepts include:

Replication Latency

Time between source changes and destination availability.

Synchronization Health

Indicates whether replication remains current.

Monitoring Data Completeness

Successful execution does not always mean successful ingestion.

Data engineers should validate:

Expected row counts
File counts
Event counts
Record completeness

Example:

A pipeline succeeds but only loads 70% of expected records.

Technical execution succeeded, but business requirements were not met.

Common Validation Checks

Row Count Validation

Compare source and destination record counts.

File Validation

Verify expected files arrived.

Timestamp Validation

Confirm recent records are present.

Duplicate Detection

Identify accidental duplicate ingestion.

Monitoring Data Quality During Ingestion

Data quality monitoring often includes:

Null value detection
Invalid data type identification
Duplicate record detection
Referential integrity checks

Monitoring quality issues early prevents downstream reporting problems.

Alerts and Notifications

Monitoring becomes significantly more effective when alerts are configured.

Common alert scenarios include:

Pipeline failures
Dataflow refresh failures
Long-running jobs
Excessive ingestion latency
Capacity utilization thresholds

Alerts allow engineers to respond before business users notice issues.

Troubleshooting Ingestion Failures

A common troubleshooting workflow includes:

Step 1

Review Monitoring Hub status.

Step 2

Identify failed workload.

Step 3

Inspect detailed error message.

Step 4

Validate source connectivity.

Step 5

Verify credentials and permissions.

Step 6

Review recent schema changes.

Step 7

Rerun ingestion process if appropriate.

Best Practices

Establish Baselines

Track normal:

Runtime duration
Throughput
Latency
Data volume

Baseline measurements make anomalies easier to identify.

Monitor Data Quality

Do not rely solely on execution success.

Validate:

Completeness
Accuracy
Timeliness

Use Alerts

Configure proactive notifications for:

Failures
Delays
Performance degradation

Retain Historical Monitoring Data

Historical execution information helps identify:

Trends
Capacity growth
Recurring failures

Investigate Long-Running Jobs

Increasing execution times often indicate:

Growing data volumes
Inefficient queries
Capacity limitations

DP-700 Exam Tips

Know the Monitoring Hub

The Monitoring Hub is the primary location for monitoring Fabric workloads.

Understand Pipeline Monitoring

Be familiar with:

Run history
Activity runs
Error messages
Execution duration

Understand Streaming Metrics

Know the importance of:

Throughput
Latency
Backlogs
Failed events

Monitor More Than Success Status

Successful execution does not guarantee complete or accurate data ingestion.

Understand Data Validation

Exam questions often focus on verifying:

Row counts
Data completeness
Freshness
Data quality

Practice Exam Questions

Question 1

Which Microsoft Fabric feature serves as the central location for monitoring pipelines, notebooks, Spark jobs, and dataflows?

A. Data Activator

B. OneLake Explorer

C. Monitoring Hub

D. Eventhouse

Answer: C

Explanation: The Monitoring Hub provides centralized monitoring across Fabric workloads and is the primary tool for reviewing execution history and failures.

Question 2

A pipeline execution completed successfully, but only half the expected records were loaded.

What should you verify first?

A. Workspace permissions

B. Data completeness and row counts

C. Capacity SKU

D. Sensitivity labels

Answer: B

Explanation: Successful execution does not guarantee successful business outcomes. Row count validation helps confirm complete ingestion.

Question 3

Which metric measures the delay between event creation and event availability in a streaming solution?

A. Throughput

B. Replication count

C. Ingestion latency

D. Refresh frequency

Answer: C

Explanation: Ingestion latency measures how quickly streaming data becomes available after being generated.

Question 4

Which issue is most likely if streaming event backlogs continue growing over time?

A. Processing cannot keep up with incoming events

B. Missing endorsement settings

C. Too many workspace roles

D. Excessive sensitivity labels

Answer: A

Explanation: Growing backlogs typically indicate that event processing is slower than event arrival rates.

Question 5

When troubleshooting a failed pipeline, what should typically be examined first?

A. Lakehouse shortcuts

B. Activity-level execution details

C. Workspace endorsements

D. Semantic model refresh schedules

Answer: B

Explanation: Activity-level details usually identify the exact source of a pipeline failure.

Question 6

Which metric is most useful for determining whether a Dataflow Gen2 refresh is becoming slower over time?

A. Sensitivity label

B. Number of workspaces

C. Refresh duration

D. Dataset owner

Answer: C

Explanation: Refresh duration directly measures execution performance and helps identify degradation trends.

Question 7

A data engineer wants to verify that every expected source file was loaded during ingestion.

Which validation approach should be used?

A. Capacity monitoring

B. File count validation

C. Role assignment review

D. Workspace auditing

Answer: B

Explanation: File count validation confirms that all expected files were ingested.

Question 8

Which Spark monitoring metric can help identify uneven partition distribution during ingestion?

A. Activity retry count

B. Replication latency

C. Refresh history

D. Data skew

Answer: D

Explanation: Data skew occurs when partitions contain significantly different amounts of data, creating processing bottlenecks.

Question 9

What is the primary purpose of configuring alerts for ingestion workloads?

A. To reduce storage costs

B. To automatically increase capacity

C. To proactively notify administrators of issues

D. To encrypt incoming data

Answer: C

Explanation: Alerts help identify failures, delays, and performance issues before they impact users.

Question 10

Which monitoring focus is most important for mirrored databases?

A. Report visual refresh time

B. Synchronization health and replication latency

C. Notebook parameter values

D. Semantic model relationships

Answer: B

Explanation: Mirroring depends on keeping source and destination systems synchronized, making replication latency and synchronization health critical monitoring metrics.

Go to the DP-700 Exam Prep Hub main page.

Data Development, DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Group and aggregate data (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Ingest and transform data (30–35%)
   --> Ingest and transform batch data
      --> Group and aggregate data

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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

Grouping and aggregating data are among the most common and important data transformation operations performed by data engineers. Organizations rarely analyze raw transactional data directly. Instead, they summarize, categorize, and aggregate data to create meaningful business metrics such as total sales, average order value, monthly revenue, customer counts, inventory levels, and operational KPIs.

In Microsoft Fabric, grouping and aggregation can be performed using several technologies, including:

SQL in Fabric Data Warehouses and Lakehouses
PySpark notebooks
Dataflows Gen2
KQL (Kusto Query Language)
Data pipelines as part of larger ETL/ELT processes

For the DP-700 exam, you should understand:

Why grouping and aggregation are important
When to aggregate data
How to implement aggregations using SQL, PySpark, KQL, and Dataflows Gen2
Common aggregation functions
Performance considerations
Aggregations in dimensional modeling and analytics solutions

Why Group and Aggregate Data?

Raw data often contains millions or billions of records.

For example:

OrderID	CustomerID	OrderDate	Amount
1001	C001	2026-01-01	250
1002	C001	2026-01-02	150
1003	C002	2026-01-02	300

Business users usually want summarized information such as:

CustomerID	TotalSales
C001	400
C002	300

This transformation is accomplished through grouping and aggregation.

Benefits include:

Faster analytics
Reduced storage requirements
Easier reporting
Improved dashboard performance
Simplified business intelligence models

Understanding Grouping

Grouping combines records that share common values.

Examples:

Group by:

Customer
Product
Region
Department
Date
Month
Year

Example:

Region	Sales
East	100
East	200
West	300

Grouped by Region:

Region	TotalSales
East	300
West	300

The GROUP BY operation creates logical categories before aggregation calculations occur.

Common Aggregation Functions

Data engineers should be familiar with the most common aggregation functions.

SUM()

Calculates totals.

Example:

			
SELECT Region,
       SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region;

Result:

Region	TotalSales
East	500000
West	750000

COUNT()

Counts rows.

			
SELECT Region,
       COUNT(*) AS OrderCount
FROM Sales
GROUP BY Region;

Used for:

Number of customers
Number of transactions
Number of products

AVG()

Calculates averages.

			
SELECT ProductCategory,
       AVG(SalesAmount) AS AverageSale
FROM Sales
GROUP BY ProductCategory;

Used for:

Average order value
Average response time
Average inventory level

MIN()

Returns the smallest value.

			
SELECT MIN(OrderDate)
FROM Orders;

Used for:

Earliest order
Lowest temperature
Minimum cost

MAX()

Returns the largest value.

			
SELECT MAX(OrderDate)
FROM Orders;

Used for:

Latest transaction
Highest sales amount
Maximum inventory quantity

Grouping and Aggregation Using SQL

SQL is the most common approach for aggregation in Fabric.

Example:

			
SELECT
    ProductCategory,
    SUM(SalesAmount) AS TotalSales,
    COUNT(*) AS OrderCount,
    AVG(SalesAmount) AS AverageSale
FROM Sales
GROUP BY ProductCategory;

		

This query:

Groups records by category
Calculates total sales
Counts orders
Calculates average sales

Multi-Column Grouping

You can group by multiple columns.

Example:

			
SELECT
    Year,
    Region,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Year, Region;

		

Result:

Year	Region	TotalSales
2025	East	500000
2025	West	700000
2026	East	550000

This provides more granular analysis.

Filtering Aggregated Results with HAVING

WHERE filters rows before aggregation.

HAVING filters after aggregation.

Example:

			
SELECT Region,
       SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region
HAVING SUM(SalesAmount) > 1000000;

		

Only regions exceeding $1 million in sales are returned.

Aggregation Using PySpark

PySpark is commonly used for large-scale aggregation operations in Lakehouses.

Example:

			
from pyspark.sql.functions import sum
sales_df.groupBy("Region") \
        .agg(sum("SalesAmount").alias("TotalSales")) \
        .show()

Result:

Region	TotalSales
East	500000
West	750000

Multiple Aggregations in PySpark

			
from pyspark.sql.functions import sum, avg, count
sales_df.groupBy("Region").agg(
    sum("SalesAmount").alias("TotalSales"),
    avg("SalesAmount").alias("AvgSales"),
    count("*").alias("OrderCount")
)

		

This performs several calculations in a single operation.

Aggregation Using KQL

KQL is commonly used in Real-Time Intelligence workloads.

Example:

			
Sales
| summarize TotalSales=sum(SalesAmount)
    by Region

Result:

Region	TotalSales
East	500000
West	750000

Multiple Aggregations in KQL

			
Sales
| summarize
    TotalSales=sum(SalesAmount),
    AvgSales=avg(SalesAmount),
    OrderCount=count()
    by Region

		

This pattern is common in real-time analytics.

Aggregation in Dataflows Gen2

Dataflows Gen2 provides a low-code interface.

Using the Group By transformation, users can:

Sum values
Count rows
Calculate averages
Find minimum values
Find maximum values

Typical steps:

Connect to source
Select Group By
Choose grouping columns
Define aggregation functions
Load results

This approach is useful for citizen developers and low-code ETL scenarios.

Aggregation in Dimensional Models

Aggregations are commonly used before loading data into fact and dimension tables.

Example:

Raw transactions:

OrderID	Customer	Amount
1	A	100
2	A	200
3	B	300

Aggregated customer sales:

Customer	TotalSales
A	300
B	300

This summary table can support reporting and dashboards.

Fact Table Aggregations

Fact tables often store:

Transaction-level facts
Daily summaries
Monthly summaries

Examples:

Transaction Fact

OrderID	Amount
1001	50

Daily Aggregate Fact

Date	TotalSales
2026-01-01	50000

Aggregated fact tables improve query performance.

Window Aggregations vs Group Aggregations

Data engineers should understand the difference.

Group Aggregation

Returns one row per group.

			
SELECT Region,
       SUM(SalesAmount)
FROM Sales
GROUP BY Region;

Window Aggregation

Preserves detail rows.

			
SELECT
    OrderID,
    Region,
    SalesAmount,
    SUM(SalesAmount)
        OVER(PARTITION BY Region)
        AS RegionTotal
FROM Sales;

		

Useful for:

Running totals
Rankings
Percentages
Advanced analytics

Performance Considerations

Grouping and aggregation can be expensive.

Best practices include:

Filter Early

Reduce data before aggregation.

WHERE OrderDate >= '2026-01-01'

Aggregate Close to the Source

Avoid moving unnecessary detailed records.

Use Partitioning

Partitioning helps Spark process data efficiently.

Examples:

Date
Region
Customer segment

Use Delta Tables

Delta tables improve performance through:

Data skipping
File optimization
Efficient query execution

Avoid Excessive Cardinality

Grouping on highly unique columns can reduce efficiency.

Bad example:

GROUP BY TransactionID

Good example:

GROUP BY Region

DP-700 Exam Tips

Remember the following:

GROUP BY creates logical groups before aggregation.
HAVING filters aggregated results.
SQL uses GROUP BY.
PySpark uses groupBy() and agg().
KQL uses summarize.
Dataflows Gen2 provides Group By transformations.
Aggregated fact tables improve reporting performance.
Window functions preserve detailed rows while performing calculations.
Aggregations are frequently used when preparing dimensional models.
Filtering before aggregation improves performance.

Practice Exam Questions

Question 1

A data engineer needs to calculate total sales by region in a Fabric Warehouse.

Which SQL function should be used?

A. AVG()

B. COUNT()

C. SUM()

D. MAX()

Correct Answer: C

Explanation: SUM() calculates the total of numeric values. AVG() calculates averages, COUNT() counts rows, and MAX() returns the largest value.

Question 2

A Fabric notebook must calculate the number of orders per customer.

Which aggregation function should be used?

A. COUNT()

B. AVG()

C. MIN()

D. MAX()

Correct Answer: A

Explanation: COUNT() returns the number of rows in each group, making it ideal for counting orders.

Question 3

You need to remove regions with total sales less than $500,000 after aggregation.

Which SQL clause should you use?

A. ORDER BY

B. WHERE

C. DISTINCT

D. HAVING

Correct Answer: D

Explanation: HAVING filters aggregated results after the GROUP BY operation is completed.

Question 4

Which KQL operator is primarily used for aggregation?

A. project

B. summarize

C. extend

D. join

Correct Answer: B

Explanation: The summarize operator performs grouping and aggregation in KQL.

Question 5

A Fabric Dataflow Gen2 developer wants to calculate average sales by product category.

Which transformation should be used?

A. Merge

B. Append

C. Group By

D. Split Column

Correct Answer: C

Explanation: The Group By transformation supports aggregation operations such as averages, sums, counts, minimums, and maximums.

Question 6

What is the primary purpose of a GROUP BY clause?

A. Sort rows

B. Remove duplicates

C. Filter rows

D. Create logical groups for aggregation

Correct Answer: D

Explanation: GROUP BY organizes rows into groups before aggregate calculations are performed.

Question 7

Which PySpark operation performs grouping before aggregation?

A. select()

B. filter()

C. groupBy()

D. orderBy()

Correct Answer: C

Explanation: groupBy() defines the grouping columns that will be used by aggregation functions.

Question 8

Which scenario is most appropriate for a window aggregation?

A. Total sales by region only

B. Average salary by department only

C. Customer counts by state only

D. Display each transaction along with the total sales for its region

Correct Answer: D

Explanation: Window functions preserve detail rows while calculating aggregates across a defined partition.

Question 9

A data engineer groups a dataset by TransactionID, where every TransactionID is unique.

What is the likely result?

A. Improved aggregation performance

B. Reduced cardinality

C. Limited performance benefits because each group contains one row

D. Automatic partition optimization

Correct Answer: C

Explanation: Grouping by a highly unique column creates many groups and often provides little analytical value.

Question 10

When preparing data for a dimensional model, why are aggregated tables often created?

A. To increase data duplication

B. To improve reporting and query performance

C. To eliminate dimension tables

D. To replace fact tables entirely

Correct Answer: B

Explanation: Pre-aggregated tables reduce the amount of data that must be processed during reporting, improving performance and user experience.

Go to the DP-700 Exam Prep Hub main page.

Data Development, DP-700, Microsoft Certification June 3, 2026

Denormalize data (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Ingest and transform data (30–35%)
   --> Ingest and transform batch data
      --> Denormalize data

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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

Denormalization is an important data engineering concept that appears frequently in modern analytics platforms, including Microsoft Fabric. While normalized data models are ideal for transactional systems, analytics workloads often benefit from denormalized structures that improve query performance, simplify reporting, and reduce the number of joins required during analysis.

For the DP-700 exam, you should understand:

What denormalization is
Why denormalization is used
When denormalization should be applied
How denormalization is implemented in Microsoft Fabric
The tradeoffs between normalized and denormalized models
How denormalization relates to Lakehouses, Warehouses, and dimensional modeling

What Is Denormalization?

Denormalization is the process of combining data from multiple related tables into fewer tables to reduce the need for joins during querying.

In a normalized model, information is separated into multiple related tables to eliminate redundancy.

Example normalized structure:

Customer Table

CustomerID	CustomerName
1	Contoso
2	Fabrikam

Product Table

ProductID	ProductName
10	Laptop
20	Monitor

Sales Table

SaleID	CustomerID	ProductID	Amount
1001	1	10	1200

To generate a report, multiple joins are required.

Denormalized Version

SaleID	CustomerName	ProductName	Amount
1001	Contoso	Laptop	1200

The descriptive attributes are stored directly in the fact record.

This eliminates the need for joins when querying.

Why Denormalize Data?

Analytics systems are optimized differently than transactional systems.

The primary goals of denormalization are:

Faster query performance
Reduced join complexity
Improved reporting efficiency
Simplified data models
Better user experience for analysts

In Microsoft Fabric, analytical workloads often prioritize read performance over minimizing storage consumption.

Normalization vs Denormalization

Characteristic	Normalized	Denormalized
Data redundancy	Low	Higher
Storage efficiency	Better	Lower
Query complexity	Higher	Lower
Join requirements	Many	Few
Reporting performance	Slower	Faster
Transaction systems	Preferred	Rarely used
Analytics systems	Sometimes	Common

Denormalization in Microsoft Fabric

Denormalization is commonly performed during:

Data ingestion
Data transformation
Data warehouse loading
Lakehouse processing
ETL/ELT workflows

Fabric engineers frequently use:

Dataflows Gen2
Notebooks (PySpark)
SQL transformations
Data Pipelines

to create denormalized analytical datasets.

Common Denormalization Techniques

Flattening Multiple Tables

The most common approach is combining related tables into a single analytical table.

Example:

Before

Customer

			
CustomerID
CustomerName
Region

Sales

			
SaleID
CustomerID
Amount

After

			
SaleID
CustomerID
CustomerName
Region
Amount

		

This reduces reporting complexity.

Creating Wide Tables

A wide table contains many descriptive columns.

Example:

OrderID	CustomerName	Region	ProductName	Category	Salesperson	Amount

Analysts can query one table instead of several.

Materializing Joins

Rather than joining tables every time a report executes, the join is performed once during data loading.

Example:

			
SELECT
    s.SaleID,
    c.CustomerName,
    p.ProductName,
    s.Amount
FROM Sales s
JOIN Customer c
    ON s.CustomerID = c.CustomerID
JOIN Product p
    ON s.ProductID = p.ProductID;

		

The resulting dataset is stored as a new table.

Aggregated Tables

Sometimes denormalization includes pre-computing aggregates.

Example:

			
SELECT
    Region,
    SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region;

		

This produces a summarized table optimized for reporting.

Denormalization and Star Schemas

A common DP-700 topic is dimensional modeling.

Star schemas are partially denormalized models.

Example:

Fact Table

FactSales

Dimension Tables

DimCustomer

DimProduct

DimDate

Star schemas intentionally denormalize dimension information while maintaining separate fact and dimension tables.

This approach balances:

Performance
Simplicity
Storage efficiency

Denormalization in Lakehouses

Within Fabric Lakehouses, denormalization is often used to create:

Curated Silver tables
Gold analytical tables
Reporting datasets

Typical flow:

Bronze Layer

Raw source data

Silver Layer

Cleaned and standardized data

Gold Layer

Denormalized business-ready tables

Example:

			
Bronze Customer
Bronze Orders
Bronze Products
↓ Transform
Gold SalesAnalytics

		

The Gold table contains business-friendly denormalized data.

Denormalization Using PySpark

Example:

			
sales_df = spark.table("Sales")
customer_df = spark.table("Customer")
result_df = sales_df.join(
    customer_df,
    sales_df.CustomerID == customer_df.CustomerID
)
result_df.write.mode("overwrite").saveAsTable("SalesAnalytics")

		

The resulting table is denormalized.

Denormalization Using SQL

Example:

			
CREATE TABLE SalesAnalytics AS
SELECT
    s.SaleID,
    c.CustomerName,
    p.ProductName,
    s.Amount
FROM Sales s
JOIN Customer c
    ON s.CustomerID = c.CustomerID
JOIN Product p
    ON s.ProductID = p.ProductID;

		

This is one of the most common techniques used in Fabric Warehouses.

Benefits of Denormalization

Faster Query Performance

Fewer joins mean faster report execution.

Simpler Queries

Analysts write simpler SQL.

Instead of:

			
SELECT ...
FROM FactSales
JOIN DimCustomer
JOIN DimProduct
JOIN DimDate

		

They may query a single table.

Improved Reporting

Power BI reports often perform better against denormalized structures.

Better User Experience

Business users can easily understand a flatter model.

Drawbacks of Denormalization

Increased Storage

Data duplication increases storage consumption.

More Complex Updates

Changes may need to be applied in multiple locations.

Potential Data Inconsistency

Improper ETL processes can create mismatched values.

Longer Load Times

More transformations occur during ingestion.

When to Denormalize

Denormalization is typically appropriate when:

✅ Data is primarily read rather than updated

✅ Reporting performance is important

✅ Large numbers of joins slow queries

✅ Business users require simplified models

✅ Building Gold-layer analytical datasets

When Not to Denormalize

Avoid denormalization when:

❌ Supporting transactional applications

❌ Frequent updates occur

❌ Data consistency is critical

❌ Storage costs are a major concern

❌ Redundancy must be minimized

DP-700 Exam Tips

Remember the Main Goal

Denormalization primarily improves analytical query performance.

Understand Lakehouse Layers

Expect questions about:

Bronze = Raw
Silver = Refined
Gold = Business-ready and often denormalized

Know the Tradeoff

The exam often tests:

Better performance ↔ More data redundancy

Star Schemas Matter

Star schemas are intentionally denormalized and are frequently used in analytics solutions.

SQL and PySpark Are Common Tools

Both SQL and PySpark are commonly used to implement denormalization transformations in Fabric.

Practice Exam Questions

Question 1

What is the primary purpose of denormalization?

A. Minimize storage usage

B. Eliminate duplicate data completely

C. Increase referential integrity

D. Reduce query complexity and improve performance

Answer: D

Explanation: Denormalization reduces the number of joins required and improves query performance for analytical workloads.

Question 2

Which characteristic is typically associated with a denormalized model?

A. Reduced redundancy

B. Increased data redundancy

C. More foreign keys

D. Higher normalization levels

Answer: B

Explanation: Denormalized models intentionally duplicate some data to improve read performance.

Question 3

Which Fabric layer commonly contains denormalized business-ready datasets?

A. Bronze

B. Landing

C. Gold

D. Raw

Answer: C

Explanation: Gold-layer datasets are typically optimized for reporting and analytics and are often denormalized.

Question 4

A report currently joins ten tables to answer business questions. What is the primary benefit of denormalizing the model?

A. Reduced ETL processing

B. Reduced storage requirements

C. Increased normalization

D. Faster query execution

Answer: D

Explanation: Reducing the number of joins generally improves query performance.

Question 5

Which operation is most commonly used when denormalizing data?

A. Joining related tables

B. Index rebuilding

C. Partitioning

D. Creating primary keys

Answer: A

Explanation: Denormalization commonly involves combining related tables through joins.

Question 6

Which workload benefits most from denormalized structures?

A. Transaction processing

B. Online order entry

C. Inventory updates

D. Business intelligence reporting

Answer: D

Explanation: Reporting and analytics workloads benefit from simplified structures and faster query performance.

Question 7

What is a potential disadvantage of denormalization?

A. Slower reads

B. Increased storage consumption

C. Reduced reporting performance

D. Fewer columns available

Answer: B

Explanation: Because data may be duplicated, denormalized models often consume more storage.

Question 8

A Fabric engineer creates a table that combines customer, product, and sales information into a single reporting table. What technique is being used?

A. Data partitioning

B. Schema evolution

C. Streaming ingestion

D. Denormalization

Answer: D

Explanation: Combining multiple related tables into a single analytical table is a classic denormalization technique.

Question 9

Which schema design is considered partially denormalized and commonly used in analytics?

A. Snowflake schema

B. Third Normal Form

C. Star schema

D. Operational schema

Answer: C

Explanation: Star schemas intentionally denormalize dimension data to improve analytical performance.

Question 10

Why might a data engineer choose a denormalized Gold-layer table instead of querying multiple Silver-layer tables?

A. To simplify reporting and improve performance

B. To eliminate all ETL processing

C. To reduce data quality requirements

D. To increase normalization

Answer: A

Explanation: Denormalized Gold-layer tables provide a business-friendly structure that supports faster reporting and easier querying.

Go to the DP-700 Exam Prep Hub main page.

Data Development, DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Choose an appropriate data store (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Ingest and transform data (30–35%)
   --> Ingest and transform batch data
      --> Choose an appropriate data store

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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

One of the most important responsibilities of a Microsoft Fabric Data Engineer is selecting the appropriate data store for a given workload. The choice of data store directly affects performance, scalability, maintainability, security, data modeling approaches, and overall solution cost.

Microsoft Fabric provides multiple storage and analytics options, each optimized for specific use cases. The DP-700 exam expects candidates to understand when and why to use each storage option, especially in batch ingestion and transformation scenarios.

A common exam theme is evaluating business requirements and selecting the most appropriate storage technology among:

OneLake
Lakehouse
Data Warehouse
KQL Database
Eventhouse
Mirrored Databases
Delta Tables
Shortcuts
External Storage Sources

Understanding the strengths and limitations of each option is critical for success on the exam.

Understanding Data Stores in Microsoft Fabric

A data store is a repository where data is persisted and made available for processing, analytics, reporting, or machine learning.

Selecting a data store depends on factors such as:

Data structure
Data volume
Query patterns
Processing requirements
Latency requirements
User skill sets
Governance needs

A poor storage choice can result in:

Slow query performance
Increased complexity
Higher costs
Difficult maintenance

OneLake: The Foundation of Fabric

What is OneLake?

OneLake is the unified logical data lake for Microsoft Fabric.

It serves as:

A single storage layer
A centralized data repository
A common storage foundation for all Fabric workloads

Key characteristics:

Tenant-wide storage
Based on Azure Data Lake Storage Gen2 architecture
Supports open formats
Eliminates data silos

Benefits

Single copy of data
Unified governance
Centralized security
Reduced duplication

Exam Tip

OneLake itself is not typically selected as the analytics store. Instead, workloads such as Lakehouses and Warehouses are built on top of OneLake.

Lakehouse

What is a Lakehouse?

A Lakehouse combines features of:

Data lakes
Data warehouses

Lakehouses support:

Structured data
Semi-structured data
Unstructured data

while maintaining:

Open file formats
Delta Lake support
Spark processing

When to Choose a Lakehouse

Choose a Lakehouse when:

Data engineering workloads dominate
Spark processing is required
Data science workloads are expected
Large volumes of raw data must be stored
Data arrives in multiple formats

Examples:

IoT data
Clickstream data
Log files
Data science projects

Lakehouse Advantages

Open Format Storage

Uses Delta Parquet files.

Spark Integration

Optimized for notebooks and Spark jobs.

Data Science Friendly

Supports machine learning workflows.

Multi-format Data

Handles structured and unstructured data.

Lakehouse Limitations

Less familiar to traditional SQL developers
Some reporting scenarios may perform better in a warehouse

Data Warehouse

What is a Fabric Warehouse?

A Fabric Warehouse is a fully managed relational analytics platform optimized for SQL workloads.

Characteristics:

Relational tables
T-SQL support
ACID transactions
Optimized query engine

When to Choose a Warehouse

Choose a Warehouse when:

Business users primarily use SQL
Dimensional models are required
Reporting workloads dominate
Data is highly structured

Examples:

Enterprise reporting
Financial reporting
Sales analytics
Executive dashboards

Warehouse Advantages

Familiar SQL Experience

Supports T-SQL.

Strong BI Integration

Works seamlessly with reporting tools.

Relational Modeling

Supports star and snowflake schemas.

High Query Performance

Optimized for analytical queries.

Warehouse Limitations

Less flexible for unstructured data
Not ideal for large-scale machine learning workloads

Lakehouse vs Warehouse

Feature	Lakehouse	Warehouse
Storage Format	Delta Files	Relational Tables
Primary Engine	Spark	SQL
Structured Data	Yes	Yes
Semi-Structured Data	Yes	Limited
Unstructured Data	Yes	No
Machine Learning	Excellent	Limited
BI Reporting	Good	Excellent
Data Science	Excellent	Limited

Delta Tables

What Are Delta Tables?

Delta tables are transactional storage structures built on Delta Lake technology.

Benefits include:

ACID compliance
Schema enforcement
Schema evolution
Time travel
Reliable batch processing

When to Use Delta Tables

Use Delta tables when:

Reliable batch ingestion is required
Incremental loads are needed
Historical versions must be maintained
Data quality controls are important

Exam Tip

Most Lakehouse production data should be stored as Delta tables.

KQL Database

What is a KQL Database?

KQL databases are optimized for:

Log analytics
Telemetry
Time-series analysis
Streaming workloads

They use:

Kusto Query Language (KQL)

instead of SQL.

When to Choose KQL Databases

Choose KQL databases for:

Application logs
Monitoring data
Telemetry
Operational analytics

Examples:

Server monitoring
Manufacturing sensors
Website activity tracking

Advantages

Extremely fast ingestion
High-performance time-series analysis
Optimized aggregations

Limitations

Not designed for dimensional modeling
Less suitable for traditional reporting

Eventhouse

What is Eventhouse?

Eventhouse is a Real-Time Intelligence component designed for large-scale event analytics.

Eventhouse supports:

Streaming ingestion
KQL analytics
High-volume telemetry

When to Use Eventhouse

Choose Eventhouse when:

Massive event volumes exist
Real-time analytics are required
Streaming data dominates

Mirrored Databases

What Are Mirrored Databases?

Mirroring replicates operational database data into Fabric.

Supported sources include:

Azure SQL Database
Azure SQL Managed Instance
SQL Server (supported scenarios)
Other supported databases

When to Choose Mirroring

Choose mirroring when:

Near real-time synchronization is required
Minimal ETL is desired
Operational systems must remain the source of truth

Advantages

Reduced data movement
Simplified architecture
Near real-time availability

Shortcuts

What Are Shortcuts?

Shortcuts provide virtual access to data stored elsewhere.

The data remains in its original location.

Examples:

Azure Data Lake Storage Gen2
Amazon S3
Other OneLake locations

When to Use Shortcuts

Choose shortcuts when:

Data duplication must be avoided
Multiple teams share datasets
Existing storage investments should be preserved

Benefits

No copying
Reduced storage costs
Single source of truth

Simplified synchronization

Lakehouse

Decision Framework

Ask the following questions:

Is the workload primarily SQL analytics?

→ Warehouse

Is Spark required?

→ Lakehouse

Is machine learning required?

→ Lakehouse

Is the data mostly telemetry or logs?

→ KQL Database

Is the data streaming continuously?

→ Eventhouse or KQL Database

Must external data remain in place?

→ Shortcut

Must operational databases replicate into Fabric?

→ Mirrored Database

Best Practices

Store Curated Data as Delta Tables

Provides reliability and transactional consistency.

Avoid Unnecessary Copies

Use shortcuts when appropriate.

Match Store to Workload

Do not force all workloads into one store.

Separate Raw and Curated Layers

Use Medallion Architecture:

Bronze
Silver
Gold

Use Warehouses for Reporting

Leverage optimized SQL engines.

Use Lakehouses for Engineering

Take advantage of Spark and Delta Lake.

DP-700 Exam Focus Areas

You should understand:

✓ OneLake architecture

✓ Lakehouse capabilities

✓ Warehouse capabilities

✓ Delta tables

✓ KQL databases

✓ Eventhouse

✓ Mirrored databases

✓ Shortcuts

✓ SQL vs Spark workloads

✓ Reporting vs data science workloads

✓ Structured vs semi-structured data

✓ Batch vs streaming storage choices

✓ Common architecture decision scenarios

Practice Exam Questions

Question 1

A company needs to store structured, semi-structured, and unstructured data while enabling Spark notebook processing. Which data store should be selected?

A. KQL Database

B. Warehouse

C. Lakehouse

D. Eventhouse

Answer: C

Explanation

Lakehouses support multiple data formats and integrate natively with Spark, making them ideal for data engineering and data science workloads.

Which feature is provided by Delta tables?

A. Automatic dashboard creation

B. ACID transaction support

C. Real-time alerting

D. Event routing

Answer: B

Explanation

Delta tables provide ACID transactions, schema enforcement, and reliable data processing.

Question 6

A business intelligence team primarily writes T-SQL queries and builds dimensional models.

Which storage option should they use?

A. Eventhouse

B. KQL Database

C. Warehouse

D. Lakehouse

Answer: C

Explanation

Warehouses are optimized for SQL analytics and dimensional modeling.

Question 7

Question 10

A solution requires high-volume real-time event analytics with streaming ingestion.

Which Fabric component is most appropriate?

A. Warehouse

B. Mirrored Database

C. Delta Table

D. Eventhouse

Answer: D

Explanation

Eventhouse is designed specifically for large-scale streaming analytics and real-time intelligence scenarios.

Exam Tip

For DP-700, many questions can be answered by remembering this mapping:

Requirement	Best Choice
Spark, ML, flexible data formats	Lakehouse
SQL analytics and reporting	Warehouse
Telemetry and time-series data	KQL Database
Massive real-time event analytics	Eventhouse
External data without copying	Shortcut
Near real-time operational replication	Mirrored Database
Unified Fabric storage layer	OneLake

A common exam pattern is to present multiple technically possible options. Focus on selecting the option that is optimized for the workload, not merely one that could work.

Go to the DP-700 Exam Prep Hub main page.