Free Microsoft Azure SQL AI Developer Associate (DP-800) Sample Questions

Correct answer: B — Create a clustered columnstore index on the fact table

• A. Nonclustered rowstore indexes speed up seeks for small numbers of rows, but they do not help a workload dominated by large aggregations across most of the table, and maintaining many of them adds write overhead.
• B. Column-by-column storage with segment elimination and batch-mode execution is built for exactly this kind of large-scale scan and aggregation workload, and it delivers far higher compression than rowstore.
• C. A uniqueness constraint enforces data integrity on one column but provides no benefit for scan-heavy aggregation queries across billions of rows.
• D. A clustered rowstore index physically orders rows by date, which helps range filtering but still requires row-by-row processing for the wide aggregations described here.

Correct answer: A — Add a computed column using JSON_VALUE on the Brand path and index it

• A. Defining a computed column that extracts the Brand value with JSON_VALUE and indexing that computed column lets the optimizer seek directly on Brand instead of scanning the raw JSON text.
• B. Simply reformatting the storage type does not create any index structure the optimizer can use to avoid scanning every row for the Brand value.
• C. A CONVERT wrapper does not create an index and typically prevents index usage rather than enabling a seek.
• D. Switching to XML changes the data model entirely and is unnecessary; JSON data can be indexed efficiently via a computed column without abandoning the JSON format.

Correct answer: A — json

• A. The json data type parses and stores documents in an efficient binary form, avoiding repeated text parsing on every read and allowing targeted updates without rewriting the whole document.
• B. varchar stores JSON as plain text and requires the engine to re-parse the entire string on every access, which is the inefficiency the native type is designed to remove.
• C. xml is a separate markup format with its own type system and indexing; it is not the type introduced for efficient native JSON storage.

Correct answer: A — The scalar function executes once per row, effectively causing row-by-row processing

• A. Interpreted scalar UDFs invoked per row create implicit row-by-row execution, much like a cursor, and this per-row overhead dominates runtime across millions of rows.
• B. The function's presence in the SELECT list does not itself force a scan of the outer query's driving table; the dominant cost is repeated invocation, not access-path selection.
• C. SQL Server does not automatically cache scalar function results across rows in tempdb, so tempdb caching is not the source of this slowdown.
• D. Scalar UDF usage can restrict parallelism for the query that calls it, but it does not disable parallelism server-wide for other queries.

Correct answer: B — False

• A. Views cannot declare a parameter list; any filtering must be applied by querying the view with a WHERE clause afterward, not by passing arguments into it.
• B. This is correct: views have no parameter list, so achieving parameterized filtering requires an outer WHERE clause or switching to an inline table-valued function instead.

Correct answer: A — True

• A. This is correct: INSTEAD OF triggers work on both tables and views, whereas AFTER triggers are supported only on tables, not on views.
• B. This statement accurately reflects SQL Server's trigger placement rules, so marking it false would be incorrect.

Correct answers: A, B — REGEXP_SPLIT_TO_TABLE; REGEXP_MATCHES

• REGEXP_SPLIT_TO_TABLE. This is correct because this function splits an input string into pieces delimited by a regex pattern and returns each piece as a separate row in a table.
• REGEXP_MATCHES. This is correct because this function returns a table of every captured substring that matches the given pattern, producing one row per match found in the string.
• REGEXP_LIKE. This is incorrect because this function returns a single scalar boolean value indicating whether a match exists, not a set of rows.
• REGEXP_COUNT. This is incorrect because this function returns a single scalar integer representing the number of matches, not the matches themselves as rows.
• REGEXP_INSTR. This is incorrect because this function returns a single scalar integer position of a match, not a table of results.
• REGEXP_REPLACE. This is incorrect because this function returns a single scalar string with replacements applied, not a set of rows.

Correct answer: A — True

• True. This is correct because this anchor-plus-recursive-member structure joined by UNION ALL, with the recursive member self-referencing the CTE, is the required shape of a recursive CTE in T-SQL.
• False. This is incorrect because omitting either the anchor member, the recursive member, or the self-reference to the CTE name would result in an invalid or non-recursive query rather than a working recursive CTE.

