Author: thedatacommunity

Databases, DP-800, Microsoft Certification

Design and Implement SEQUENCES (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and Implement SEQUENCES

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

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

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

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

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

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

What Is a SEQUENCE?

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

Unlike an IDENTITY column:

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

The database maintains the current value of the sequence.

Common Use Cases

SEQUENCES are commonly used for:

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

SEQUENCE vs. IDENTITY

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

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

Creating a SEQUENCE

Basic syntax:

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

This sequence:

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

Using NEXT VALUE FOR

Values are generated using the NEXT VALUE FOR function.

Example:

SELECT NEXT VALUE FOR dbo.OrderSequence;

Output:

1

The next execution returns:

2

Then:

3

Each call advances the sequence.

Using a SEQUENCE During INSERT

Example:

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

The generated sequence value becomes the OrderID.

Sharing a SEQUENCE Across Multiple Tables

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

Example:

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

Both tables generate identifiers from the same sequence.

This guarantees unique values across both tables.

Choosing the Data Type

Supported numeric types include:

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

Example:

CREATE SEQUENCE dbo.InvoiceSequence
AS BIGINT;

Choose a type large enough for expected future growth.

START WITH

The START WITH clause specifies the first value.

Example:

CREATE SEQUENCE dbo.InvoiceSequence
AS INT
START WITH 1000;

Generated values:

1000
1001
1002

INCREMENT BY

Defines how much the sequence changes.

Example:

INCREMENT BY 10

Generated values:

10
20
30
40

Negative increments are also supported.

Example:

INCREMENT BY -1

Produces:

100
99
98
97

MINVALUE and MAXVALUE

A sequence can define minimum and maximum values.

Example:

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

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

CYCLE Option

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

Example:

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

Generated values:

1
2
3
4
5
1
2

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

Use CYCLE only when reused values are acceptable.

NO CYCLE

NO CYCLE is the default behavior.

Example:

CREATE SEQUENCE dbo.OrderSequence
AS INT
NO CYCLE;

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

This is appropriate for identifiers that must remain unique.

CACHE Option

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

Example:

CREATE SEQUENCE dbo.OrderSequence
AS INT
CACHE 100;

Benefits:

  • Fewer disk writes
  • Higher throughput
  • Better scalability

Trade-off:

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

NO CACHE

Disables sequence caching.

Example:

NO CACHE

Benefits:

  • Reduces gaps caused by unexpected shutdowns

Trade-offs:

  • Slightly slower performance
  • Increased metadata updates

Restarting a SEQUENCE

A sequence can be restarted.

Example:

ALTER SEQUENCE dbo.OrderSequence
RESTART WITH 5000;

The next generated value will be 5000.

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

Altering a SEQUENCE

Existing sequences can be modified.

Example:

ALTER SEQUENCE dbo.OrderSequence
INCREMENT BY 5;

Future values increase by 5.

Dropping a SEQUENCE

Example:

DROP SEQUENCE dbo.OrderSequence;

This removes the sequence object from the database.

Obtaining Multiple Sequence Values

Applications can retrieve sequence values before performing inserts.

Example:

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

This is useful when:

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

Sequence Gaps

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

Gaps may occur because of:

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

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

Performance Considerations

SEQUENCES generally perform very well.

Performance is improved by:

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

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

SEQUENCES in Distributed Applications

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

Examples include:

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

Applications can reserve identifiers before inserting data.

AI-Enabled Database Scenarios

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

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

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

Best Practices

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

Common Exam Tips

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

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

Practice Exam Questions

Question 1

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

A. A DEFAULT constraint

B. An IDENTITY column

C. A computed column

D. A SEQUENCE

Answer: D

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

Question 2

Which statement best describes a SEQUENCE object?

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

B. It can only generate BIGINT values.

C. It automatically creates a clustered index.

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

Answer: D

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

Question 3

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

A. NEXT IDENTITY

B. GET NEXT

C. NEXT VALUE FOR

D. CURRENT VALUE

Answer: C

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

Question 4

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

A. Because SEQUENCES cannot contain gaps.

B. Because a SEQUENCE automatically enforces referential integrity.

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

D. Because SEQUENCES automatically create foreign keys.

Answer: C

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

Question 5

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

A. It guarantees gap-free numbering.

B. It improves performance by reducing metadata updates.

C. It automatically encrypts sequence values.

D. It prevents transaction rollbacks.

Answer: B

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

Question 6

Which statement about sequence values is correct?

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

B. Sequence values are always consecutive with no gaps.

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

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

Answer: C

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

Question 7

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

A. SQL Server raises an error.

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

C. The sequence becomes read-only.

D. SQL Server automatically increases the maximum value.

Answer: B

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

Question 8

Which statement about the NO CYCLE option is correct?

A. It causes sequence values to restart automatically.

B. It caches all generated values.

C. It allows duplicate sequence values.

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

Answer: D

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

Question 9

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

A. Automatically maintaining historical versions of rows

B. Enforcing referential integrity

C. Generating invoice numbers shared across multiple applications

D. Validating JSON documents

Answer: C

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

Question 10

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

A. The PRIMARY KEY constraint removed duplicate values.

B. Transaction rollbacks deleted the missing values.

C. The sequence automatically renumbered existing rows.

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

Answer: D

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

Go to the DP-800 Exam Prep Hub main page

Data Development, Databases, DP-800, Microsoft Certification

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:

ConstraintPurpose
PRIMARY KEYUniquely identifies each row
FOREIGN KEYMaintains relationships between tables
UNIQUEPrevents duplicate values
CHECKRestricts acceptable values
DEFAULTAutomatically 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.

ConstraintIndex
Enforces business rulesImproves query performance
Maintains data integritySpeeds data retrieval
May automatically create an indexDoes 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

Databases, DP-800, Microsoft Certification

Design and implement JSON columns and indexes (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and implement JSON columns and indexes

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

JSON (JavaScript Object Notation) has become one of the most widely used formats for exchanging data between applications, web services, APIs, cloud platforms, and AI solutions. Modern database applications frequently need to store, query, validate, and index JSON documents alongside traditional relational data.

SQL Server and Azure SQL Database provide extensive built-in support for working with JSON, allowing developers to:

  • Store JSON documents
  • Validate JSON data
  • Query individual properties
  • Modify JSON documents
  • Convert relational data to JSON
  • Convert JSON into relational tables
  • Improve performance by indexing JSON values

For the DP-800: Developing AI-Enabled Database Solutions certification exam, understanding how JSON is stored, queried, and indexed is an important skill because many AI-enabled applications exchange information using JSON.

Why Use JSON?

JSON offers a flexible way to store semi-structured data that doesn’t fit neatly into fixed relational tables.

Common scenarios include:

  • REST API payloads
  • Application configuration
  • Product catalogs
  • Customer preferences
  • Event logs
  • IoT telemetry
  • AI prompts and responses
  • Chat conversation history
  • Metadata storage

Rather than creating dozens of optional columns, JSON allows variable attributes to be stored in a single column.

JSON Support in SQL Server

Unlike some database systems, SQL Server stores JSON as text rather than as a dedicated JSON data type.

Typically, JSON documents are stored in:

  • NVARCHAR(MAX)
  • NVARCHAR(n)

Example:

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerInfo NVARCHAR(MAX)
);

A row might contain:

{
  "CustomerID": 125,
  "Name": "John Smith",
  "City": "Seattle",
  "LoyaltyLevel": "Gold"
}

Although stored as text, SQL Server provides built-in functions that understand JSON syntax.

Validating JSON

Before using JSON data, developers should ensure it contains valid JSON.

SQL Server provides the ISJSON() function.

Example:

SELECT ISJSON(CustomerInfo)
FROM Orders;

Returns:

  • 1 = Valid JSON
  • 0 = Invalid JSON

Example using a CHECK constraint:

ALTER TABLE Orders
ADD CONSTRAINT CK_ValidJSON
CHECK (ISJSON(CustomerInfo) = 1);

This prevents invalid JSON from being stored.

Extracting Values with JSON_VALUE()

The JSON_VALUE() function extracts a scalar value from a JSON document.

Example:

SELECT
JSON_VALUE(CustomerInfo,'$.Name')
FROM Orders;

Result:

John Smith

Common uses include:

  • Filtering
  • Sorting
  • Displaying attributes
  • Reporting

Extracting Objects and Arrays with JSON_QUERY()

JSON_QUERY() retrieves an object or array rather than a scalar value.

Example JSON:

{
  "CustomerID": 25,
  "Orders":
  [
      {"OrderID":1},
      {"OrderID":2}
  ]
}

Query:

SELECT
JSON_QUERY(CustomerInfo,'$.Orders')
FROM Orders;

Returns the JSON array.

Updating JSON with JSON_MODIFY()

JSON_MODIFY() updates properties without replacing the entire document.

Example:

UPDATE Orders
SET CustomerInfo =
JSON_MODIFY(CustomerInfo,
'$.City',
'Chicago');

Useful when applications maintain customer preferences or AI-generated metadata.

Parsing JSON into Rows with OPENJSON()

OPENJSON converts JSON into relational rows.

Example:

SELECT *
FROM OPENJSON(@json);

Example JSON:

{
 "Name":"Alice",
 "City":"Boston"
}

Returns:

KeyValue
NameAlice
CityBoston

Using WITH to Define a Schema

OPENJSON becomes much more useful when mapping JSON to columns.

Example:

SELECT *
FROM OPENJSON(@json)
WITH
(
    Name NVARCHAR(100),
    City NVARCHAR(100),
    Age INT
);

This converts JSON directly into relational columns.

Producing JSON with FOR JSON

SQL Server can return query results as JSON.

Example:

SELECT CustomerID,
       Name,
       City
FROM Customers
FOR JSON AUTO;

Output:

[
 {
   "CustomerID":1,
   "Name":"Alice",
   "City":"Boston"
 }
]

FOR JSON PATH

FOR JSON PATH provides greater control over the structure.

Example:

SELECT CustomerID,
       Name
FROM Customers
FOR JSON PATH;

Developers commonly use this when building REST APIs.

Common JSON Functions

FunctionPurpose
ISJSON()Validate JSON
JSON_VALUE()Return scalar value
JSON_QUERY()Return object or array
JSON_MODIFY()Update JSON
OPENJSON()Convert JSON into rows
FOR JSONProduce JSON output

These functions appear frequently in Microsoft documentation and certification objectives.

Why JSON Indexing Matters

JSON itself is stored as text.

Without indexing, SQL Server must scan every JSON document to locate requested values.

This becomes expensive for large tables.

Example:

SELECT *
FROM Orders
WHERE JSON_VALUE(CustomerInfo,'$.City')='Seattle';

Without indexing, SQL Server evaluates JSON_VALUE() for every row.

Indexing JSON Data

Because JSON is stored in NVARCHAR columns, SQL Server cannot directly create an index on an arbitrary JSON property.

Instead, developers create computed columns that expose JSON properties and then index those computed columns.

This is the recommended approach for SQL Server and Azure SQL Database and is an important DP-800 exam objective.

Creating a Computed Column

Example:

ALTER TABLE Orders
ADD CustomerCity AS
JSON_VALUE(CustomerInfo,'$.City');

SQL Server now treats CustomerCity as a regular column.

Creating an Index

Once the computed column exists, it can be indexed.

Example:

CREATE INDEX IX_Orders_CustomerCity
ON Orders(CustomerCity);

Queries filtering on CustomerCity can now use the index rather than scanning every JSON document.

Persisted Computed Columns

Computed columns may be marked PERSISTED.

Example:

ALTER TABLE Orders
ADD CustomerCity
AS JSON_VALUE(CustomerInfo,'$.City')
PERSISTED;

Benefits include:

  • Value stored physically
  • Faster reads
  • Reduced recalculation

Trade-offs include:

  • Increased storage
  • Slightly slower INSERT/UPDATE operations

JSON Path Expressions

SQL Server identifies JSON elements using path expressions.

Examples:

$.Name
$.Address.City
$.Orders[0]
$.Orders[1].Price

The dollar sign ($) represents the root of the JSON document.

Understanding JSON path syntax is essential for using JSON functions correctly.

Nested JSON

Example:

{
 "Customer":
 {
   "Name":"Alice",
   "Address":
   {
      "City":"Boston"
   }
 }
}

Extracting City:

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

Working with JSON Arrays

Example JSON:

{
 "Products":
 [
   {"ID":1},
   {"ID":2},
   {"ID":3}
 ]
}

Retrieve the array:

SELECT
JSON_QUERY(OrderInfo,'$.Products');

Convert to rows:

SELECT *
FROM OPENJSON
(
JSON_QUERY(OrderInfo,'$.Products')
);

JSON Design Considerations

JSON is ideal when:

  • Attributes vary significantly between rows.
  • Schema flexibility is required.
  • Data originates from APIs.
  • AI systems generate dynamic metadata.
  • Semi-structured information must be stored.

Relational tables are preferable when:

  • Strong relationships exist.
  • Frequent joins are required.
  • Referential integrity must be enforced.
  • Structured reporting dominates the workload.

Many production databases combine relational columns with JSON columns.

JSON and AI Applications

JSON is heavily used throughout AI-enabled database solutions.

Common examples include:

  • Storing AI prompt templates
  • Saving LLM responses
  • Recording chat history
  • Persisting vector search metadata
  • Storing application configuration
  • Capturing model parameters
  • Logging AI inference results

Because AI services frequently communicate using JSON, SQL Server’s JSON functionality is an important integration capability.

Performance Considerations

When working with JSON:

  • Validate incoming JSON using ISJSON().
  • Avoid repeatedly evaluating JSON_VALUE() in large queries.
  • Create computed columns for frequently queried properties.
  • Index computed columns used in WHERE, JOIN, or ORDER BY clauses.
  • Persist computed columns when read performance outweighs storage costs.
  • Avoid storing extremely large documents unless necessary.
  • Use OPENJSON() for efficient ingestion of structured JSON payloads.

Best Practices

  • Store only valid JSON documents.
  • Keep relational data relational whenever practical.
  • Use JSON for flexible or semi-structured attributes.
  • Use JSON_VALUE() for scalar values.
  • Use JSON_QUERY() for arrays and objects.
  • Use JSON_MODIFY() for updates.
  • Use OPENJSON() to transform JSON into relational rows.
  • Create computed columns for frequently searched JSON properties.
  • Index computed columns to improve query performance.
  • Test execution plans to verify indexes are being used.

Common Exam Tips

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

  • SQL Server stores JSON as NVARCHAR, not as a native JSON data type.
  • ISJSON() validates JSON syntax.
  • JSON_VALUE() returns a scalar value.
  • JSON_QUERY() returns an object or array.
  • JSON_MODIFY() updates JSON documents.
  • OPENJSON() converts JSON into relational rows.
  • FOR JSON AUTO and FOR JSON PATH generate JSON output from SQL queries.
  • JSON properties are typically indexed by exposing them through computed columns and creating indexes on those columns.
  • Persisted computed columns can improve read performance at the cost of additional storage and update overhead.

Practice Exam Questions

Question 1

A developer needs to verify that incoming API payloads contain valid JSON before storing them in a table. Which SQL Server function should be used?

A. JSON_QUERY()

B. OPENJSON()

C. JSON_VALUE()

D. ISJSON()

Answer: D

Explanation: ISJSON() validates whether a string contains properly formatted JSON. It returns 1 for valid JSON and 0 for invalid JSON, making it ideal for validation and CHECK constraints.

Question 2

A developer wants to retrieve the value of the City property from a JSON document stored in an NVARCHAR column. Which function should be used?

A. JSON_VALUE()

B. JSON_QUERY()

C. JSON_MODIFY()

D. OPENJSON()

Answer: A

Explanation: JSON_VALUE() extracts a single scalar value from a JSON document, such as a string, number, or Boolean.

Question 3

Which statement accurately describes how SQL Server stores JSON data?

A. SQL Server stores JSON in a dedicated JSON data type.

B. SQL Server stores JSON as XML internally.

C. SQL Server stores JSON as character data, typically in an NVARCHAR column.

D. SQL Server automatically converts JSON into relational columns.

Answer: C

Explanation: SQL Server does not provide a native JSON data type. JSON documents are stored as text, typically in NVARCHAR(MAX) or NVARCHAR(n) columns.

Question 4

A query frequently filters on the value returned by JSON_VALUE(CustomerInfo,'$.City'). What is the recommended way to improve query performance?

A. Create a clustered index on the JSON column.

B. Create a computed column using JSON_VALUE() and index the computed column.

C. Replace the JSON column with XML.

D. Create a foreign key on the JSON column.

Answer: B

Explanation: SQL Server cannot directly index an arbitrary JSON property. The recommended approach is to expose the property through a computed column and then create an index on that computed column.

Question 5

A developer needs to retrieve an entire JSON array from a document rather than a single scalar value. Which function should be used?

A. JSON_QUERY()

B. JSON_VALUE()

C. ISJSON()

D. JSON_MODIFY()

Answer: A

Explanation: JSON_QUERY() returns a JSON object or array, whereas JSON_VALUE() returns only scalar values.

Question 6

Which SQL Server feature converts relational query results into JSON output?

A. OPENJSON

B. JSON_VALUE

C. FOR JSON

D. JSON_MODIFY

Answer: C

Explanation: The FOR JSON clause formats SQL query results as JSON. Both AUTO and PATH modes are available.

Question 7

A developer receives a JSON document containing thousands of customer records and wants to transform it into relational rows for loading into SQL Server. Which function should be used?

A. JSON_QUERY()

B. JSON_MODIFY()

C. ISJSON()

D. OPENJSON()

Answer: D

Explanation: OPENJSON() parses JSON documents and converts them into rows and columns. It is commonly used for importing JSON data.

Question 8

What is the primary advantage of creating a persisted computed column based on a JSON property?

A. It automatically encrypts the JSON document.

B. It physically stores the computed value, reducing recalculation during queries.

C. It compresses the JSON document.

D. It converts JSON into XML.

Answer: B

Explanation: A persisted computed column stores its calculated value on disk, improving query performance by avoiding repeated evaluation of the expression.

Question 9

Which JSON path expression retrieves the City property nested inside an Address object?

A. $.City.Address

B. $.Address.City

C. $.Address[City]

D. $.City->Address

Answer: B

Explanation: JSON path expressions begin at the root ($) and navigate nested objects using dot notation, such as $.Address.City.

Question 10

Which scenario is the best candidate for storing information in a JSON column instead of creating many optional relational columns?

A. A highly normalized customer master table with strict referential integrity

B. A table containing only integer values

C. A fixed employee payroll table with mandatory attributes

D. A product catalog in which different product categories have different sets of optional attributes

Answer: D

Explanation: JSON is well suited for semi-structured or variable data, such as product attributes that differ across categories, avoiding the need for numerous nullable columns while maintaining flexibility.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification

Design and implement specialized tables, including in-memory, temporal, external, ledger, and graph tables (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 specialized tables, including in-memory, temporal, external, ledger, and graph

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

Modern SQL Server and Azure SQL Database environments provide several specialized table types designed to address specific business, performance, security, and analytical requirements. Rather than relying solely on traditional row-based tables, developers can use specialized tables to improve transaction throughput, maintain historical data automatically, access external data sources, provide tamper-evident records, or model complex relationships.

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

  • When to use each specialized table type
  • How each table is implemented
  • Their advantages and limitations
  • Common business scenarios
  • Performance considerations
  • Best practices

Understanding these table types is especially important because many modern AI-enabled applications require historical analysis, secure auditing, graph-based relationships, or access to large external datasets.

Overview of Specialized Tables

SQL Server and Azure SQL support several specialized table types:

Table TypePrimary Purpose
In-Memory TablesHigh-performance OLTP workloads
Temporal TablesAutomatic historical data tracking
External TablesQuery external data without importing it
Ledger TablesTamper-evident data and auditability
Graph TablesModel complex relationships between entities

Each solves a different architectural challenge.

In-Memory Tables

What Are In-Memory Tables?

In-memory tables are stored primarily in memory instead of traditional disk-based storage. They are part of the In-Memory OLTP feature in SQL Server and Azure SQL Database.

Unlike conventional tables, in-memory tables are optimized for extremely fast transaction processing.

They use:

  • Memory-optimized data structures
  • Lock-free algorithms
  • Latch-free architecture
  • Optimistic concurrency

These improvements reduce contention and dramatically improve throughput.

Benefits

Advantages include:

  • Extremely low latency
  • High transaction throughput
  • Reduced locking
  • Reduced blocking
  • Improved scalability
  • Better concurrency

Applications can often process several times more transactions compared to equivalent disk-based tables.

Typical Use Cases

Common scenarios include:

  • Financial trading
  • E-commerce transactions
  • Reservation systems
  • Gaming platforms
  • Real-time telemetry
  • IoT ingestion
  • Session state storage
  • High-volume order processing

Memory-Optimized Filegroup

Although data is stored in memory during operation, SQL Server still requires a memory-optimized filegroup for durability.

Example:

ALTER DATABASE SalesDB
ADD FILEGROUP InMemoryFG CONTAINS MEMORY_OPTIMIZED_DATA;

Creating a Memory-Optimized Table

Example:

CREATE TABLE Orders
(
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME2
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA
);

Durability Options

SCHEMA_AND_DATA

Both schema and data survive server restart.

Recommended for:

  • Production applications
  • Financial systems
  • Business transactions

SCHEMA_ONLY

Only the table definition persists.

Data disappears after restart.

Useful for:

  • Temporary data
  • Session caches
  • ETL staging
  • Intermediate processing

Memory-Optimized Indexes

Memory-optimized tables require indexes.

Supported types include:

  • Hash indexes
  • Nonclustered indexes

Hash indexes perform exceptionally well for equality searches.

Example:

INDEX IX_Order HASH(OrderID)
WITH (BUCKET_COUNT = 100000)

Limitations

Developers should know:

  • Not every SQL feature is supported.
  • Memory planning is important.
  • Some T-SQL functionality differs.
  • Requires appropriate database configuration.

Temporal Tables

What Are Temporal Tables?

Temporal tables automatically maintain a complete history of data changes.

Whenever a row is:

  • Updated
  • Deleted

SQL Server automatically copies the previous version into a history table.

This eliminates the need for custom audit triggers.

Components

A temporal table consists of:

  • Current table
  • History table
  • System versioning

SQL Server manages the history automatically.

Period Columns

Temporal tables include two hidden datetime columns:

ValidFrom
ValidTo

These define the period during which each row version was valid.

Creating a Temporal Table

Example:

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
    SYSTEM_VERSIONING = ON
);

Querying Historical Data

SQL provides temporal query options.

Example:

SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2025-01-01';

This retrieves the table exactly as it existed at the specified point in time.

Other options include:

  • FROM…TO
  • BETWEEN
  • CONTAINED IN
  • ALL

Benefits

Temporal tables provide:

  • Automatic history
  • Point-in-time recovery
  • Audit support
  • Trend analysis
  • Easier reporting

Typical Use Cases

Examples include:

  • Employee history
  • Customer profile changes
  • Insurance records
  • Compliance reporting
  • AI training using historical snapshots

External Tables

What Are External Tables?

External tables allow SQL Server or Azure SQL to query data stored outside the database without importing it.

The external data remains in its original location.

SQL accesses it through metadata.

Supported Data Sources

External tables can access:

  • Azure Data Lake Storage
  • Azure Blob Storage
  • Hadoop
  • Microsoft Fabric OneLake
  • SQL Server
  • Oracle (through supported virtualization technologies)
  • Parquet files
  • CSV files
  • Delta Lake (supported platforms)

PolyBase

SQL Server commonly uses PolyBase to query external data.

PolyBase allows T-SQL queries against external sources.

Example architecture:

SQL Server
      ↓
External Table
      ↓
Azure Data Lake

Advantages

Benefits include:

  • No data duplication
  • Large-scale analytics
  • Access data lakes
  • Simplified ETL
  • Lower storage costs

Common AI Scenarios

AI projects frequently use external tables to access:

  • Training datasets
  • Data lake files
  • Feature engineering data
  • Parquet datasets
  • Fabric Lakehouse data

Considerations

Performance depends on:

  • Network speed
  • External storage
  • Predicate pushdown
  • File formats
  • Data partitioning

Ledger Tables

What Are Ledger Tables?

Ledger tables provide tamper-evident records.

They use cryptographic hashing to detect unauthorized modifications.

Ledger technology helps organizations prove that data has not been altered.

Why Ledger Tables Matter

Industries with regulatory requirements often require immutable records.

Examples include:

  • Banking
  • Healthcare
  • Government
  • Supply chain
  • Legal systems

How They Work

Ledger tables automatically maintain:

  • Transaction history
  • Cryptographic digests
  • Verification metadata

Attempts to modify historical records become detectable.

Updatable Ledger Tables

These support:

  • INSERT
  • UPDATE
  • DELETE

While preserving historical versions.

Append-Only Ledger Tables

These allow:

  • INSERT

Only.

Rows cannot be updated or deleted.

Useful for:

  • Financial journals
  • Event logs
  • Blockchain-style records

Benefits

Ledger tables provide:

  • Data integrity
  • Nonrepudiation
  • Tamper evidence
  • Simplified auditing
  • Regulatory compliance

Typical Use Cases

Examples include:

  • Financial transactions
  • Audit logs
  • Medical records
  • Government systems
  • Digital contracts

Graph Tables

What Are Graph Tables?

Graph tables model relationships between entities.

Instead of relying solely on foreign keys, graph databases represent information as:

  • Nodes
  • Edges

This structure is ideal for highly connected data.

Node Tables

Node tables store entities.

Examples:

  • Customers
  • Products
  • Employees
  • Devices
  • Companies

Example:

CREATE TABLE Person
(
    ID INT,
    Name NVARCHAR(100)
)
AS NODE;

Edge Tables

Edge tables define relationships.

Example:

CREATE TABLE FriendOf
AS EDGE;

Possible relationships:

  • Friend of
  • Purchased
  • Works for
  • Located in
  • Connected to

Graph Queries

Graph queries use the MATCH clause.

Example:

SELECT *
FROM Person,
FriendOf,
Person
WHERE MATCH(Person-(FriendOf)->Person);

Advantages

Graph tables simplify:

  • Relationship traversal
  • Network analysis
  • Recommendation systems
  • Fraud detection
  • Organizational charts

AI Use Cases

Graph tables are increasingly valuable for AI because they model relationships that traditional relational databases handle less efficiently.

Examples include:

  • Knowledge graphs
  • Recommendation engines
  • Customer relationship analysis
  • Social networks
  • Supply chain analysis
  • Fraud detection

Choosing the Correct Specialized Table

RequirementBest Choice
Maximum OLTP performanceIn-Memory Table
Automatic historyTemporal Table
Access data lakeExternal Table
Tamper-evident recordsLedger Table
Relationship analysisGraph Table

Comparing Specialized Tables

FeatureIn-MemoryTemporalExternalLedgerGraph
High-speed transactions✔
Historical tracking✔
External data✔
Tamper detection✔
Relationship modeling✔
AI training support✔✔✔✔✔

Best Practices

In-Memory Tables

  • Use for high-volume OLTP workloads.
  • Choose an appropriate bucket count for hash indexes.
  • Monitor memory consumption carefully.
  • Use SCHEMA_AND_DATA for durable business data.

Temporal Tables

  • Use for audit and historical reporting.
  • Periodically archive large history tables if appropriate.
  • Query historical versions using FOR SYSTEM_TIME.

External Tables

  • Prefer efficient file formats such as Parquet.
  • Partition large datasets.
  • Minimize unnecessary data movement.
  • Push filtering to the external source whenever possible.

Ledger Tables

  • Use when regulatory compliance or auditability is required.
  • Understand the difference between append-only and updatable ledger tables.
  • Regularly verify ledger digests as part of governance processes.

Graph Tables

  • Use when relationships are central to the data model.
  • Avoid replacing well-designed relational models unnecessarily.
  • Combine graph and relational tables where appropriate.

Common Exam Tips

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

  • In-memory tables improve OLTP performance through memory optimization and optimistic concurrency.
  • Temporal tables automatically maintain historical versions of data using system versioning.
  • External tables allow SQL queries against data stored outside the database without importing it.
  • Ledger tables provide cryptographically verifiable, tamper-evident records for auditing and compliance.
  • Graph tables represent entities and relationships using node and edge tables and support graph pattern matching with the MATCH clause.
  • SCHEMA_ONLY memory-optimized tables do not preserve data after a restart, while SCHEMA_AND_DATA tables do.
  • Append-only ledger tables support inserts only; updatable ledger tables support inserts, updates, and deletes while preserving history.

Practice Exam Questions

Question 1

A financial trading application requires extremely high transaction throughput while minimizing locking and blocking. Which table type should be implemented?

A. Temporal table

B. In-memory table

C. Ledger table

D. External table

Answer: B

Explanation: In-memory tables use memory-optimized storage and lock-free, latch-free processing to maximize OLTP performance and concurrency.

Question 2

A company wants SQL Server to automatically maintain historical versions of customer records whenever updates occur. Which feature should be used?

A. External table

B. Graph table

C. Temporal table

D. Ledger table

Answer: C

Explanation: Temporal tables automatically maintain current and historical versions of rows using system versioning, eliminating the need for custom audit triggers.

Question 3

A database administrator needs to query Parquet files stored in Azure Data Lake without copying the data into SQL Server. Which table type is most appropriate?

A. External table

B. Ledger table

C. In-memory table

D. Temporal table

Answer: A

Explanation: External tables provide metadata that enables SQL queries against external data sources such as Azure Data Lake and Parquet files without importing the data.

Question 4

Which specialized table type provides cryptographic verification that historical records have not been altered?

A. Graph table

B. External table

C. Temporal table

D. Ledger table

Answer: D

Explanation: Ledger tables use cryptographic hashing and transaction digests to create tamper-evident records suitable for auditing and regulatory compliance.

Question 5

Which statement about memory-optimized tables configured with SCHEMA_ONLY durability is correct?

A. Both schema and data survive a server restart.

B. The table cannot contain indexes.

C. The table definition remains, but the data is lost after a restart.

D. The table is automatically converted to a temporal table.

Answer: C

Explanation: SCHEMA_ONLY durability preserves only the table definition. Data stored in the table is not retained after the SQL Server instance restarts.

Question 6

Which SQL Server feature is specifically designed to model entities and their relationships using node and edge tables?

A. Ledger

B. PolyBase

C. Temporal

D. Graph

Answer: D

Explanation: Graph tables use node and edge tables to represent entities and relationships and support graph pattern matching using the MATCH clause.

Question 7

A retail company wants to analyze historical pricing information exactly as it existed on a specific date six months ago. Which query capability supports this requirement?

A. MATCH

B. FOR SYSTEM_TIME AS OF

C. OPENROWSET

D. MERGE

Answer: B

Explanation: The FOR SYSTEM_TIME AS OF clause queries a temporal table as it existed at a specified point in time.

Question 8

Which workload is the best candidate for a clustered in-memory table?

A. A data warehouse containing historical sales data

B. A compliance audit archive

C. A product catalog that rarely changes

D. A high-volume online order processing system

Answer: D

Explanation: In-memory tables are designed for OLTP workloads with frequent inserts, updates, and concurrent transactions.

Question 9

What is the primary advantage of using external tables in AI-enabled database solutions?

A. They automatically encrypt all external files.

B. They eliminate the need for indexes.

C. They enable SQL queries against externally stored datasets without duplicating the data.

D. They automatically convert relational data into graph structures.

Answer: C

Explanation: External tables allow SQL Server and Azure SQL to access external data sources directly, making them ideal for AI workloads that consume large datasets stored in data lakes.

Question 10

Which scenario is the best fit for an append-only ledger table?

A. A customer profile database requiring frequent updates

B. A temporary ETL staging table

C. A social network relationship graph

D. A financial transaction journal where records must never be modified or deleted

Answer: D

Explanation: Append-only ledger tables permit inserts but prevent updates and deletes, making them ideal for immutable transaction logs and regulatory audit records.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification

Design and implement tables, including data types, size, columns, indexes, and column store indexes (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and implement tables, including data types, size, columns, indexes, and column store indexes

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

One of the most fundamental skills measured on the DP-800: Developing AI-Enabled Database Solutions exam is the ability to design and implement efficient database tables. Every SQL solution—whether supporting traditional applications, analytics, or AI-enabled workloads—depends on well-designed tables that maximize performance, maintain data integrity, minimize storage requirements, and scale effectively.

Poor table design often results in slow queries, excessive storage consumption, locking issues, and difficult maintenance. Conversely, properly designed tables improve application responsiveness, simplify development, and reduce infrastructure costs.

This article covers the key concepts required for the DP-800 exam, including:

  • Choosing appropriate data types
  • Determining column sizes
  • Designing table structures
  • Creating clustered and nonclustered indexes
  • Understanding filtered, included, and composite indexes
  • Implementing columnstore indexes
  • Best practices and common design mistakes

Designing Tables

A table stores related information organized into rows and columns.

Good table design should achieve the following goals:

  • Eliminate unnecessary duplication
  • Support efficient queries
  • Enforce data integrity
  • Reduce storage requirements
  • Support future growth
  • Minimize maintenance

A typical design process includes:

  1. Identify entities
  2. Define columns
  3. Choose data types
  4. Select appropriate sizes
  5. Determine nullable columns
  6. Define primary keys
  7. Create foreign keys
  8. Add indexes based on workload

Choosing Appropriate Data Types

Selecting the correct data type is one of the most important database design decisions.

Using oversized or inappropriate data types increases:

  • Storage usage
  • Memory usage
  • Network traffic
  • Index size
  • Backup size
  • Query execution time

Integer Data Types

Data TypeStorageRange
TINYINT1 byte0–255
SMALLINT2 bytes-32,768 to 32,767
INT4 bytes±2.1 billion
BIGINT8 bytesExtremely large values

Example:

CustomerID INT
OrderID BIGINT
Age TINYINT

CustomerID INT

OrderID BIGINT

Age TINYINT



Decimal and Numeric

Used for precise financial calculations.

Price DECIMAL(10,2)

Price DECIMAL(10,2)



  • 10 total digits
  • 2 digits after the decimal

Examples:

12345678.90
99999999.99

12345678.90
99999999.99<b
r>


</b

Floating Point Types

Used for scientific calculations.

FLOAT
REAL

FLOAT



REAL

  • Measurements
  • Statistics
  • Sensor data

Avoid for:

  • Currency
  • Accounting
  • Financial systems

Character Data Types

CHAR

Fixed-length storage.

CHAR(2)

CHAR(2)



  • Country codes
  • State abbreviations
  • Status values

VARCHAR

Variable-length storage.

VARCHAR(100)

VARCHAR(100)



Ideal for:

  • Names
  • Email addresses
  • Descriptions

NCHAR and NVARCHAR

Support Unicode characters.

NVARCHAR(100)

NVARCHAR(100)



  • Multiple languages
  • International names
  • Emoji
  • Unicode symbols

VARCHAR(MAX)

Stores very large text.

Use only when necessary.

Examples:

  • Documents
  • Long descriptions
  • JSON

Avoid using MAX columns unnecessarily because they reduce performance.

Date and Time Data Types

Common options include:

TypeDescription
DATEDate only
TIMETime only
DATETIME2Date and time with high precision
DATETIMEOFFSETDate/time plus time zone

Microsoft recommends DATETIME2 for most new applications.

Example:

CreatedDate DATETIME2

Binary Data Types

Examples include:

VARBINARY
VARBINARY(MAX)

VARBINARY



VARBINARY(MAX)

  • Images
  • Encryption keys
  • Files
  • AI embeddings (in some scenarios)

UniqueIdentifier

Stores globally unique identifiers (GUIDs).

CustomerGuid UNIQUEIDENTIFIER

CustomerGuid UNIQUEIDENTIFIER



  • Globally unique
  • Useful for distributed systems

Drawbacks:

  • Larger indexes
  • Can fragment clustered indexes when generated randomly

NULL vs NOT NULL

Every column should explicitly define whether NULL values are allowed.

Example:

FirstName NVARCHAR(50) NOT NULL
MiddleName NVARCHAR(50) NULL

FirstName NVARCHAR(50) NOT NULL

MiddleName NVARCHAR(50) NULL



  • Improves data integrity
  • Simplifies queries
  • Often improves performance

Identity Columns

Automatically generate sequential values.

Example:

CustomerID INT IDENTITY(1,1)

CustomerID INT IDENTITY(1,1)



Start at 1

Increment by 1

Commonly used as surrogate primary keys.

Computed Columns

Values calculated from other columns.

Example:

FullName AS FirstName + ' ' + LastName

FullName AS FirstName + ‘ ‘ + LastName



Sparse Columns

Designed for tables with many NULL values.

Benefits:

  • Reduce storage
  • Useful for optional attributes

Trade-off:

Slightly higher processing overhead.

Table Constraints

Constraints enforce data integrity.

Primary Key

Uniquely identifies each row.

PRIMARY KEY (CustomerID)

PRIMARY KEY (CustomerID)



  • Unique
  • NOT NULL
  • Automatically indexed

Foreign Key

Maintains relationships between tables.

FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)

UNIQUE Constraint

Prevents duplicate values.

Example:

EmailAddress UNIQUE

CHECK Constraint

Restricts acceptable values.

Example:

CHECK (Salary > 0)

DEFAULT Constraint

Automatically inserts default values.

Example:

CreatedDate DATETIME2
DEFAULT GETDATE()

Index Fundamentals

Indexes improve query performance by reducing table scans.

Without indexes:

SQL Server reads every row.

SQL Server reads every row.



SQL Server quickly locates matching rows.

SQL Server quickly locates matching rows.



  • WHERE
  • JOIN
  • ORDER BY
  • GROUP BY

Clustered Index

Determines the physical order of rows.

Each table can have only one clustered index.

Example:

CREATE CLUSTERED INDEX IX_Customers
ON Customers(CustomerID);

CREATE CLUSTERED INDEX IX_Customers

ON Customers(CustomerID);



  • Primary keys
  • Sequential values

Nonclustered Index

Stores a separate searchable structure.

A table can have many nonclustered indexes.

Example:

CREATE INDEX IX_LastName
ON Customers(LastName);

CREATE INDEX IX_LastName

ON Customers(LastName);



Composite Index

Contains multiple columns.

Example:

CREATE INDEX IX_OrderDateCustomer
ON Orders(OrderDate, CustomerID);

CREATE INDEX IX_OrderDateCustomer

ON Orders(OrderDate, CustomerID);



The leftmost column should be the most selective or frequently filtered.

Included Columns

Include non-key columns to create covering indexes.

Example:

CREATE INDEX IX_LastName
ON Customers(LastName)
INCLUDE (FirstName, EmailAddress);

CREATE INDEX IX_LastName

ON Customers(LastName)

INCLUDE (FirstName, EmailAddress);



  • Reduces key lookups
  • Improves SELECT performance

Filtered Index

Indexes only selected rows.

Example:

CREATE INDEX IX_ActiveCustomers
ON Customers(Status)
WHERE Status='Active';

CREATE INDEX IX_ActiveCustomers

ON Customers(Status)



WHERE Status=’Active’;

  • Smaller index
  • Faster maintenance
  • Better query performance

Covering Index

A covering index contains every column required by a query.

Example:

Query:

SELECT FirstName, LastName
FROM Customers
WHERE LastName='Smith';

SELECT FirstName, LastName

FROM Customers

WHERE LastName=’Smith’;



  • LastName (key)
  • FirstName (included)

No lookup to the base table is required.

Index Maintenance

Indexes require regular maintenance.

Common tasks include:

  • Rebuild indexes
  • Reorganize indexes
  • Update statistics
  • Monitor fragmentation

Highly fragmented indexes reduce performance.

Columnstore Indexes

Columnstore indexes store data by columns rather than rows.

Traditional storage:

Row 1
Row 2
Row 3

Row 1

Row 2

Row 3



CustomerID
FirstName
LastName
City

CustomerID

FirstName

LastName

City



Benefits of Columnstore Indexes

Advantages include:

  • High compression
  • Reduced storage
  • Faster aggregations
  • Parallel processing
  • Batch execution mode

Ideal for:

  • Data warehouses
  • Reporting
  • Analytics
  • AI feature engineering
  • Large fact tables

Clustered Columnstore Index

Entire table stored in column format.

Example:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales
ON Sales;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales

ON Sales;



  • Fact tables
  • Large analytical workloads

Nonclustered Columnstore Index

Adds columnstore capabilities to an existing rowstore table.

Example:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales
ON Sales
(
Revenue,
Quantity,
ProductID
);

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales

ON Sales


(
Reven
ue,
Quantity,

ProductID

);



Rowstore vs Columnstore

FeatureRowstoreColumnstore
Best forOLTPAnalytics
InsertsExcellentGood
UpdatesExcellentModerate
AggregationsModerateExcellent
CompressionLowVery High
Large scansSlowerMuch Faster

Choosing the Right Index

ScenarioRecommended Index
Primary keyClustered
Frequent lookupsNonclustered
Multi-column searchesComposite
ReportingColumnstore
Active records onlyFiltered
Covering queriesIncluded columns

Best Practices

  • Choose the smallest appropriate data type.
  • Avoid VARCHAR(MAX) unless required.
  • Use DATETIME2 instead of DATETIME for new development.
  • Define NOT NULL whenever appropriate.
  • Create indexes based on query patterns rather than every column.
  • Avoid excessive indexing because each index increases insert, update, and delete costs.
  • Use composite indexes carefully, considering column order.
  • Regularly rebuild or reorganize fragmented indexes.
  • Use clustered columnstore indexes for large analytical tables.
  • Test index changes using execution plans and performance metrics.

Common Exam Tips

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

  • Smaller data types improve storage efficiency.
  • Clustered indexes determine physical row order.
  • A table can have only one clustered index.
  • Multiple nonclustered indexes are allowed.
  • Included columns create covering indexes.
  • Filtered indexes reduce storage and improve performance for selective queries.
  • Composite index column order matters.
  • Columnstore indexes are optimized for analytical workloads.
  • DATETIME2 is preferred over DATETIME for new applications.
  • FLOAT should not be used for financial data.

Practice Exam Questions

Question 1

A database designer needs to store a person’s age. The maximum expected value is 120. Which data type is the most storage-efficient?

A. INT

B. SMALLINT

C. TINYINT

D. BIGINT

Answer: C

Explanation: TINYINT stores values from 0 to 255 using only one byte, making it the most efficient choice for age values.

Question 2

A company stores customer names in multiple languages, including Japanese and Arabic. Which data type should be used?

A. CHAR

B. VARCHAR

C. TEXT

D. NVARCHAR

Answer: D

Explanation: NVARCHAR supports Unicode characters, making it suitable for multilingual applications.

Question 3

Which index determines the physical order of rows within a SQL Server table?

A. Nonclustered index

B. Filtered index

C. Clustered index

D. Columnstore index

Answer: C

Explanation: A clustered index defines the physical storage order of rows. Each table can have only one clustered index.

Question 4

A reporting system performs large aggregation queries against a fact table containing hundreds of millions of rows. Which index type is most appropriate?

A. Clustered columnstore index

B. Nonclustered index

C. Filtered index

D. XML index

Answer: A

Explanation: Clustered columnstore indexes are optimized for large analytical workloads, providing high compression and fast aggregations.

Question 5

Why might a developer create a filtered index?

A. To improve backup performance

B. To index only rows matching a specific condition

C. To encrypt indexed values

D. To automatically partition a table

Answer: B

Explanation: A filtered index includes only rows that satisfy a defined predicate, reducing storage and maintenance while improving performance for targeted queries.

Question 6

A table has a composite index on (OrderDate, CustomerID). Which query is most likely to benefit directly from the index?

A. Filtering only on CustomerID

B. Filtering only on ProductID

C. Filtering on OrderDate

D. Filtering only on TotalAmount

Answer: C

Explanation: Composite indexes are most effective when queries use the leftmost indexed column. A filter on OrderDate can efficiently leverage the index.

Question 7

Which statement about clustered indexes is correct?

A. A table can have many clustered indexes.

B. Clustered indexes cannot contain primary keys.

C. Clustered indexes store data separately from the table.

D. A table can have only one clustered index.

Answer: D

Explanation: Because a clustered index defines the physical order of the rows, only one clustered index can exist per table.

Question 8

A developer wants to eliminate expensive key lookups for a frequently executed query without changing the indexed search column. Which feature should be used?

A. Sparse columns

B. Included columns

C. Identity columns

D. Computed columns

Answer: B

Explanation: Included columns allow additional non-key columns to be stored in a nonclustered index, creating a covering index that can avoid key lookups.

Question 9

Which data type is recommended for storing currency values that require exact precision?

A. FLOAT

B. REAL

C. DECIMAL

D. MONEY with floating-point conversion

Answer: C

Explanation: DECIMAL provides fixed precision and scale, making it appropriate for financial calculations where exact values are required.

Question 10

Why are columnstore indexes particularly valuable for AI and analytics workloads?

A. They increase transaction locking.

B. They optimize sequential identity generation.

C. They eliminate the need for primary keys.

D. They provide high compression and significantly accelerate large scan and aggregation queries.

Answer: D

Explanation: Columnstore indexes organize data by column, enabling excellent compression and efficient execution of analytical queries common in reporting, feature engineering, and AI scenarios.

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

AB-620, Agentic AI, AI, Azure AI, Microsoft Certification

Exam Prep Hub for AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio

Welcome to the AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio Exam Prep Hub!

Welcome to the one-stop hub with information for preparing for the AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio certification exam. The content for this exam helps prepare you to be a developer that “builds, extends, and integrates custom agents for enterprise-grade solutions”.
Upon successful completion of the exam, you earn the Microsoft Certified: AI Agent Builder Associate (beta) certification.

This hub provides information directly here (topic-by-topic as outlined in the official study guide), links to a number of external resources, tips for preparing for the exam, practice tests, and section questions to help you prepare. Bookmark this page and use it as a guide to ensure that you are fully covering all relevant topics for the AB-620 exam and making use of as many of the resources available as possible.

Audience profile (from Microsoft’s site)


As a candidate for this Microsoft Certification, you’re a professional developer or advanced builder who builds, extends, and integrates custom agents for enterprise-grade solutions. You typically work as an IT application developer, consultant, or independent software vendor (ISV) partner focused on creating scalable AI solutions for organizations or customers.
For this exam, you should be familiar with Power Fx, Microsoft Dataverse, Microsoft Power Platform environments and components, Microsoft 365 Copilot, Microsoft Foundry, and adaptive cards.
You need intermediate knowledge of generative AI concepts, including models, orchestration, retrieval-augmented generation (RAG), Model Context Protocol (MCP), Agent2Agent (A2A) protocol, and more. You should also have experience with prompt engineering and with REST APIs and integration patterns. Additionally, you need experience configuring agents with basic knowledge sources, instructions, tools, and topics in Microsoft Copilot Studio.
As a developer who works in Copilot Studio, you:
- Integrate agents with Microsoft Foundry.
- Integrate agents with Model Context Protocol (MCP) servers.
- Integrate agents with custom connectors.
- Integrate agents with APIs.
- Integrate agents with Microsoft Fabric.
- Automate tasks with computer use.
- Integrate agents with connectors.
You create:
- Multi-agent solutions.
- Agents with enterprise knowledge sources (such as ServiceNow, SAP, and others).
- Advanced agent topics and tools.
- Computer-using agents.
- Agents that perform advanced actions via APIs.
You collaborate with Microsoft 365 administrators, Microsoft Power Platform administrators, Microsoft Copilot administrators, Copilot Studio agent builders, Copilot Studio administrators, Foundry administrators, agentic AI business solutions architects, and Copilot Studio architects.

Skills at a glance (as specified in the official study guide)

  • Plan and configure agent solutions (30–35%)
  • Integrate and extend agents in Copilot Studio (40–45%)
  • Test and manage agents (20–25%)

Topic-by-Topic Exam Content

[click a topic link to access the content and practice questions for that topic]

Plan and configure agent solutions (30–35%)

Plan an agent solution

Create and monitor agent flows in Copilot Studio

Configure topics

Integrate and extend agents in Copilot Studio (40–45%)

Connect to enterprise knowledge sources

Add tools to agents

Configure multi-agent collaboration from Copilot Studio

Integrate agents with Azure

Test and manage agents (20–25%)

Evaluate agent performance

Implement application lifecycle management (ALM) for agents in Copilot Studio

AB-620 Practice Exams

Important AB-620 Resources

Link to the free, comprehensive, self-paced course on Microsoft Learn:
Design and build integrated AI agent solutions in Copilot Studio
https://learn.microsoft.com/en-us/training/courses/ab-620t00

This course has 3 Learning Paths:

(1) Design agent conversations and responses using topics in Microsoft Copilot Studio

This Learning Path has 3 modules:

(i) Deliver rich agent responses using Adaptive Cards in Microsoft Copilot Studio

(ii) Take action from agent conversations using topics and tools in Microsoft Copilot Studio

(iii) Generate AI-powered agent responses using generative answers in Microsoft Copilot Studio

(2) Design and build multi-agent solutions in Microsoft Copilot Studio

This Learning Path has 4 modules:

(i) Design multi-agent solutions in Microsoft Copilot Studio

(ii) Delegate agent tasks using child agents in Copilot Studio

(iii) Build multi-agent solutions using connected agents in Copilot Studio

(iv) Build cross-platform multi-agent solutions using the Agent2Agent protocol in Microsoft Copilot Studio

(3) Integrate agents with enterprise systems in Microsoft Copilot Studio

This Learning Path has 4 modules:

(i) Design integration strategies for agents in Microsoft Copilot Studio

(ii) Take action in external systems using connector and REST API agent tools in Microsoft Copilot Studio

(iii) Ground agents with enterprise knowledge using connectors and Azure AI Search in Microsoft Copilot Studio

(iv) Integrate agents with external systems via MCP in Microsoft Copilot Studio

Link to the certification page:

Link to the study guide:


YouTube resources:

Courses: This is a highly rated course for AB-620 on Udemy:

Check out the previews of each course you are considering to decide which trainer is best for you. And a tip for you … if your timeline allows for it, wait for the occasional Udemy sale to buy your course(s).

Good luck to you passing the AB-900 Exam!
However, the more preparation you have, the less luck you will need. 🙂

Visit this post to see the list of all the certification preparation hubs available on The Data Community.

AB-620, Microsoft Certification

AB-620 Practice Exam #4 (30 questions)

 
This practice exam is a part of the AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio Exam Prep Hub.

Question 1 (Scenario-Based)

A multinational retailer plans to implement a conversational AI platform. The architecture must meet these requirements:

  • Customer conversations begin with a single entry point.
  • Pricing data is stored in Dataverse.
  • Product manuals reside in SharePoint.
  • Inventory information is retrieved from SAP in real time.
  • Specialized fulfillment, returns, and warranty teams manage their own agents independently.

Which architecture best satisfies these requirements?

A. Build one agent with all business logic and duplicate each department’s topics.

B. Use Connected Agents, Azure AI Search (or approved enterprise knowledge grounding) for manuals, Connector/REST API Tools for SAP and Dataverse, and delegate specialized tasks to department-owned agents.

C. Store inventory inside SharePoint and answer all questions using Generative Answers.

D. Create separate standalone agents without communication.

Answer: B

Explanation:
This design separates static knowledge from transactional data, supports independent ownership through Connected Agents, and retrieves live business data from authoritative systems.

Question 2 (Multiple Answer)

Which TWO characteristics describe an effective enterprise grounding strategy?

A. Use trusted organizational knowledge sources.

B. Ground responses using public internet content whenever possible.

C. Refresh indexes as enterprise content changes.

D. Store transactional ERP data inside conversation topics.

Answers: A, C

Question 3 (Single Answer)

Which scenario most strongly favors a Connector Tool over a REST API Tool?

A. Accessing a well-supported Microsoft 365 service through an existing connector

B. Calling a proprietary HTTP endpoint with no available connector

C. Querying Azure AI Search

D. Displaying an Adaptive Card

Answer: A

Explanation:
When a supported connector already exists, it typically reduces development effort and maintenance compared to implementing a custom REST integration.

Question 4 (Fill in the Blank)

Adaptive Cards primarily improve the __________ experience during conversations.

A. indexing

B. retrieval

C. authentication

D. user interaction

Answer: D

Question 5 (Match the Answers)

Match each capability with the most appropriate use case.

CapabilityUse Case
1. Generative AnswersA. Retrieve enterprise knowledge
2. REST API ToolB. Execute live business transaction
3. Connected AgentC. Collaborate across independently managed agents
4. Adaptive CardD. Collect structured user input

Answer

  • 1 → A
  • 2 → B
  • 3 → C
  • 4 → D

Question 6 (Scenario-Based)

Users report that AI-generated responses frequently cite outdated procedures even though newer documents exist.

What should be investigated first?

A. Conversation greetings

B. Knowledge source synchronization and indexing

C. Trigger phrase wording

D. Child Agent configuration

Answer: B

Question 7 (Multiple Answer)

Which TWO design decisions improve long-term maintainability?

A. Isolate reusable business capabilities.

B. Create highly specialized agents with clear ownership.

C. Duplicate conversation logic across agents.

D. Combine unrelated business domains into one topic.

Answers: A, B

Question 8 (Single Answer)

Which architecture best supports independent deployment cycles across business units?

A. Large monolithic agent

B. Connected Agents

C. Single topic with branches

D. Adaptive Cards

Answer: B

Question 9 (Scenario-Based)

An airline wants multiple AI systems developed by different vendors to exchange requests without requiring proprietary integrations.

Which capability is specifically intended for this scenario?

A. Connector Tools

B. Azure AI Search

C. Agent2Agent protocol

D. Adaptive Cards

Answer: C

Question 10 (Single Answer)

What is the primary purpose of MCP?

A. Replace Azure AI Search

B. Replace REST APIs

C. Standardize communication with external tools and services

D. Replace Connected Agents

Answer: C

Question 11 (Multiple Answer)

Which TWO actions should occur before invoking an operation that modifies customer records?

A. Authenticate the user.

B. Validate required inputs.

C. Display an image.

D. Perform semantic search.

Answers: A, B

Question 12 (Scenario-Based)

A company stores millions of engineering documents in multiple repositories.

Employees ask natural language questions requiring semantic understanding.

Which capability should provide the primary grounding layer?

A. Adaptive Cards

B. Trigger phrases

C. Topics

D. Azure AI Search

Answer: D

Question 13 (Single Answer)

A Child Agent should ideally be responsible for:

A. One cohesive business capability

B. Every conversation in the solution

C. User authentication

D. Conversation analytics

Answer: A

Question 14 (Multiple Answer)

Which TWO situations justify using REST API Tools?

A. Real-time order status

B. Account balance lookup

C. Employee handbook retrieval

D. Vacation policy search

Answers: A, B

Question 15 (Scenario-Based)

A logistics organization wants warehouse, transportation, customs, and billing agents maintained by separate teams while preserving conversational context.

Which design is MOST appropriate?

A. Child Topics

B. Connected Agents

C. Static Topics

D. Azure AI Search

Answer: B

Question 16 (Single Answer)

Which statement best describes semantic search?

A. Searches only exact keywords.

B. Understands intent and contextual meaning.

C. Searches images only.

D. Retrieves only structured databases.

Answer: B

Question 17 (Match the Answers)

Match each technology with its primary purpose.

TechnologyPurpose
1. Connector ToolA. Prebuilt application integration
2. REST API ToolB. Custom HTTP integration
3. MCPC. External tool interoperability
4. Agent2AgentD. Agent-to-agent collaboration

Answer

  • 1 → A
  • 2 → B
  • 3 → C
  • 4 → D

Question 18 (Scenario-Based)

A support agent occasionally generates responses that are technically correct but reference obsolete procedures.

Which corrective action is MOST appropriate?

A. Increase greeting length.

B. Review knowledge governance, source quality, and grounding configuration.

C. Add more trigger phrases.

D. Create additional topics.

Answer: B

Question 19 (Multiple Answer)

Which TWO production metrics provide the strongest indication of agent effectiveness?

A. Successful task completion rate

B. Escalation rate

C. Number of Adaptive Cards displayed

D. Number of topics created

Answers: A, B

Question 20 (Single Answer)

Which design principle best supports enterprise scalability?

A. Modular business capabilities

B. Large conversation topics

C. Duplicate workflows

D. Static conversations

Answer: A

Question 21 (Scenario-Based)

A healthcare provider requires public health information to be available anonymously while patient-specific information requires authentication.

Which approach should be implemented?

A. Require authentication for every conversation.

B. Authenticate only before protected operations.

C. Disable anonymous access entirely.

D. Authenticate after returning patient data.

Answer: B

Question 22 (Fill in the Blank)

Conversation analytics primarily help identify opportunities to improve agent __________.

A. licensing

B. responsiveness and effectiveness

C. storage

D. deployment frequency

Answer: B

Question 23 (Single Answer)

Which capability enables users to complete structured forms directly within conversations?

A. Generative Answers

B. Azure AI Search

C. Topics

D. Adaptive Cards

Answer: D

Question 24 (Multiple Answer)

Which TWO activities should be included in production validation?

A. Verify API integrations.

B. Test delegation paths.

C. Disable analytics.

D. Remove authentication.

Answers: A, B

Question 25 (Scenario-Based)

A financial institution wants AI-generated investment guidance to reference only approved internal research while excluding public internet sources.

Which design is most appropriate?

A. Ground Generative Answers using approved enterprise repositories only.

B. Enable unrestricted internet search.

C. Store research inside Adaptive Cards.

D. Replace Generative Answers with greeting topics.

Answer: A

Question 26 (Single Answer)

Which statement best explains why Connected Agents are preferred over one monolithic agent in large organizations?

A. They allow teams to independently develop, deploy, and maintain specialized capabilities.

B. They eliminate the need for testing.

C. They replace Azure AI Search.

D. They require fewer APIs.

Answer: A

Question 27 (Multiple Answer)

Which TWO capabilities primarily support enterprise integrations?

A. Connector Tools

B. REST API Tools

C. Adaptive Cards

D. Trigger phrases

Answers: A, B

Question 28 (Scenario-Based)

An organization has adopted MCP to standardize integrations with external AI tools. A new partner introduces an AI service that also supports MCP.

What is the primary architectural benefit?

A. Existing integration patterns can be reused with minimal custom development.

B. Azure AI Search is no longer required.

C. REST APIs become unsupported.

D. Connected Agents are automatically replaced.

Answer: A

Question 29 (Single Answer)

What is the primary responsibility of Agent2Agent (A2A)?

A. Authenticating users

B. Indexing enterprise documents

C. Displaying Adaptive Cards

D. Standardizing communication between compatible AI agents

Answer: D

Question 30 (Complex Architecture Scenario)

A multinational enterprise is modernizing its customer engagement platform.

Requirements include:

  • One customer-facing entry-point agent.
  • Independent development teams for Finance, Sales, HR, Logistics, and Customer Support.
  • More than 50 million enterprise documents.
  • AI responses must cite trusted internal knowledge.
  • Customer account information must always come directly from operational systems.
  • Third-party AI services should participate without proprietary integrations.
  • Future business domains should be added with minimal redesign.
  • Administrators want detailed production analytics and continuous monitoring after deployment.

Which architecture BEST satisfies all requirements?

A. One monolithic agent using only Generative Answers.

B. Connected Agents with Generative Answers grounded on trusted enterprise knowledge (such as Azure AI Search), Connector and REST API Tools for live business transactions, Agent2Agent and MCP for interoperable integrations, and continuous monitoring with analytics after deployment.

C. Multiple isolated agents with nightly synchronization.

D. Child Agents with all operational data indexed into enterprise search.

Answer: B

Explanation:
This architecture aligns with Microsoft-recommended enterprise design principles:

  • Connected Agents provide scalable orchestration across independently managed business domains.
  • Enterprise knowledge is grounded using trusted repositories (for example, Azure AI Search).
  • Connector Tools and REST API Tools retrieve authoritative, real-time operational data rather than relying on indexed copies.
  • Agent2Agent enables interoperable communication among compatible AI agents.
  • MCP standardizes interactions with external tools and AI services.
  • Continuous analytics and monitoring support ongoing optimization, governance, and operational excellence.

Go to the AB-620 Exam Prep Hub main page

AB-620, Microsoft Certification

AB-620 Practice Exam #3 (30 questions)

 
This practice exam is a part of the AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio Exam Prep Hub.

Question 1 (Scenario-Based)

A global insurance company is building a customer support solution. A front-door agent must answer policy questions, submit claims, check claim status, and schedule inspections. Specialized teams own each business capability and deploy their own agents independently.

Which architecture provides the greatest scalability while minimizing maintenance?

A. Create one large agent containing all business logic.

B. Use Connected Agents with specialized agents for claims, inspections, and policies.

C. Create separate topics for every department inside one agent.

D. Create multiple child topics within a single conversation.

Answer: B

Explanation:
Connected Agents allow independently managed agents to collaborate while preserving conversational context. This architecture scales better than a monolithic agent.

Question 2 (Multiple Answer)

An enterprise architect wants to reduce hallucinations generated by AI responses.

Which TWO actions should be recommended?

A. Ground responses using trusted enterprise knowledge.

B. Increase the number of greeting topics.

C. Restrict generative responses to approved knowledge sources.

D. Duplicate trigger phrases across topics.

Answers: A, C

Explanation:
Grounding responses with trusted knowledge sources significantly reduces hallucinations and improves factual accuracy.

Question 3 (Single Answer)

Which situation is the best candidate for using a REST API Tool instead of Generative Answers?

A. Retrieving company vacation policy

B. Looking up product documentation

C. Answering frequently asked questions

D. Checking the real-time balance of a customer’s account

Answer: D

Explanation:
REST API Tools are intended for transactional or live operational data.

Question 4 (Fill in the Blank)

When designing reusable conversations, business logic should remain independent of the __________ layer.

A. authentication

B. presentation

C. storage

D. analytics

Answer: B

Question 5 (Match the Answers)

Match each capability with the primary scenario.

CapabilityScenario
1. Child AgentA. Enterprise semantic search
2. MCPB. Specialized delegated capability
3. Azure AI SearchC. External tool interoperability
4. Adaptive CardD. Interactive user experience

Answer

  • 1 → B
  • 2 → C
  • 3 → A
  • 4 → D

Question 6 (Scenario-Based)

Your company maintains over 15 million engineering documents.

Employees frequently ask technical questions using natural language.

Which solution provides the highest quality retrieval?

A. Manual topics

B. SharePoint folders only

C. Azure AI Search with semantic and vector search

D. Adaptive Cards

Answer: C

Question 7 (Multiple Answer)

A parent agent delegates requests to several child agents.

Which TWO design practices improve maintainability?

A. Assign each child agent a single business responsibility.

B. Allow every child agent to perform every task.

C. Reuse child agents across multiple parent conversations.

D. Duplicate business logic inside every child.

Answers: A, C

Question 8 (Single Answer)

A conversation requires collecting several related inputs before calling an external system.

Which approach provides the cleanest user experience?

A. Multiple sequential text questions

B. Adaptive Card form

C. Multiple trigger phrases

D. Generative Answers

Answer: B

Question 9 (Scenario-Based)

A multinational organization acquires another company whose AI agents were built using different technologies.

Management wants both ecosystems to communicate without rewriting either platform.

Which capability best satisfies this requirement?

A. Child Agents

B. Connected Topics

C. Agent2Agent protocol

D. Azure AI Search

Answer: C

Question 10 (Single Answer)

Which statement about MCP is TRUE?

A. It replaces Azure AI Search.

B. It standardizes integration with external tools and services.

C. It replaces REST APIs.

D. It stores conversation history.

Answer: B

Question 11 (Multiple Answer)

An enterprise wants secure enterprise integrations.

Which TWO actions are recommended?

A. Authenticate users before sensitive operations.

B. Use least-privilege permissions for external systems.

C. Store passwords inside topics.

D. Disable authentication during production.

Answers: A, B

Question 12 (Scenario-Based)

A customer asks:

“Has my refund been processed?”

The answer must always reflect the current ERP status.

Which design should be implemented?

A. Store refund status in SharePoint.

B. Use Generative Answers.

C. Invoke a REST API Tool.

D. Create additional trigger phrases.

Answer: C

Question 13 (Single Answer)

Which design principle best improves long-term maintainability?

A. Centralize reusable business capabilities.

B. Create duplicate business logic.

C. Increase conversation depth.

D. Build larger topics.

Answer: A

Question 14 (Multiple Answer)

Which TWO scenarios are appropriate for Generative Answers?

A. Employee handbook questions

B. Company policy retrieval

C. Credit card authorization

D. Live inventory reservation

Answers: A, B

Question 15 (Scenario-Based)

Several specialized agents must collaborate while preserving the conversation context and allowing each department to deploy independently.

Which solution should you recommend?

A. Child Agents

B. Azure AI Search

C. Adaptive Cards

D. Connected Agents

Answer: D

Question 16 (Single Answer)

Which capability is primarily responsible for grounding AI responses using indexed enterprise content?

A. Adaptive Cards

B. Azure AI Search

C. Trigger phrases

D. Topics

Answer: B

Question 17 (Match the Answers)

Match each technology to its purpose.

TechnologyPurpose
1. Connector ToolA. Enterprise application integration
2. REST API ToolB. Custom HTTP endpoint
3. Connected AgentC. Multi-agent collaboration
4. TopicD. Conversation flow

Answer

  • 1 → A
  • 2 → B
  • 3 → C
  • 4 → D

Question 18 (Scenario-Based)

Users report that responses became less accurate after several new document repositories were connected.

What should be investigated FIRST?

A. Adaptive Card layout

B. Knowledge source quality and grounding configuration

C. Trigger phrase length

D. Topic names

Answer: B

Question 19 (Multiple Answer)

Which TWO metrics best evaluate production quality?

A. Successful task completion

B. Escalation percentage

C. Number of Adaptive Cards

D. Number of trigger phrases

Answers: A, B

Question 20 (Single Answer)

What is the primary benefit of semantic search over simple keyword search?

A. Lower storage costs

B. Better understanding of user intent

C. Faster authentication

D. Reduced API usage

Answer: B

Question 21 (Scenario-Based)

A banking organization wants every transaction request to require customer authentication while allowing public FAQ access anonymously.

What should you configure?

A. Authenticate every conversation immediately.

B. Require authentication only before protected actions.

C. Disable anonymous access.

D. Store authentication inside Adaptive Cards.

Answer: B

Question 22 (Fill in the Blank)

The primary objective of production monitoring is to continuously improve __________ and reliability.

A. storage

B. usability

C. performance

D. deployment frequency

Answer: C

Question 23 (Single Answer)

Which statement best describes Connected Agents?

A. They replace REST APIs.

B. They allow independently managed agents to collaborate.

C. They replace child agents in every scenario.

D. They perform semantic search.

Answer: B

Question 24 (Multiple Answer)

Which TWO tasks belong to production validation before deployment?

A. Test API integrations

B. Validate conversation routing

C. Delete historical analytics

D. Disable monitoring

Answers: A, B

Question 25 (Scenario-Based)

A healthcare organization wants clinicians to ask natural language questions while ensuring responses come only from approved medical documentation.

Which solution best satisfies the requirement?

A. Public internet search

B. Azure AI Search with approved medical repositories

C. Adaptive Cards

D. Trigger phrase expansion

Answer: B

Question 26 (Single Answer)

Why are modular conversation designs generally preferred?

A. Easier testing, maintenance, and reuse

B. More authentication

C. More trigger phrases

D. Less integration

Answer: A

Question 27 (Multiple Answer)

Which TWO capabilities are specifically intended for enterprise system integration?

A. Connector Tools

B. REST API Tools

C. Adaptive Cards

D. Trigger phrases

Answers: A, B

Question 28 (Scenario-Based)

A manufacturing company has independent Procurement, Inventory, Maintenance, and Shipping agents.

Executives want one customer-facing entry point while allowing each department to maintain its own release schedule.

Which architecture is MOST appropriate?

A. One large parent topic

B. One monolithic agent

C. Connected Agents

D. Azure AI Search only

Answer: C

Question 29 (Single Answer)

Which capability is responsible for presenting rich forms, buttons, and images within conversations?

A. Azure AI Search

B. Topics

C. Adaptive Cards

D. REST API Tools

Answer: C

Question 30 (Complex Scenario)

A multinational enterprise is building an intelligent service platform.

Requirements include:

  • Customer conversations begin with a single entry-point agent.
  • Business domains are maintained by independent development teams.
  • Enterprise knowledge exceeds 25 million documents.
  • AI responses must be grounded using semantic retrieval.
  • Customer account information must always be retrieved in real time.
  • External AI systems from partner organizations must participate in workflows.
  • Future integrations should require minimal architectural changes.

Which solution BEST satisfies all requirements?

A. Build one monolithic agent using Generative Answers for every request.

B. Build separate agents without communication and synchronize data nightly.

C. Use Child Agents, storing all customer information in Azure AI Search.

D. Use Connected Agents, Azure AI Search for enterprise grounding, REST API Tools for transactional data, and Agent2Agent/MCP for interoperable external integrations.

Answer: D

Explanation:
This design follows Microsoft’s recommended architectural principles:

  • Connected Agents provide scalable orchestration.
  • Azure AI Search grounds responses over large enterprise repositories.
  • REST API Tools retrieve authoritative live transactional data.
  • Agent2Agent enables communication between heterogeneous AI agents.
  • MCP provides standardized interoperability with external tools and services.

Go to the AB-620 Exam Prep Hub main page

AB-620, Microsoft Certification

AB-620 Practice Exam #2 (30 questions)

 
This practice exam is a part of the AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio Exam Prep Hub.

Question 1 (Scenario-Based)

A multinational company is building a customer service agent. Product documentation is stored in SharePoint, technical manuals are indexed in Azure AI Search, and warranty information is available through a REST API.

The agent should answer questions using documentation whenever possible but retrieve live warranty information only when customers ask about an individual product.

Which design best satisfies these requirements?

A. Place all warranty data inside SharePoint.

B. Configure Generative Answers for all data sources, including the REST API.

C. Use Generative Answers for documentation and invoke a REST API Tool only when warranty information is required.

D. Build separate agents for documentation and warranties without delegation.

Answer: C

Explanation:
Generative Answers should retrieve static enterprise knowledge, while live transactional data should be obtained through REST API Tools only when needed.

Question 2 (Multiple Answer)

A company wants to reduce maintenance effort when building dozens of conversational workflows.

Which TWO design practices should be recommended?

A. Create reusable child topics for common business processes.

B. Duplicate topics for each business unit.

C. Build modular conversation flows.

D. Store business logic inside Adaptive Cards.

Answers: A, C

Explanation:
Reusable, modular conversation design significantly improves maintainability.

Question 3 (Single Answer)

Which characteristic best distinguishes Connected Agents from Child Agents?

A. Connected Agents can communicate across independently managed agents.

B. Child Agents always require REST APIs.

C. Connected Agents cannot return conversation context.

D. Child Agents require Azure AI Search.

Answer: A

Explanation:
Connected Agents enable collaboration among independently managed agents, whereas Child Agents are subordinate components of a parent agent.

Question 4 (Fill in the Blank)

Adaptive Cards primarily separate the presentation layer from the ________ layer.

A. Storage

B. Authentication

C. Business logic

D. Analytics

Answer: C

Question 5 (Match the Answers)

Match each component to its primary responsibility.

ComponentResponsibility
1. TopicA. Enterprise knowledge retrieval
2. Connector ToolB. Conversation workflow
3. Azure AI SearchC. External application integration
4. Adaptive CardD. Interactive user interface

Answer

  • 1 → B
  • 2 → C
  • 3 → A
  • 4 → D

Question 6 (Scenario)

A support agent retrieves outdated answers after documentation has been updated.

What should be investigated FIRST?

A. Trigger phrases

B. Azure AI Search index synchronization

C. Adaptive Card layout

D. Conversation variables

Answer: B

Explanation:
Knowledge freshness depends on indexing and synchronization.

Question 7 (Multiple Answer)

Which TWO situations justify using Child Agents?

A. Isolating reusable business capabilities

B. Delegating specialized business functions

C. Displaying images

D. Storing authentication credentials

Answers: A, B

Question 8 (Single Answer)

A conversation requires collecting multiple user inputs before submitting a service request.

Which feature provides the best user experience?

A. Trigger phrases

B. Adaptive Cards

C. Azure AI Search

D. Generative Answers

Answer: B

Question 9 (Scenario)

A company wants independent AI agents developed by external vendors to collaborate without exposing proprietary implementation details.

Which technology best addresses this requirement?

A. Power Automate

B. Child Agents

C. Agent2Agent protocol

D. Adaptive Cards

Answer: C

Question 10 (Single Answer)

Why should business transactions generally avoid relying solely on Generative Answers?

A. They require deterministic execution.

B. They cannot access SharePoint.

C. They require Adaptive Cards.

D. They cannot use connectors.

Answer: A

Question 11 (Multiple Answer)

An architect is designing a financial services agent.

Which TWO actions should require authenticated users?

A. Viewing account balances

B. Resetting passwords

C. Reading public FAQs

D. Viewing office hours

Answers: A, B

Question 12 (Single Answer)

Which capability provides semantic ranking across enterprise content?

A. Adaptive Cards

B. Azure AI Search

C. Topics

D. Power Automate

Answer: B

Question 13 (Scenario)

A parent agent delegates work to a child agent.

What should the child agent ideally return?

A. Raw API payloads only

B. Completed business result

C. Internal diagnostic logs

D. Azure Search indexes

Answer: B

Question 14 (Multiple Answer)

Which TWO characteristics describe REST API Tools?

A. Execute HTTP requests

B. Support authentication

C. Replace Azure AI Search

D. Eliminate connectors

Answers: A, B

Question 15 (Single Answer)

Which design principle minimizes duplicated business logic?

A. Long conversation topics

B. Reusable child agents

C. Multiple greeting topics

D. Static responses

Answer: B

Question 16 (Scenario)

A healthcare organization wants AI responses grounded only in approved clinical documentation.

Which solution is most appropriate?

A. Public web search

B. Azure AI Search over approved repositories

C. Trigger phrase expansion

D. Adaptive Cards

Answer: B

Question 17 (Fill in the Blank)

The ________ protocol standardizes interactions between AI agents developed by different vendors.

A. HTTPS

B. SOAP

C. Agent2Agent

D. TCP

Answer: C

Question 18 (Scenario)

A manufacturing agent should retrieve machine status from an operational system only after identifying the equipment number.

What should happen first?

A. Invoke the REST API immediately

B. Ask for equipment identification

C. Display an Adaptive Card after the API call

D. Perform Azure AI Search

Answer: B

Question 19 (Multiple Answer)

Which TWO activities improve conversation quality during testing?

A. Validate topic transitions

B. Verify connector responses

C. Disable analytics

D. Remove authentication

Answers: A, B

Question 20 (Single Answer)

Which statement best describes MCP?

A. A semantic search engine

B. A protocol for integrating external tools and services

C. A replacement for REST

D. A replacement for connectors

Answer: B

Question 21 (Scenario)

An enterprise agent must answer policy questions while ensuring responses always reference official documents.

What should you configure?

A. Static topics only

B. Generative Answers grounded on trusted knowledge sources

C. Adaptive Cards only

D. REST APIs

Answer: B

Question 22 (Single Answer)

Which capability allows agents to invoke hundreds of Microsoft and third-party applications with minimal development effort?

A. Connectors

B. Child Agents

C. Adaptive Cards

D. Azure AI Search

Answer: A

Question 23 (Multiple Answer)

Which TWO metrics are most valuable when evaluating production agents?

A. Resolution rate

B. Escalation frequency

C. CPU temperature

D. Tenant storage size

Answers: A, B

Question 24 (Scenario)

Several departments maintain their own specialized agents.

The organization wants each department to continue independent development while allowing seamless collaboration.

Which architecture should be recommended?

A. Single monolithic agent

B. Connected Agents

C. One large topic

D. Adaptive Cards

Answer: B

Question 25 (Single Answer)

Which benefit does modular topic design provide?

A. Easier reuse and maintenance

B. More trigger phrases

C. Higher API latency

D. Less testing

Answer: A

Question 26 (Match the Answers)

Match each technology with the appropriate scenario.

TechnologyScenario
1. Adaptive CardA. Interactive form
2. Azure AI SearchB. Enterprise document retrieval
3. REST API ToolC. Live business transaction
4. MCPD. Standardized external tool integration

Answer

  • 1 → A
  • 2 → B
  • 3 → C
  • 4 → D

Question 27 (Scenario)

A retail agent should automatically delegate shipping questions to a logistics agent while preserving conversation context.

Which feature best accomplishes this?

A. Connected Agents

B. Static Topics

C. Adaptive Cards

D. Azure AI Search

Answer: A

Question 28 (Multiple Answer)

Which TWO situations are appropriate for Azure AI Search grounding?

A. Large enterprise knowledge repositories

B. Frequently changing documentation

C. Live inventory lookup

D. Credit card authorization

Answers: A, B

Question 29 (Single Answer)

What is the primary objective of production monitoring?

A. Reduce document size

B. Identify failures and improve agent performance

C. Increase Adaptive Card complexity

D. Create additional topics

Answer: B

Question 30 (Scenario-Based)

A global enterprise is designing a Copilot Studio solution consisting of dozens of specialized agents maintained by separate teams. Customer conversations should begin with a single front-door agent, which delegates requests to specialized agents while preserving context. Enterprise documentation should be searchable using semantic and vector search, while live order status should always come directly from the ERP system.

Which architecture best satisfies these requirements?

A. Store all ERP data inside Azure AI Search.

B. Use one monolithic topic containing all business logic.

C. Use Connected Agents with Azure AI Search for knowledge retrieval and REST API Tools for live ERP transactions.

D. Replace Azure AI Search with Adaptive Cards.

Answer: C

Explanation:
This architecture separates static knowledge retrieval from transactional data access, enables scalable multi-agent collaboration through Connected Agents, and ensures that live business information is always retrieved directly from the source system rather than cached in a search index.

Go to the AB-620 Exam Prep Hub main page

AB-620, Agentic AI, AI, Azure AI, Microsoft Certification

Create and use environment variables (AB-620 Exam Prep)

 
This post is a part of the AB-620: Designing and Building Integrated AI Agent Solutions in Copilot Studio Exam Prep Hub.
This topic falls under these sections:
Test and manage agents (20–25%)
   --> Implement application lifecycle management (ALM) for agents in Copilot Studio
      --> Create and use environment variables (in Microsoft Copilot Studio)

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

As organizations move Copilot Studio agents from development to testing and production, many configuration settings change between environments. For example:

  • API endpoints
  • Azure AI Search service names
  • Azure OpenAI or Azure AI Foundry resources
  • Dataverse URLs
  • SQL Server connection information
  • SharePoint sites
  • REST API base URLs
  • Storage account names
  • Feature flags

Hardcoding these values into an agent or Power Automate flow creates deployment challenges because developers must manually edit every component for each environment.

Environment variables solve this problem by allowing configuration values to be stored separately from the application. Components reference the environment variable rather than a fixed value. When the solution is imported into another environment, only the environment variable needs to be updated.

For the AB-620 exam, you should understand:

  • What environment variables are
  • Why they are important for ALM
  • Types of environment variables
  • How to create them
  • How to use them in Copilot Studio
  • How they work with solutions
  • Their relationship to connection references
  • Best practices for deployment

What Are Environment Variables?

An environment variable is a reusable configuration setting stored within a Power Platform solution.

Instead of embedding configuration values directly into application components, the components reference an environment variable.

Example:

Instead of:

https://dev-api.contoso.com

An agent references:

API_BaseURL

Each environment supplies its own value.

Why Environment Variables Matter

Organizations usually have multiple environments:

  • Development
  • Test
  • User Acceptance Testing (UAT)
  • Staging
  • Production

Each environment typically uses different resources.

Example:

EnvironmentAPI URL
Developmenthttps://dev-api.contoso.com
Testhttps://test-api.contoso.com
Productionhttps://api.contoso.com

Without environment variables, every component would need to be edited during deployment.

With environment variables:

  • The solution remains unchanged.
  • Only the variable value changes.

Benefits of Environment Variables

Environment variables provide:

  • Easier deployments
  • Reusable configuration
  • Improved portability
  • Reduced manual work
  • Better governance
  • Fewer deployment errors
  • Cleaner application design
  • Improved ALM support

Environment Variables vs Hardcoded Values

Hardcoded Configuration

Agent

↓

https://dev-api.company.com

Problems:

  • Difficult migration
  • Manual editing
  • Error-prone
  • Poor ALM

Environment Variable Configuration

Agent

↓

API_URL

↓

Environment Variable

↓

Current Environment Value

Benefits:

  • Flexible
  • Reusable
  • Easy deployment

Common Uses

Environment variables commonly store:

  • REST API endpoints
  • Azure AI Search service names
  • Azure OpenAI endpoints
  • Azure AI Foundry endpoints
  • Azure Storage account names
  • Dataverse URLs
  • SharePoint URLs
  • Cosmos DB endpoints
  • SQL Server names
  • Feature toggles
  • Default language settings
  • Prompt configuration values

Types of Environment Variables

Power Platform supports two primary pieces of information:

Environment Variable Definition

The definition contains:

  • Variable name
  • Display name
  • Description
  • Data type
  • Default value

Example:

SearchServiceName

Environment Variable Value

The value changes by environment.

Development

contoso-search-dev

Testing

contoso-search-test

Production

contoso-search-prod

Supported Data Types

Environment variables support several data types.

Common types include:

  • Text
  • Decimal number
  • Two options (Boolean)
  • JSON
  • Data source
  • Secret (when integrated with Azure Key Vault)

The appropriate type depends on the configuration being stored.

Secrets and Azure Key Vault

Sensitive information should not be stored as plain text.

Examples include:

  • API keys
  • Client secrets
  • Access tokens
  • Passwords

Instead:

Environment Variable

↓

Azure Key Vault Secret

↓

Application

This approach improves security and simplifies secret rotation.

Creating an Environment Variable

General steps:

  1. Open the Power Apps Maker Portal.
  2. Open an unmanaged solution.
  3. Select New.
  4. Choose Environment Variable.
  5. Enter:
    • Display Name
    • Schema Name
    • Data Type
    • Default Value (optional)
  6. Save.

The variable is now available within the solution.

Using Environment Variables in Copilot Studio

Once created, environment variables can be referenced by:

  • Copilot Studio agents
  • Power Automate flows
  • Custom connectors
  • Plugins
  • Dataverse components
  • AI prompts
  • REST API tools
  • Azure integrations

Instead of storing a literal value, components reference the variable.

Example

Without environment variables:

REST API
https://dev-api.contoso.com/orders

With environment variables:

API_URL
↓
https://dev-api.contoso.com

The REST action builds the URL dynamically.

Environment Variables During Deployment

When exporting a solution:

Environment Variable Definition

↓

Solution Package

↓

Import

↓

Administrator enters Production Value

↓

Application works without modification

No changes to the agent are required.

Relationship to Solutions

Environment variables are solution components.

This means they:

  • Export with the solution
  • Import with the solution
  • Support versioning
  • Participate in ALM
  • Work with managed solutions
  • Work with Power Platform Pipelines

Environment Variables and Connection References

These concepts are commonly confused.

Environment Variables

Store:

Configuration values

Examples:

  • URL
  • Service name
  • Feature flag
  • Search index
  • Region

Connection References

Store:

Authentication information

Examples:

  • SQL connection
  • SharePoint connection
  • Dataverse connection
  • Outlook connection

Think of it this way:

Environment Variable = What system should be used?

Connection Reference = How do I authenticate to that system?

Working with Power Platform Pipelines

Power Platform Pipelines automatically support environment variables.

Deployment process:

Development

↓

Export Solution

↓

Pipeline

↓

Import

↓

Assign Production Variable Values

↓

Application Ready

No manual editing of the agent is required.

Versioning

Environment variables participate in solution versioning.

Example:

Version 1.0

SearchServiceName

Version 1.1

SearchServiceName
New Variable:
FeatureToggle

Both variables become part of the upgraded solution.

Common Mistakes

Hardcoding URLs

Instead of:

https://company-dev-api.com

Use:

API_URL

Storing Secrets as Text

Never place passwords directly into text variables.

Use Azure Key Vault integration whenever possible.

Duplicating Variables

Avoid creating multiple variables for the same setting.

Instead, reuse existing variables.

Poor Naming

Avoid names like:

Variable1

Prefer:

AzureSearchEndpoint

or

OrdersAPIBaseURL

Ignoring Default Values

Default values can simplify development and testing while allowing administrators to override values during deployment.

Best Practices

Microsoft recommends:

  • Create environment variables inside solutions.
  • Use descriptive names.
  • Use environment variables instead of hardcoded values.
  • Store secrets in Azure Key Vault.
  • Separate configuration from application logic.
  • Reuse variables whenever possible.
  • Document each variable.
  • Test variable values after deployment.
  • Use connection references for authentication.
  • Use environment variables for configuration settings.

Exam Tips

Know the difference between:

ConceptStores
Environment VariableConfiguration values
Connection ReferenceAuthentication information
Managed SolutionProduction deployment
Unmanaged SolutionDevelopment
Azure Key VaultSecrets

Remember:

Environment variables make solutions portable.

Real-World Example

A company builds a customer support agent that uses:

  • Azure AI Search
  • REST APIs
  • SharePoint
  • SQL Server

Instead of hardcoding configuration:

https://dev-search.azure.com
https://dev-orders-api.com
https://dev.sharepoint.com

The solution defines:

  • SearchServiceURL
  • OrdersAPI
  • SharePointSite

During deployment to production, administrators simply update the environment variable values without modifying the agent, topics, flows, or connectors.

Summary

Environment variables are a foundational ALM feature in Microsoft Power Platform and Copilot Studio. They allow developers to separate configuration settings from application logic, making solutions easier to deploy, maintain, and version across development, test, and production environments. By storing environment-specific values such as API endpoints, Azure AI Search resources, and feature flags in reusable variables, organizations reduce deployment errors and improve maintainability. Environment variables work alongside connection references, which manage authentication, while Azure Key Vault should be used for sensitive secrets.

Practice Exam Questions

Question 1

A Copilot Studio agent calls a REST API whose base URL is different in development, testing, and production. What is the recommended approach?

A. Create an environment variable for the API URL.

B. Hardcode all three URLs in the agent.

C. Create three separate agents.

D. Create separate topics for each environment.

Answer: A

Explanation: Environment variables allow configuration values such as API endpoints to vary by environment without modifying the agent.

Question 2

Which type of information is best stored in an environment variable?

A. OAuth access tokens

B. API base URLs

C. User conversation history

D. Dataverse records

Answer: B

Explanation: Environment variables are intended for configuration settings such as URLs, service names, and feature flags rather than runtime data or authentication tokens.

Question 3

What is the primary benefit of using environment variables?

A. They improve AI response quality.

B. They reduce token consumption.

C. They separate configuration values from application logic.

D. They automatically secure REST APIs.

Answer: C

Explanation: Separating configuration from application logic simplifies deployments and reduces maintenance.

Question 4

Which feature should be used to securely store sensitive information such as API secrets?

A. Text environment variables

B. Adaptive Cards

C. Power Automate variables

D. Azure Key Vault

Answer: D

Explanation: Azure Key Vault is the recommended service for securely storing secrets and can be integrated with Power Platform.

Question 5

What is the relationship between environment variables and solutions?

A. Environment variables cannot be included in solutions.

B. Environment variables are solution components and move with the solution.

C. Environment variables are created automatically during import.

D. Environment variables are only available in managed solutions.

Answer: B

Explanation: Environment variables are packaged within solutions and participate in ALM and deployment.

Question 6

Which statement correctly distinguishes environment variables from connection references?

A. Both store authentication credentials.

B. Environment variables store user conversations.

C. Environment variables store configuration values, while connection references store authentication information.

D. Connection references replace environment variables.

Answer: C

Explanation: Environment variables define configuration values, whereas connection references identify and manage authenticated connections.

Question 7

A developer hardcodes an Azure AI Search endpoint into an agent. What is the primary disadvantage?

A. The agent cannot use generative answers.

B. The endpoint must be manually updated when deploying to another environment.

C. The agent cannot be added to a solution.

D. The endpoint becomes encrypted automatically.

Answer: B

Explanation: Hardcoded values make deployments more difficult because they require manual changes for each environment.

Question 8

Which naming convention is considered a best practice for environment variables?

A. Variable1

B. Test123

C. Value

D. OrdersAPIBaseURL

Answer: D

Explanation: Descriptive names improve readability, maintenance, and long-term governance.

Question 9

When importing a managed solution into production, what typically happens with environment variables?

A. They are deleted automatically.

B. They cannot be modified.

C. Administrators provide production-specific values.

D. They are converted into connection references.

Answer: C

Explanation: During import, administrators typically assign values appropriate for the target environment.

Question 10

Which scenario is the best use case for an environment variable?

A. Storing the current user’s conversation transcript

B. Storing an Azure AI Search service name used by an agent

C. Storing Dataverse table records

D. Storing Power Automate execution history

Answer: B

Explanation: Azure AI Search service names are environment-specific configuration settings that are ideal candidates for environment variables.

Go to the AB-620 Exam Prep Hub main page