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

Implement AI capabilities in database solutions: free practice questions

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

1. A team is building a RAG solution in Azure SQL Database. After retrieving relevant chunks via vector search, they need to pass the structured relational data to an Azure OpenAI model as context. How should the developer format the retrieved rows before including them in the prompt payload sent via sp_invoke_external_rest_endpoint?

  • A. Serialize the rows as CSV text using BCP format directives
  • B. Convert the rows to JSON using FOR JSON PATH or FOR JSON AUTO✓ Correct
  • C. Pass the rows as a binary VARBINARY stream
  • D. Export the rows to an Azure Blob Storage parquet file and reference the URL
Explanation

Azure OpenAI models expect text-based context in the prompt. The idiomatic T-SQL approach is to use FOR JSON PATH or FOR JSON AUTO to serialize relational result sets into JSON strings, which can then be embedded directly in the JSON request body constructed for sp_invoke_external_rest_endpoint. CSV via BCP format directives is not a native T-SQL serialization method and would require external tooling; moreover, LLMs process JSON-structured context far more reliably than raw CSV. A VARBINARY binary stream is a raw binary format that cannot be embedded in a JSON text payload and is not interpretable by the language model. Exporting to Azure Blob Storage parquet adds unnecessary latency, requires external storage access, and the model cannot read parquet files directly — it needs text in the prompt.

2. A data engineer is troubleshooting poor recall in a production VECTOR_SEARCH query in Azure SQL Database. The vector index was created six months ago on a table that has had approximately 40% of its rows inserted or updated since then. The engineer suspects the index quality has degraded. Which action is most likely to restore search recall without rebuilding the entire index from scratch?

  • A. Run UPDATE STATISTICS on the table to refresh the query optimizer's statistics about the vector distribution.
  • B. Drop and recreate the full-text index on the same table, as full-text and vector indexes share the same internal structure.
  • C. Reorganize or rebuild the vector index to incorporate the newly inserted and updated vectors into the index graph, restoring graph connectivity and recall quality.✓ Correct
  • D. Increase the TOP N parameter in the VECTOR_SEARCH query to retrieve more candidates and compensate for index drift without touching the index.
Explanation

Option C is correct. ANN vector indexes (such as graph-based DiskANN indexes) can experience recall degradation over time as many rows are inserted or updated after the initial index build, because new vectors are added incrementally and the graph structure may not be fully optimized for the evolved data distribution. Reorganizing or rebuilding the vector index reincorporates all current vectors and restores optimal graph connectivity and recall. Option A is wrong — `UPDATE STATISTICS` refreshes cardinality statistics used by the query optimizer for traditional query plans; it has no effect on the internal graph structure of a vector index or its recall quality. Option B is wrong — full-text indexes and vector indexes are entirely separate index types with different internal structures; rebuilding the full-text index does not affect vector search recall. Option D is wrong — increasing TOP N retrieves more candidates but does not fix the root cause (degraded index quality); it is a workaround that increases latency and is not equivalent to restoring index quality.

3. A data engineer wants to enable automatic embedding refresh in Azure SQL Database using Change Data Capture (CDC) together with Azure Functions. Which statement correctly describes the role of CDC in this architecture?

  • A. CDC directly triggers an Azure Function via a push notification whenever a row changes in the source table.
  • B. CDC writes row-level change records (inserts, updates, deletes) to system change tables; the Azure Function polls these tables on a schedule to identify which rows need their embeddings regenerated.✓ Correct
  • C. CDC stores the new embedding vectors in a shadow table so the Azure Function can copy them to the main embeddings table.
  • D. CDC compresses changed rows into a binary diff format that the Azure Function must decode before calling the embedding model.
Explanation

Option B is correct: CDC captures DML changes (inserts, updates, deletes) and writes them to dedicated change tables in the database. An Azure Function (typically timer-triggered or event-driven via a CDC-aware connector) polls these tables to discover which rows have changed and then calls the embedding model for those rows only. Option A is wrong because CDC does not push notifications to Azure Functions; the Function must poll or be triggered by a separate mechanism like Azure Event Hubs or a timer. Option C is wrong because CDC does not store embeddings—it stores change records (before/after images of the changed row data). Option D is wrong because CDC stores full column values (not binary diffs) in a human-readable relational format.

4. An AI engineer is implementing a RAG (Retrieval-Augmented Generation) solution entirely within Azure SQL Database. After performing a vector search to retrieve relevant document chunks, the engineer needs to call an Azure OpenAI chat completion endpoint to generate the final answer. Which stored procedure should be used to invoke the REST endpoint from T-SQL?

  • A. sp_execute_external_script
  • B. sp_invoke_external_rest_endpoint✓ Correct
  • C. sp_send_dbmail
  • D. sp_OACreate
Explanation

sp_invoke_external_rest_endpoint is the purpose-built stored procedure in Azure SQL Database for calling external HTTP REST APIs — including Azure OpenAI endpoints — directly from T-SQL, making it ideal for RAG pipelines that stay within the database. sp_execute_external_script is used to run external scripts in languages like Python or R via SQL Server Machine Learning Services; it does not directly call HTTP REST endpoints. sp_send_dbmail sends email via Database Mail and has no capability to call REST APIs. sp_OACreate instantiates OLE Automation objects, which is an older Windows-specific mechanism not available in Azure SQL Database and not designed for REST API calls.

5. A company is evaluating when to use full-text search versus semantic vector search versus hybrid search in Azure SQL Database. Match each scenario to the most appropriate search type. Which of the following statements correctly pairs scenarios with search types? (Select TWO.)

  • A. A legal department searches contracts for the exact phrase 'force majeure clause' — use semantic vector search because legal terms have precise embeddings.
  • B. A customer support portal retrieves articles that are conceptually related to 'my device won't turn on,' even if the articles use different wording like 'power failure' — use semantic vector search.✓ Correct
  • C. A product catalog search must return results ranked by both keyword relevance and conceptual similarity for short ambiguous queries — use hybrid search with RRF.✓ Correct
  • D. A compliance system must find all rows containing the exact regulatory code 'REG-2024-07B' — use semantic vector search for high-precision regulatory term matching.
  • E. An e-commerce site needs to rank product listings only by sales rank, with no text relevance signal — use full-text search with a RANK BY clause.
Explanation

Options B and C are correct. B is correct: 'device won't turn on' vs. 'power failure' is a classic semantic equivalence scenario where vector search excels by capturing meaning beyond exact keywords. C is correct: hybrid search with RRF is ideal when queries are short and ambiguous, because combining keyword and semantic signals improves overall ranking quality. Option A is wrong: searching for an exact phrase like 'force majeure clause' is a keyword task best served by full-text search, not vector search, which may dilute exact-phrase precision. Option D is wrong: an exact regulatory code like 'REG-2024-07B' is a lookup task—full-text or even a simple LIKE/equality query is more reliable than semantic search, which might return conceptually related but non-matching codes. Option E is wrong: ranking only by sales rank requires no text search at all; using full-text search would introduce irrelevant text scores.

39 more questions in this domain

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

Start practicing free
Implement AI capabilities in database solutions — Free Developing AI-Enabled Database Solutions Practice Questions | DataCertPrep — Certification Prep