Correct answer: A — True

• True. This is correct because this function is defined to return a count of the number of times the given regex pattern occurs within the string, which differs from a boolean existence check.
• False. This is incorrect because returning a boolean existence flag describes a different function; the count function specifically tallies every occurrence of the pattern.

Correct answer: A — %USERPROFILE%\.mcp.json

• A. Correct. The .mcp.json file under the user's profile is the global configuration file that makes configured MCP servers available for that user account across sessions.
• B. Incorrect. There is no ProgramData-based mcp-servers.json file used by SSMS; MCP configuration is read from the user profile's .mcp.json file.
• C. Incorrect. SQL Database Project files describe database schema for CI/CD, not MCP server configuration, and aren't read by Copilot Chat for this purpose.
• D. Incorrect. MCP servers are configured in a JSON file, not an XML settings file, and this path isn't the one SSMS watches for MCP configuration.

Correct answers: A, B, D — Review AI-generated code before executing it, especially statements that modify or delete data.; Grant the MCP server's underlying connection only the least privilege needed for its intended tools.; Avoid pasting production credentials or sensitive data directly into chat prompts.

• A. Correct. Reviewing generated code before execution, particularly data-modifying statements, catches unsafe or incorrect suggestions before they run against real data.
• B. Correct. Constraining the MCP connection to least privilege limits the damage if the AI issues an unintended or overly broad command.
• C. Incorrect. Confidence displayed by a model doesn't guarantee correctness or safety, so treating suggestions as automatically safe undermines the other safeguards.
• D. Correct. Keeping production credentials and sensitive data out of prompts prevents that information from being exposed to or retained by the AI service.
• E. Incorrect. Turning off auditing removes the record needed to investigate what an AI-assisted session actually did, increasing rather than reducing risk.
• F. Incorrect. Generated T-SQL still needs the same testing as hand-written code; training data doesn't guarantee correctness for a specific schema or workload.

Correct answer: A — Select Manage Models, then choose Azure as the provider and add the provisioned model.

• A. Correct. The model picker's Manage Models option lets you add a model from a custom provider. Choosing Azure as the provider brings in the model already provisioned in your own Azure OpenAI resource, so Copilot Chat can use it instead of a built-in model.
• B. Incorrect. The `.mcp.json` file configures MCP servers and the tools they expose to Copilot. It does not control which language model powers Copilot Chat, so it cannot redirect model requests to an Azure OpenAI endpoint.
• C. Incorrect. Renaming an Azure OpenAI deployment does not register it with Copilot or substitute it for a built-in model. The model must be added explicitly through the Manage Models workflow in the model picker.
• D. Incorrect. Ask mode is a chat interaction mode and does not perform network discovery of Azure-hosted models. Custom models are added deliberately through Manage Models in the model picker.

Correct answer: A — SCHEMABINDING ;

• A. Correct. `SCHEMABINDING` binds the view to the schema of the objects it references, so the Sales table's referenced columns cannot be dropped or altered in a way that breaks the view unless the view is dropped or altered first. This is exactly the protection the stem asks for, and it requires two-part object names such as `dbo.Sales` in the view definition.
• B. Incorrect. `WITH CHECK OPTION` makes sure that INSERT and UPDATE statements issued through the view still satisfy the view's WHERE clause. It does not lock the schema of the underlying Sales table, so columns could still be dropped or altered.
• C. Incorrect. `WITH ENCRYPTION` only obfuscates the view's definition text in system catalog views. It provides no protection against schema changes on the base table, so the view could still break.
• D. Incorrect. `WITH RECOMPILE` forces plan recompilation and is valid on stored procedures, not on views. It therefore cannot provide the requested schema protection.

Correct answer: A — Always Encrypted with secure enclaves

