Microsoft Certified: SQL AI Developer Associate — Practice Set 1

Question 1

A developer needs to store product reviews in an Azure SQL database. The reviews arrive as JSON documents and must be queryable by specific JSON properties. Which column type should the developer use to store these documents while still enabling JSON-specific indexing?

Accepted Answer

Azure SQL and SQL Server support a native JSON column type that can be paired with a JSON index to enable efficient querying of JSON properties. NVARCHAR(MAX) with a computed column (A) is the legacy approach that predates the native JSON type and does not support JSON-native indexing. VARBINARY(MAX) (C) stores binary data and is not queryable via JSON functions. XML columns (D) use a completely different data format and indexing mechanism that does not apply to JSON.

Question 2

A database architect needs to ensure that historical data in an Orders table is automatically retained so that queries can retrieve the state of any row as it existed at any point in the past two years. Which specialized table type satisfies this requirement with the least custom development effort?

Accepted Answer

Temporal tables automatically maintain a history table with system-managed start/end time columns, allowing point-in-time queries using the FOR SYSTEM_TIME clause. In-memory optimized tables (A) improve performance for OLTP workloads but do not track row history. Ledger tables (C) provide tamper-evident history for compliance but are not designed for general point-in-time retrieval using time-travel syntax. External tables (D) reference data outside the database and have no built-in row history.

Question 3

A developer needs to write a T-SQL query that calculates a running total of sales per customer, ordered by sale date, without using a GROUP BY clause that collapses rows. Which T-SQL construct should be used?

Accepted Answer

Window functions with an OVER clause allow aggregate calculations (like running totals) to be performed across a set of rows related to the current row without collapsing the result set. A correlated subquery (A) can compute running totals but is much less efficient and more complex. A scalar function with a loop (C) is a cursor-like anti-pattern that performs poorly at scale. A CTE with HAVING (D) filters grouped aggregates, which collapses rows rather than preserving individual row detail.

Question 4

A developer is working on a SQL database in Microsoft Fabric and wants to use GitHub Copilot Chat to help write a complex stored procedure. Before starting, the team's security officer needs to understand the data exposure risks. Which security consideration is most important when using GitHub Copilot with database code in this context?

Accepted Answer

When using AI-assisted tools like GitHub Copilot, the prompt context — which may include schema definitions, table names, column names, sample data, or inline comments — is sent to the language model. This represents a potential data exposure risk that security officers must evaluate before enabling Copilot with sensitive database code. Copilot does not apply Always Encrypted to its prompts (A). RLS does not need to be disabled for Copilot to function (C). Copilot suggestions are not guaranteed secure and must be reviewed for SQL injection and other vulnerabilities (D).

Question 5

A developer is configuring a GitHub Copilot session to assist with writing T-SQL for a Microsoft Fabric SQL database. They want to connect to additional context sources. Which two actions can the developer take to extend GitHub Copilot's context using Model Context Protocol (MCP)? Choose 2.

Accepted Answer

The DP-800 skills guide explicitly lists 'Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse' as a measured skill. Connecting to these MCP endpoints gives Copilot live schema and data context to improve code generation quality. Configuring model and MCP tool options (B) is a related skill but refers to settings within the session rather than the act of connecting to specific endpoints. Dynamic data masking (C) is a security feature unrelated to MCP connectivity. Replacing Copilot (D) is out of scope. Option E is specifically named in the skill bullet.

Question 6

A developer needs to write a T-SQL query to find all product names that are within an edit distance of 2 from a user-supplied search string, to support fuzzy search functionality. Which function should the developer use?

Accepted Answer

EDIT_DISTANCE() is a T-SQL function specifically designed to compute the Levenshtein edit distance between two strings, making it ideal for fuzzy string matching based on character-level differences. SOUNDEX() (A) encodes strings by their phonetic sound rather than character similarity, which is not the same as edit distance. DIFFERENCE() (B) compares SOUNDEX values and returns a similarity score of 0-4, not an edit distance. PATINDEX() (D) returns the starting position of a pattern in a string and does not compute edit distance.

Question 7

A developer needs to model a social network where users can follow other users in a SQL database. Queries will frequently ask 'find all friends of friends within 3 hops'. Which table type and query pattern is most appropriate?

Accepted Answer

SQL Server and Azure SQL support graph tables (NODE and EDGE table types) and the MATCH operator, which is purpose-built for traversing graph relationships like 'friends of friends' across multiple hops with concise syntax. Recursive CTEs with self-join (A) can express graph traversal but are more verbose, harder to optimize, and not as naturally suited to arbitrary-depth traversal. Temporal tables (C) track row history over time and are unrelated to graph relationships. In-memory tables (D) improve OLTP performance but do not simplify graph query patterns.

