Microsoft Developing AI-Enabled Database Solutions (DP-800) · 37% practice allocation

Microsoft DP-800: Design and develop database solutions Practice Questions

5 free sample questions from the Design and develop database solutions domain, with answers and explanations. The full DP-800 bank has 232 questions across 3 domains. Questions are scenario-based and written from the official Microsoft exam guide.

You are developing a stored procedure in Microsoft Fabric SQL database. The procedure uses a TRY/CATCH block. Inside the CATCH block, the developer writes:

IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;
ELSE IF XACT_STATE() = 1
    ROLLBACK TRANSACTION;

After the rollback, the developer wants to re-raise the original error to the calling application. Which statement should be used?

A. RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
B. SELECT ERROR_MESSAGE() AS ErrorMessage;
C. THROW;✓ Correct
D. RETURN ERROR_NUMBER();

Explanation

THROW; (with no arguments) re-raises the original exception that was caught, preserving the original error number, severity, state, and message — which is the recommended modern T-SQL approach. RAISERROR with ERROR_MESSAGE() and ERROR_SEVERITY() re-raises a message but does NOT preserve the original error number, and its severity and state are not perfectly preserved either; additionally, severity 20+ requires WITH LOG. RETURN ERROR_NUMBER() simply returns a code to the caller without raising an actual SQL error condition. SELECT ERROR_MESSAGE() outputs the message as a result set, which does not signal an error to the calling application.

A data engineer is working in Microsoft Fabric SQL database. They need to query a table called Employees and return, for each employee, their salary and the running total of salaries ordered by HireDate within each DepartmentID. Which T-SQL construct correctly computes the running total?

A. SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY HireDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)✓ Correct
B. SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY HireDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
C. SUM(Salary) GROUP BY DepartmentID ORDER BY HireDate
D. SUM(Salary) OVER (ORDER BY HireDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Explanation

A running total requires a window frame that accumulates from the start of the partition up to the current row: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING computes a trailing sum (current row to end), not a running total. SUM with GROUP BY collapses rows and cannot return a per-row running total. The fourth option omits PARTITION BY DepartmentID, so the running total crosses department boundaries, and UNBOUNDED FOLLOWING makes it a grand total rather than a running total.

A developer is designing a table in Microsoft Fabric SQL database to store employee records. The table must track the historical state of each row over time so that queries can retrieve what a row looked like at any past point in time, without requiring custom application logic. Which table type should the developer use?

A. A temporal table with SYSTEM_TIME versioning✓ Correct
B. An in-memory optimized table with a natively compiled stored procedure
C. A standard heap table with a manually maintained audit trigger
D. A ledger table configured for updatable ledger rows

Explanation

Temporal tables (SYSTEM_TIME versioning) automatically maintain a history table and allow AS OF, BETWEEN, and CONTAINED IN time-travel queries with no custom application logic required. A heap table with an audit trigger requires custom logic and does not natively support time-travel syntax. A ledger table provides tamper-evidence and an audit trail but does not support the FOR SYSTEM_TIME AS OF query syntax for point-in-time row retrieval. In-memory optimized tables improve transaction throughput but have no built-in historical versioning capability.

A developer is building a solution in Azure SQL Database. They need a database object that accepts an OrderID parameter and returns a result set of related OrderLine rows, including computed discount amounts. The object must be joinable with other tables in a FROM clause and must support inline logic for best performance. Which object type should the developer create?

A. A scalar user-defined function that returns the discount amount for a single line
B. A multi-statement table-valued function (MTVF) that populates a table variable and returns it
C. An inline table-valued function (iTVF) defined with a single SELECT statement✓ Correct
D. A stored procedure with an OUTPUT parameter

Explanation

An inline table-valued function (iTVF) is defined with a single RETURN (SELECT ...) statement, is treated by the optimizer as a macro that gets expanded (inlined) into the calling query, supports parameters for filtering, and is directly usable in a FROM clause with CROSS APPLY or JOIN. This gives the best performance. A scalar UDF returns a single value, not a result set. An MTVF populates a table variable row-by-row and is treated as a black box by the optimizer, preventing statistics-based optimization (it assumes 1 row), leading to poor performance at scale. A stored procedure cannot be used directly in a FROM clause.

A data engineer is working in Azure SQL Database. They need to create a table called SensorLog that is optimized for extremely high-throughput INSERT operations (millions of rows per minute) with minimal locking contention. Reads will be infrequent and can tolerate slightly elevated latency. Which table type best satisfies these requirements?

A. A disk-based heap table with no indexes
B. A disk-based table with a clustered index on the timestamp column
C. A memory-optimized table (In-Memory OLTP) with DURABILITY = SCHEMA_AND_DATA
D. A memory-optimized table with DURABILITY = SCHEMA_ONLY✓ Correct

Explanation

A memory-optimized table with DURABILITY = SCHEMA_ONLY stores all data in memory and does not write to the transaction log or checkpoint files, eliminating I/O overhead entirely and providing the highest possible INSERT throughput. Because reads are infrequent and data loss on restart is acceptable for a sensor log (data can be re-ingested), SCHEMA_ONLY durability is appropriate. SCHEMA_AND_DATA memory-optimized tables provide durability but still incur log I/O, reducing throughput compared to SCHEMA_ONLY. A disk-based clustered table incurs index maintenance and page I/O on every insert. A disk-based heap avoids index maintenance but still incurs disk I/O and locking, making it far slower than in-memory alternatives.

Test yourself across every DP-800 domain

The free DP-800 practice test mixes about 20 questions weighted like the exam. The full 232-question bank adds instant grading, flashcards and a timed mock exam.

Take the free DP-800 practice test Practice the full bank

Study this domain

Design and develop database solutions study guide (DP-800) →

Other DP-800 domains

← All Microsoft Developing AI-Enabled Database Solutions study resources