• A. Secure enclaves let the database engine process encrypted data inside a protected memory region, enabling pattern matching, richer comparison operators, and in-place encryption or re-encryption without moving data outside the database.
• B. Randomized encryption without enclaves blocks all computation on the encrypted values, including pattern matching and range comparisons, so this configuration would not support the required searches.
• C. Dynamic Data Masking only changes what appears in query results for nonprivileged users; it does not encrypt data or enable secure server-side computation on ciphertext.
• D. Row-Level Security filters which rows a user can see based on identity, but it has no role in enabling computation on encrypted column values.

Correct answers: A, B, D — Members of the db_owner role can view unmasked data without any additional grant; The UNMASK permission can be granted at the column, table, schema, or database level; Combined with Microsoft Entra ID, UNMASK access can be managed for users, groups, and applications

• A. Users with administrative rights such as server admin, Microsoft Entra admin, and the db_owner role automatically see original, unmasked data without needing a separate UNMASK grant.
• B. UNMASK can be granted or revoked at multiple scopes, from a single column up through table, schema, and database level, giving fine-grained control over who bypasses masking.
• C. Because masking policies operate only on the result set returned by a query, they can be layered on top of Microsoft Entra-managed identities so that unmask access follows the same central identity governance as other Azure services.
• D. Because masking policies operate only on the result set returned by a query, they can be layered on top of Microsoft Entra-managed identities so that unmask access follows the same central identity governance as other Azure services.
• E. This is not accurate. Dynamic Data Masking is a result-set obfuscation feature, not a comprehensive data-loss-prevention control, so it must be paired with proper access control and auditing rather than relied on alone.
• F. This is not accurate. Dynamic Data Masking is independent of Always Encrypted and can be configured on any eligible column without any prior encryption feature being enabled.

Correct answer: A — inaccessible

• A. Data API builder denies access by default, so any entity without explicit permissions defined for a role is completely unreachable through the REST or GraphQL endpoints for that role.
• B. This describes the opposite of Data API builder's secure-by-default design; no read access is granted automatically without an explicit permissions entry.
• C. This is inaccurate because an entity with no permissions block has no configured actions at all, including no write access, not merely restricted read access.

Correct answer: A — Delete the rows in small batches using a loop with TOP(n) and a WHERE filter, committing each batch

• A. Correct. Breaking a large delete into small, committed batches keeps each transaction's lock footprint small and short-lived, avoiding lock escalation to the table level and minimizing blocking of concurrent OLTP activity.
• B. Incorrect. Running the full delete as one large SERIALIZABLE transaction increases lock scope and duration, making blocking of concurrent sessions worse, not better.
• C. Incorrect. A schema modification lock blocks all concurrent access to the table for DDL purposes and is not an appropriate or available hint for reducing blocking during row deletes.
• D. Incorrect. The transaction log cannot be disabled in Azure SQL Database, and doing so would eliminate crash recovery guarantees rather than address blocking.

Correct answer: A — deadlock victim

• A. Correct. The deadlock monitor selects one of the participating transactions as this and terminates it, returning error 1205 to the client so the other transaction can proceed.
• B. Incorrect. A head blocker is the session at the root of a blocking chain that is not itself waiting on anyone else, a different concept from the transaction chosen to be killed in a deadlock.
• C. Incorrect. An orphaned connection refers to a client session that disconnected without cleanly releasing its resources, which is unrelated to deadlock victim selection.

Correct answer: A — Add Build Remove and None Include entries for each referenced file

• A. Correct. Using Build Remove excludes the referenced file from schema compilation, and None Include keeps it visible in the project as a plain file, which is the documented pattern for files pulled in via :r includes.
• B. Incorrect. A project supports exactly one PostDeploy entry; the :r include mechanism inside that single script is how multiple seed files are chained, not by declaring each as its own PostDeploy item.
• C. Incorrect. Folder location has no effect on build compilation behavior; placing a data script in the Tables folder would not stop it from being compiled as a schema object.
• D. Incorrect. SqlCmdVariable entries define token replacement values used inside scripts; they do not control whether a file is excluded from schema compilation.

Correct answer: A — microsoft.sqlpackage