Question 8

A developer is building a stored procedure that performs multiple DML operations as a batch. If any single statement fails, the entire batch should be rolled back and a meaningful error message returned to the caller. Which T-SQL construct achieves this?

Accepted Answer

A TRY...CATCH block is the standard T-SQL error handling mechanism. The DML statements run inside TRY; if any error occurs, execution jumps to CATCH where ROLLBACK TRANSACTION undoes all changes and THROW or RAISERROR returns the error to the caller. GOTO (A) can be made to work but is error-prone and not the modern recommended pattern. Checking @@ERROR (C) is the old T-SQL pattern; it does not catch all error severities and is fragile. RAISERROR at the start (D) cannot pre-empt future runtime failures.

Question 9

A developer is writing a T-SQL query to extract all order line items from a JSON column named OrderDetails. Each row's OrderDetails value contains a JSON array of objects with properties 'ProductId', 'Quantity', and 'UnitPrice'. The developer needs each array element to appear as a separate row in the result set. Which function is the correct choice?

Accepted Answer

OPENJSON() is the T-SQL table-valued function that parses a JSON string and returns its contents as rows and columns, making it ideal for shredding a JSON array into individual relational rows. The WITH clause defines the output schema. JSON_VALUE() (A) extracts a single scalar value from a JSON path — it does not expand arrays into rows. JSON_QUERY() (B) extracts a JSON fragment (object or array) but does not convert it to rows. JSON_ARRAYAGG() (D) aggregates multiple rows into a JSON array — the opposite of what is needed here.

Question 10

A developer needs to create a GitHub Copilot instruction file to guide Copilot's behavior when generating T-SQL code for their project. Where should this instruction file be placed in a GitHub repository?

Accepted Answer

GitHub Copilot custom instruction files are placed in the .github directory and named copilot-instructions.md. This is the standard, recognized location that GitHub Copilot reads automatically when providing suggestions in that repository. A .copilot-instructions.txt at the root (A) is not the recognized file name or location for this feature. A /instructions subfolder (C) is not the standard Copilot convention. Repository secrets (D) are for encrypted environment variables, not instruction content.

Question 11

A compliance team requires that the SocialSecurityNumber column in a Customers table is never readable in plaintext by database administrators or the application layer — decryption must happen only on the client side. Which encryption feature should be implemented?

Accepted Answer

Always Encrypted is designed so that SQL Server never sees plaintext for the encrypted columns — encryption and decryption happen exclusively in the client driver using keys that the server never holds. TDE (A) encrypts data at rest on disk but does not protect against DBAs or privileged server-side access seeing plaintext. Column-level encryption with ENCRYPTBYPASSPHRASE (B) encrypts values in the database but decryption still occurs server-side with T-SQL, so a DBA can decrypt it. Dynamic Data Masking (D) obfuscates data in query results but does not encrypt it — a privileged user can still unmask the data.

Question 12

A developer wants to ensure that users in the Sales role can only see rows in the Orders table where the SalesRepId matches their own database login. Other users should be blocked from seeing those rows entirely. Which security feature should be implemented?

Accepted Answer

Row-Level Security (RLS) enforces access at the row level by binding a security predicate function to a table; every query against that table is automatically filtered based on the predicate, regardless of how the query is constructed. Dynamic Data Masking (A) hides data values in query results but does not prevent rows from being returned. A view (C) can filter rows but can be bypassed if the user has direct table access, and it does not scale as cleanly as RLS. DENY on the whole table (D) would block the user from accessing any rows at all, not just rows for other sales reps.

Question 13

An Azure SQL database is experiencing sudden query slowdowns. The DBA suspects blocking. They need to identify long-running transactions that are blocking other sessions and view the query text responsible for the blocking head. Which tool or view is most appropriate for this real-time investigation?

Accepted Answer

Dynamic Management Views (DMVs) like sys.dm_exec_requests expose real-time session-level data including blocking_session_id, wait_type, and the query text via sys.dm_exec_sql_text — exactly what is needed to diagnose active blocking in real time. Query Store (A) retains historical query plans and statistics but does not show live blocking information. Reviewing an execution plan in the Query Editor (C) shows how a single query would run, not which sessions are currently blocking each other. Query Performance Insight (D) shows aggregated historical data, not real-time blocking chains.

Question 14

