Developing AI-Enabled Database Solutions · 37% of the exam

Design and develop database solutions: free practice questions

5 sample questions from our 93-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Microsoft exam.

1. 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: ```sql 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. THROW;✓ Correct
  • C. RETURN ERROR_NUMBER();
  • D. SELECT ERROR_MESSAGE() AS ErrorMessage;
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.

2. You are building a compliance system in Microsoft Fabric SQL database. You need a table that automatically records the full history of every change made to employee salary records, including the exact time period each version was valid, without requiring application-level logic. Which table type satisfies this requirement?

  • A. A ledger table configured in append-only mode
  • B. A system-versioned temporal table with a linked history table✓ Correct
  • C. An in-memory optimized table with a natively compiled stored procedure
  • D. A graph table with edge constraints recording change events
Explanation

A system-versioned temporal table automatically maintains a history table that records every row version along with `SysStartTime` and `SysEndTime` columns, capturing precisely when each version was valid — entirely managed by the database engine without application changes. Option A (ledger append-only) provides tamper-evidence via blockchain-style hashing but does not automatically store prior versions with time periods; it records all INSERTs but does not automatically snapshot UPDATE/DELETE history by validity period. Option C (in-memory optimized table) is designed to reduce lock contention and latency for high-throughput OLTP; it has no built-in history or time-travel capability. Option D (graph table) models entity relationships as nodes and edges; it has no native mechanism for tracking historical versions of row data over time.

3. 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.

4. A developer is writing a query in Microsoft Fabric SQL database to identify potential duplicate customer records by comparing last names. The business requirement is to flag pairs of customers whose last names are highly similar but not necessarily identical (e.g., 'Smith' vs 'Smyth'). The dataset contains approximately 2 million customer records. Which function or approach is MOST appropriate for this task?

  • A. Use SOUNDEX() combined with DIFFERENCE() to group phonetically similar last names.
  • B. Use JARO_WINKLER_DISTANCE() to compute a similarity score between last name pairs and filter on a threshold.✓ Correct
  • C. Use EDIT_DISTANCE() and flag pairs where the distance equals zero.
  • D. Use REGEXP_LIKE() with a pattern that matches common name variants.
Explanation

Option B is correct: JARO_WINKLER_DISTANCE returns a normalized similarity score (0–1) that is specifically designed for short strings like names and is well-suited for fuzzy matching of name variants like 'Smith'/'Smyth'; filtering on a threshold (e.g., >= 0.92) correctly identifies near-duplicates. Option A is wrong because SOUNDEX/DIFFERENCE is a coarse phonetic algorithm that produces many false positives and misses non-phonetic variations; it is also not a Fabric-native fuzzy match function. Option C is wrong because EDIT_DISTANCE equal to zero means the strings are identical, which is exact matching, not fuzzy matching. Option D is wrong because REGEXP_LIKE matches against a fixed pattern; it cannot generically identify arbitrary near-duplicate name pairs without a pre-enumerated list of variants.

5. You are writing a query in Microsoft Fabric SQL database against a graph database. The nodes table is called Person and the edges table is called Follows. You need to find all people that a given user (UserID = 42) follows who also follow that user back (mutual follows). Which query fragment correctly uses the MATCH clause for this pattern?

  • A. WHERE MATCH(p1-(follows)->p2) AND p1.UserID = 42
  • B. WHERE MATCH(p1-(follows)->p2 AND p2-(follows)->p1) AND p1.UserID = 42
  • C. WHERE MATCH(p1-(follows)->p2-(follows)->p1) AND p1.UserID = 42✓ Correct
  • D. WHERE MATCH(SHORTEST_PATH(p1(-(follows)->p2)+)) AND p1.UserID = 42
Explanation

Option C uses the correct MATCH syntax for a cyclic graph pattern: p1 follows p2, and p2 follows p1, expressed as a single chain p1-(follows)->p2-(follows)->p1. This correctly models mutual follows. Option A only checks that p1 follows p2 but does not verify the reverse relationship. Option B is syntactically invalid—AND inside the MATCH clause is not a supported construct in T-SQL graph MATCH; multiple patterns are expressed as a chain or by referencing the same nodes. Option D uses SHORTEST_PATH, which is for finding paths of variable length and is not appropriate for detecting a simple two-hop cycle between known nodes.

88 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free