• A. Correct. `dotnet tool install --global microsoft.sqlpackage` is the documented command to install SqlPackage as a cross-platform .NET global tool on Windows, Linux, or macOS.
• B. Incorrect. microsoft.dacpacverify installs the DacpacVerify tool for comparing two dacpac files, not the SqlPackage deployment tool itself.
• C. Incorrect. Microsoft.Build.Sql is the NuGet package/SDK referenced inside an SDK-style .sqlproj file for building the project; it is not installed as a global CLI tool via this command.
• D. Incorrect. azure/sql-action is a GitHub Actions marketplace action referenced in workflow YAML, not a package installed with dotnet tool install.

Correct answer: A — cache with enabled true, ttl-seconds 30, and level L1L2

• A. Correct. Setting a 30-second time-to-live with the L1L2 level enables both the in-memory cache and the distributed cache tier, so cached responses are shared consistently across scaled-out DAB instances.
• B. Incorrect. The L1 level caches only in-memory per instance and is not shared across scaled-out replicas, so different instances could return stale or inconsistent cached results.
• C. Incorrect. Restricting REST methods controls which HTTP verbs are allowed on the entity but has no effect on response caching or reducing database load.
• D. Incorrect. Setting the GraphQL operation type only affects whether a stored-procedure entity appears under Query or Mutation in the schema; it does not enable caching.

Correct answer: A — Azure Kubernetes Service

• A. Correct. Azure Kubernetes Service is the container orchestration platform among DAB's documented deployment options that manages scaling, load balancing, and health of multiple container replicas in production.
• B. Incorrect. Running a single Docker container locally is intended for development and testing, not for orchestrating scaled-out production replicas behind a load balancer.
• C. Incorrect. Building and running DAB from source on one machine is a development workflow and provides no built-in orchestration or scaling across replicas.
• D. Incorrect. Air-gapped deployment addresses installing DAB without internet access; it describes a network constraint, not a scaling or orchestration mechanism.

Correct answer: A — Log Analytics workspace

• A. Correct. A Log Analytics workspace is the centralized data store where KQL queries can be run across telemetry from multiple resources, with configurable retention and alert rules, making it the right target for cross-resource correlation.
• B. Incorrect. Live Metrics offers a low-latency, real-time view of current activity for a single Application Insights resource, but it is not designed for long-term retention or cross-resource KQL analysis.
• C. Incorrect. Azure Advisor provides cost, security, and performance recommendations based on resource configuration; it does not ingest or query application telemetry.
• D. Incorrect. Azure Policy enforces and audits resource configuration compliance; it has no role in collecting or querying application request telemetry.

Correct answers: A, B — Enable READ_COMMITTED_SNAPSHOT so readers use row versions kept in tempdb instead of shared locks, removing read-write blocking on busy tables.; Break large write transactions into smaller batches and commit sooner to shorten lock hold duration and reduce contention on hot rows and pages.

• A. Correct. With READ_COMMITTED_SNAPSHOT, readers use row versions instead of taking shared locks, so they no longer block behind writers and lock waits drop on busy tables. The version store lives in the database (with accelerated database recovery) or in tempdb, so confirm that the added versioning load fits your capacity.
• B. Correct. Breaking large writes into smaller batches that commit sooner shortens how long locks are held. That reduces blocking and lock waits on hot rows and pages, and it keeps long transactions from holding resources such as tempdb space and the version store.
• C. Incorrect. Geo-replicas can offload read-only reporting queries, but they don't change how long write transactions hold locks on the primary. They also do nothing for tempdb contention on the primary, so the lock waits from long writes remain.
• D. Incorrect. Collation affects how strings are compared and sorted, not how locks are acquired or held. A case-insensitive collation doesn't skip any work, and changing the collation of every column is a disruptive change that doesn't address blocking or tempdb contention.
• E. Incorrect. Automatic tuning detects regressions and can force the last known good plan, and it doesn't cause the lock waits or tempdb contention described. Disabling it removes a performance safeguard without shortening the write transactions that cause the blocking.

Correct answer: A — Configure OpenID Connect with azure/login using a service principal's client ID, tenant ID, and subscription ID as federated credentials.