A development team is setting up a CI/CD pipeline for an Azure SQL database using SQL Database Projects. They want to prevent schema drift and control deployments through pull request approvals. Which two capabilities of SQL Database Projects directly support these requirements? Choose 2.

Accepted Answer

SQL Database Projects include built-in schema drift detection that compares the project model against the live database, alerting the team when the database schema has diverged from source control. Deployment pipeline controls (branching policies, code owners, approval gates) are a direct DP-800 skill that enforces pull request approval before schema changes are deployed. Always Encrypted (C) is a data security feature unrelated to schema drift detection. Dynamic Data Masking (D) is a runtime data obfuscation feature, not a schema control mechanism. Configuring source control (E) is foundational but does not by itself detect drift or enforce approvals.

Question 15

A developer needs to expose data from an Azure SQL database as a REST API without writing custom API code. The API should support filtering, pagination, and searching. Which Azure service should be used?

Accepted Answer

Data API builder (DAB) is specifically designed to expose SQL database entities as REST and GraphQL endpoints with minimal configuration — it supports filtering, pagination, and searching out of the box through configuration files and entity definitions. Azure API Management (A) is a gateway that manages existing APIs; it does not generate REST endpoints directly from SQL schemas. Azure Logic Apps (C) is a workflow automation tool, not an API generation platform. An Azure Function with EF Core (D) works but requires significant custom code — the opposite of 'without writing custom API code'.

Question 16

A developer needs to react to every INSERT on a SQL database table and immediately generate an embedding for the new row and store it in a separate vector table, keeping it in sync with near-zero latency. Which change-tracking mechanism is best suited for this row-level, event-driven embedding pipeline?

Accepted Answer

Azure Functions with SQL trigger binding is an event-driven mechanism that fires a function in near real-time when rows are inserted, updated, or deleted in a SQL table, making it ideal for immediately generating and storing embeddings on each new row. A batch job using Change Tracking (B) introduces latency proportional to the polling interval — near-zero latency is not achievable. CDC replicated nightly (C) introduces 24-hour latency, which is far from near-zero. Query Store (D) captures query performance data, not a row-change event trigger for downstream processing.

Question 17

A developer is configuring a Data API builder (DAB) deployment and needs to ensure the service authenticates to Azure SQL without storing a username or password in any configuration file. Which authentication approach should be configured?

Accepted Answer

Passwordless authentication using Managed Identity eliminates stored credentials entirely. The DAB host (App Service, Container App, etc.) receives an Azure AD token automatically, and Azure SQL is configured to grant access to that identity. Storing credentials in the config file (A) violates secrets management best practices and creates credential exposure risk. SQL Server Authentication with rotation (C) still relies on a password that must be stored and managed securely. Injecting a password via GitHub Actions secrets (D) reduces human exposure but still means a password exists and must be managed.

Question 18

A team is implementing a CI/CD pipeline for a SQL Database Project in GitHub Actions. They want to ensure that only reviewed, approved code reaches the production database and that all deployment secrets are protected. Which two practices should they implement? Choose 2.

Accepted Answer

Secrets management (A) ensures database credentials are stored in a secure vault (e.g., GitHub encrypted secrets or Azure Key Vault) rather than in plain text in workflow files — a direct DP-800 CI/CD skill. Branching policies with code owners (C) enforce that pull requests must be reviewed and approved before schema changes are deployed to production, preventing unauthorized changes. XACT_ABORT ON (B) is a useful session setting but is not a CI/CD pipeline control. Granting db_owner broadly (D) violates least-privilege principles. Storing connection strings in clear text (E) is a critical security violation.

Question 19

A developer has exposed a stored procedure through Data API builder (DAB) as a GraphQL mutation. They need to ensure the DAB service itself and any external clients can be secured at the endpoint level. Additionally, the team uses Azure Monitor for observability. Which combination of actions should the developer take?

Accepted Answer

DP-800 skills include both 'Secure GraphQL, REST, and MCP endpoints' and 'Recommend Azure Monitor configurations, including Application Insights and Log Analytics'. Securing the GraphQL endpoint (via authentication policies in DAB configuration) and integrating Application Insights for telemetry is the correct, layered approach. Disabling GraphQL (B) is unnecessary and reduces functionality. Exposing SQL directly (C) eliminates all the security and abstraction benefits of DAB. Using Always Encrypted on return values (D) addresses data-at-rest confidentiality but does not secure the GraphQL endpoint itself.

Question 20

A developer wants to generate vector embeddings for product descriptions stored in an Azure SQL database. Before choosing an embedding model, they need to evaluate several external models for this use case. Which factor is most critical when evaluating models for generating text embeddings that will be stored and queried in the database?