• A. Correct. With OpenID Connect, `azure/login` exchanges a short-lived token from the pipeline provider for an Azure access token. That exchange relies on a federated credential on the service principal, and the client ID, tenant ID, and subscription ID are only identifiers, not secrets. No password or client secret is stored anywhere, so the stored password is removed.
• B. Incorrect. Base64 is a reversible encoding, not encryption, so anyone with access to the encoded secret can recover the password immediately. The pipeline still has to decode and use the same SQL password at connect time. The stored password is still there, and the change adds an extra secret to manage.
• C. Incorrect. Putting the plaintext password in a YAML comment commits it to source control, where everyone with repository access can read it, including through its history. This makes security worse. The password also still exists and is still used to build the connection string, so it is not removed.
• D. Incorrect. A second SQL login with a shorter password still depends on a stored password in the secret store, so the pipeline authenticates with a password just as before. A shorter password is also weaker, not stronger. Only a passwordless method such as federated credentials removes the password.

Correct answer: A — A fields array aliasing sku_title to title and flagging id as key.

• A. Correct. The `fields` array is the unified DAB 2.0 mechanism for describing columns. Each entry can set `alias` to expose `sku_title` as `title` and set `primary-key` to `true` to mark `id` as the key, which replaces the older `mappings` and `key-fields` settings.
• B. Incorrect. Both `mappings` and `source.key-fields` are deprecated in DAB 2.0. The schema also does not allow them to be combined with the newer `fields` array on the same entity, so this is not the approach to use.
• C. Incorrect. A `relationships` object defines how one entity links to another entity, for example for GraphQL navigation. It cannot rename a column such as `sku_title` or designate `id` as the primary key.
• D. Incorrect. A permissions policy controls which fields a role can access, or filters rows with database predicates. It has no way to rename a column for API consumers or to flag a primary key, and it applies to a specific role such as `anonymous` rather than to the entity's shape.

Correct answer: A — 50 characters

• A. Correct. OVERLAP is a percentage applied to CHUNK_SIZE to determine how many characters carry over, and 25 percent of 200 characters is 50 characters.
• B. This would be the case only if OVERLAP were treated as an absolute character count rather than a percentage of CHUNK_SIZE, which is not how the parameter is defined.
• C. This value represents the non-overlapping remainder of the chunk, not the portion that is repeated at the start of the next chunk.
• D. This would mean the entire chunk repeats, which would only happen at 100 percent overlap, not the 25 percent specified here.

Correct answer: A — True

• A. Correct. CDC stores every change to a monitored table in dedicated change tables, preserving full change history, while Change Tracking is lighter-weight and only surfaces the latest net state of changed rows.
• B. Incorrect. CDC is documented as more comprehensive than Change Tracking precisely because it preserves full change history rather than just the latest net change.

Correct answer: A — sys.external_models

• A. Correct. This catalog view exposes metadata about registered external model objects, including their configuration, to principals with access to a given model.
• B. This catalog view lists database scoped credentials used for authentication, not the external model objects or their configured API_FORMAT and MODEL_TYPE.
• C. This dynamic management view shows currently executing requests on the instance; it has no relationship to reporting on registered external models.

Correct answers: A, B, C — cosine; euclidean; dot

• A. Cosine distance measures the angle between two vectors and is a supported metric for both VECTOR_DISTANCE and CREATE VECTOR INDEX.
• B. Euclidean distance measures straight-line distance between vector endpoints and is one of the officially supported metrics.
• C. Dot product (returned as a negative value so smaller means closer) is a supported metric used for similarity scoring in these functions.
• D. Manhattan (L1/taxicab) distance is not one of the metrics exposed by VECTOR_DISTANCE or CREATE VECTOR INDEX in SQL Server or Azure SQL.
• E. Jaccard similarity is used for comparing sets, not dense numeric embedding vectors, and is not a supported metric for these T-SQL vector functions.

Correct answer: A — EXECUTE ANY EXTERNAL ENDPOINT