Accepted Answer

When storing embeddings in a database vector column, the vector dimensions produced by the model must exactly match the size configured for the vector column. A mismatch causes storage or query errors. If embeddings are generated with a 1536-dimension model but the column is defined for 768 dimensions, the solution breaks. Image generation capability (A) is irrelevant for text embeddings. SQL query generation (C) is an unrelated model capability. Training dataset release date (D) may affect knowledge quality but is not the critical technical factor for embedding compatibility with a database column.

Question 21

A developer is implementing semantic vector search in Azure SQL. They have generated embeddings for a product catalog and stored them in a vector column. The search query must find the top 10 most semantically similar products to a user's query embedding. Which function and pattern should be used?

Accepted Answer

VECTOR_SEARCH() and VECTOR_DISTANCE() are the T-SQL functions for semantic vector similarity search. VECTOR_DISTANCE() computes a distance metric (cosine, dot product, Euclidean) between two vectors, and ordering results ascending by distance returns the most similar items. FREETEXT() (A) performs full-text keyword search, not semantic vector search — it does not use embeddings. SOUNDEX() (C) is a phonetic algorithm for string similarity, not vector similarity. EDIT_DISTANCE() (D) computes character-level string distance, not semantic embedding distance.

Question 22

A developer is building a Retrieval-Augmented Generation (RAG) application using Azure SQL. After retrieving relevant chunks from the database via vector search, they need to send those results to an external language model endpoint from within T-SQL. Which stored procedure enables this?

Accepted Answer

sp_invoke_external_rest_endpoint is the T-SQL stored procedure that allows Azure SQL to make HTTP REST calls to external endpoints — including language model APIs — directly from T-SQL. This is explicitly listed in the DP-800 RAG skill: 'Create a prompt by using the sp_invoke_external_rest_endpoint stored procedure'. sp_execute_external_script (A) runs external scripts (R, Python) in SQL Server Machine Learning Services, not arbitrary REST calls. sp_send_dbmail (C) sends email messages. sp_executesql (D) executes dynamic T-SQL strings, not external HTTP calls.

Question 23

A developer is designing the chunking strategy for a RAG application built on Azure SQL. The source data consists of lengthy technical manuals stored as text in a SQL table. The developer must balance retrieval precision with context completeness. Which chunking approach is most appropriate?

Accepted Answer

Fixed-size overlapping chunks balance context completeness (overlap prevents important information from being split across chunk boundaries) and retrieval precision (smaller chunks produce more focused embeddings that match narrow queries better). Storing entire manuals as one chunk (A) generates very coarse embeddings that perform poorly on specific queries and may exceed model context limits. SOUNDEX grouping (C) is a phonetic algorithm with no relevance to semantic chunking. A single embedding for the table name (D) provides no meaningful semantic signal about the content.

Question 24

A developer is designing a hybrid search system in Azure SQL that combines full-text search and vector (semantic) search results into a single ranked result list. Which two components are required to merge and rank results from both search methods? Choose 2.

Accepted Answer

Hybrid search combines keyword (full-text) search and semantic (vector) search. Full-text search via FREETEXT() or CONTAINSTABLE() (B) provides the keyword-based ranked result set. Reciprocal rank fusion (RRF) (A) is the standard algorithm for merging and re-ranking results from multiple ranked lists into a single unified ranking — explicitly listed as a DP-800 hybrid search skill. Dynamic Data Masking (C) is a security feature unrelated to search ranking. OPENJSON() (D) is for parsing JSON arrays, not vector similarity. Graph tables (E) are for relationship traversal, not search result merging.

Question 25

A developer needs to choose between Approximate Nearest Neighbor (ANN) and Exact Nearest Neighbor (ENN) search for a product recommendation feature in Azure SQL. The product catalog has 10 million rows. Query response time must be under 200ms. Which search mode should be used and why?

Accepted Answer

At 10 million rows, ENN (brute-force exhaustive scan) would require comparing every vector in the table, making sub-200ms response impossible. ANN uses a vector index (such as HNSW or IVF-based structures) to retrieve approximate nearest neighbors in logarithmic time, achieving very low latency at the cost of a small, acceptable precision trade-off. ENN (A) is impractical at 10M rows for real-time queries. Full-text search (C) does not perform semantic similarity and would not find semantically related products. A 'hash index for ENN' (D) is not a real Azure SQL vector search capability; hash indexes are used for equality lookups, not nearest-neighbor search.

DP-800 — SQL AI Developer Associate