• A. EXECUTE ANY EXTERNAL ENDPOINT is the specific database permission required to run sp_invoke_external_rest_endpoint, matching the principle of least privilege for this scenario.
• B. CONTROL SERVER grants near-complete server-wide rights and far exceeds what's needed just to invoke a REST endpoint.
• C. ALTER ANY CREDENTIAL allows managing credential objects but doesn't by itself authorize execution of the REST endpoint procedure.
• D. VIEW SERVER STATE only allows viewing server state metadata and DMVs; it grants no ability to execute the REST call procedure.

Correct answer: B — 100 MB

• A. 10 MB understates the documented limit for the payload size.
• B. 100 MB is the documented cap on payload size, both sent and received, once UTF-8 encoded for transmission.
• C. 1 GB overstates the documented limit; payloads of that size would exceed the enforced cap.

Correct answer: A — RRF sums reciprocal rank contributions from each list, so a very high rank (small rank number) in either list produces a large 1/(k+rank) term that can dominate the combined score.

• A. Correct. RRF sums a `1/(k+rank)` term from each list, so a document ranked near the top (a small rank number) in either list gets a large reciprocal term. That term can dominate the fused score even when the other list ranks the document much lower.
• B. Incorrect. RRF does not build in a fixed 2x weight for vector search over full-text search. The formula treats each list's rank contribution the same way unless you deliberately add custom weights. An equally high rank in either list therefore contributes equally.
• C. Incorrect. RRF does not discard documents that appear in only one list. A fusion query typically uses a full outer join with `COALESCE` and a zero default, so a document present in just one list still gets a partial score from that list. Dominance by a high rank therefore doesn't depend on a document being in both lists.
• D. Incorrect. RRF works on rank positions, not raw distance or relevance scores, so it needs no 0-1 normalization. Avoiding normalization across dissimilar full-text and vector scoring scales is its main advantage. Score magnitudes therefore can't be what causes one list to dominate.

Correct answer: A — The statement fails because a vector index requires at least 100 rows with non-NULL vectors to build.

• A. Correct. `CREATE VECTOR INDEX` requires at least 100 rows with non-NULL vector values in the column. With only 40 qualifying rows, the statement fails with an explicit error instead of building an index.
• B. Incorrect. The engine does not build a vector index from fewer rows than the minimum. It enforces the 100-row requirement and returns an error, so the statement does not succeed with the 40 available rows.
• C. Incorrect. SQL Server never inserts placeholder or synthetic zero-vector rows to meet the minimum row requirement. The developer must supply enough real rows with non-NULL vector data before the index can be created.
• D. Incorrect. `CREATE VECTOR INDEX` and full-text indexing are unrelated features. No fallback substitutes a full-text index when the row minimum isn't met, and the statement fails instead of succeeding silently.

Correct answers: A, B, D — Answering support questions by grounding responses in product documentation stored in SQL tables.; Summarizing a customer's recent order history from a transactional table for a support chatbot.; Grounding chatbot answers in an internal knowledge base without retraining the underlying model.

• A. Correct. Answering support questions from product documentation stored in SQL tables is a classic RAG scenario. Relevant passages are retrieved at query time and passed to the model as context, so its answers are grounded in your own content.
• B. Correct. Retrieving a customer's recent orders from a transactional table and passing them to the model as context is a valid RAG use case. It combines current data from the database with the model's language ability to produce an accurate summary for the support conversation.
• C. Incorrect. Training a new foundation model from scratch is a separate and far more expensive undertaking than RAG. RAG reuses an existing pretrained model and supplies retrieved data as context at query time, so it involves no model training.
• D. Correct. Grounding chatbot answers in an internal knowledge base without retraining the model is the core value of RAG. The model draws on current, proprietary data supplied at query time, with no fine-tuning step needed.
• E. Incorrect. Always Encrypted is a security feature that protects the confidentiality of sensitive columns, including from DBAs. It does not retrieve data or ground model responses, so it is not a RAG use case.
• F. Incorrect. Replacing all OLTP indexing strategies with vector search for a high-write transactional workload is a database design choice, not a RAG use case. Vector search complements traditional indexes for similarity retrieval and does not replace them for transactional access patterns.