CertSafari

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

    35 free sample questions from our bank of 352+, covering every exam domain, with answers and detailed explanations. Updated August 2026.

    Practice all 352 questions in quiz mode

    Domain 1: Design and develop database solutions

    Subdomain 1.1: Design and implement database objects

    1.A data engineering team stores 900 million sales transaction rows in Azure SQL Database. Nightly reports run SUM() and AVG() aggregations that scan most of the table with very few single-row lookups. Which index should the team create on this fact table to best support the workload?

    1. A.Create nonclustered rowstore indexes on every foreign key column
    2. B.Create a clustered columnstore index on the fact table
    3. C.Create a unique constraint on the transaction ID column
    4. D.Create a clustered rowstore index on the transaction date column
    Show answer & explanation

    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.

    Subdomain 1.1: Design and implement database objects

    2.Reports frequently filter products by a 'Brand' value nested inside a JSON column, and the query plan shows an expensive full scan of the column on every execution. Which approach improves this filtering performance while keeping the data stored as JSON?

    1. A.Add a computed column using JSON_VALUE on the Brand path and index it
    2. B.Convert the entire JSON column to an nvarchar(max) column with no formatting
    3. C.Wrap every query in a CONVERT function to force an index seek
    4. D.Replace the JSON column with an XML column and use XML indexes instead
    Show answer & explanation

    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.

    Subdomain 1.1: Design and implement database objects

    3.The native ___ data type stores JSON documents in an internal binary format, improving read and write performance compared to storing the same documents in nvarchar(max).

    1. A.json
    2. B.varchar
    3. C.xml
    Show answer & explanation

    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.

    Subdomain 1.2: Implement programmability objects

    4.A scalar UDF that queries a large lookup table is called from the SELECT list of a query returning two million rows. Performance is far worse than an equivalent inline computation. What is the most likely cause?

    1. A.The scalar function executes once per row, effectively causing row-by-row processing
    2. B.The scalar function forces the outer query to use a table scan instead of an index seek
    3. C.The scalar function's results are cached in tempdb, and tempdb latency is the bottleneck
    4. D.Scalar functions referenced in the SELECT list disable parallelism for the entire server
    Show answer & explanation

    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.

    Subdomain 1.2: Implement programmability objects

    5.A view joins two tables and is therefore not directly updatable through a normal DML statement. Applications still need to run INSERT statements against this view. What should be created to redirect the insert logic to the correct base tables?

    1. A.An INSTEAD OF INSERT trigger on the view
    2. B.An AFTER INSERT trigger on the view
    3. C.A DDL trigger scoped to the database
    4. D.WITH CHECK OPTION added to the view
    Show answer & explanation

    Correct answer: A — An INSTEAD OF INSERT trigger on the view

    • A. This trigger type intercepts the INSERT statement issued against the view and lets custom logic distribute the insert manually across the joined base tables.
    • B. SQL Server does not support this trigger type on views; only INSTEAD OF triggers can be defined on a view.
    • C. This trigger type fires on schema-change events like CREATE or ALTER and has nothing to do with row-level INSERT statements against a view.
    • D. This clause only validates that rows modified through the view still satisfy its filter condition; it does not enable inserts against a non-updatable multi-table view.

    Subdomain 1.2: Implement programmability objects

    6.Views in SQL Server can accept input parameters the same way stored procedures do, allowing a caller to pass a value directly into the view's WHERE clause.

    1. A.True
    2. B.False
    Show answer & explanation

    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.

    Subdomain 1.2: Implement programmability objects

    7.INSTEAD OF triggers can be defined on both tables and views, while AFTER triggers can only be defined on tables.

    1. A.True
    2. B.False
    Show answer & explanation

    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.

    Subdomain 1.3: Write advanced T-SQL code

    8.Scenario: A log table stores raw text lines such as "ERROR 4042: connection timeout", and a report needs only the four-digit error code extracted as its own column. Which function directly returns the substring matching a pattern like \d{4}, without a separate position lookup step?

    1. A.REGEXP_SUBSTR(LogLine, '\d{4}')
    2. B.REGEXP_INSTR(LogLine, '\d{4}')
    3. C.REGEXP_COUNT(LogLine, '\d{4}')
    4. D.REGEXP_LIKE(LogLine, '\d{4}')
    Show answer & explanation

    Correct answer: A — REGEXP_SUBSTR(LogLine, '\d{4}')

    • REGEXP_SUBSTR(LogLine, '\d{4}'). This is correct because this function extracts and returns the actual matching substring for the given pattern, which here is the four-digit error code itself.
    • REGEXP_INSTR(LogLine, '\d{4}'). This is incorrect because that function returns only the numeric starting or ending character position of the match, not the matched text itself.
    • REGEXP_COUNT(LogLine, '\d{4}'). This is incorrect because that function returns how many times the pattern occurs in the string, which does not help retrieve the actual code value.
    • REGEXP_LIKE(LogLine, '\d{4}'). This is incorrect because that function returns only a boolean indicating whether a match exists somewhere in the string, not the substring content.

    Subdomain 1.3: Write advanced T-SQL code

    9.Scenario: A developer needs to split a comma-separated list stored in a single column into multiple rows, and separately needs to return every regular expression match in a string, not just the first one, as multiple rows. Which two regex functions return a table of rows rather than a single scalar value? (Select all that apply.)(Select 2)

    1. A.REGEXP_SPLIT_TO_TABLE
    2. B.REGEXP_MATCHES
    3. C.REGEXP_LIKE
    4. D.REGEXP_COUNT
    5. E.REGEXP_INSTR
    6. F.REGEXP_REPLACE
    Show answer & explanation

    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.

    Subdomain 1.3: Write advanced T-SQL code

    10.True or False: A recursive common table expression must contain at least one anchor member and at least one recursive member, combined using UNION ALL, where the recursive member references the CTE name itself.

    1. A.True
    2. B.False
    Show answer & explanation

    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.

    Subdomain 1.3: Write advanced T-SQL code

    11.Scenario: A developer uses a regex count function with the pattern '[0-9]+' to determine how many separate numeric substrings appear in a product description. True or False: This function returns the total number of times the pattern matches within the string, not just whether it matches at least once.

    1. A.True
    2. B.False
    Show answer & explanation

    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.

    Subdomain 1.4: Design and implement SQL solutions by using AI-assisted tools

    12.A developer wants to manually add a locally running MCP server that communicates over standard input/output rather than HTTP. When filling out the custom MCP server dialog in the Copilot Chat Tools panel, what should the developer provide for the Type?

    1. A.A stdio connection with the command and any arguments needed to launch the server process
    2. B.An HTTP connection pointing at the server's loopback IP address
    3. C.A named pipe connection configured through SQL Server Configuration Manager
    4. D.A WebSocket connection using the server's process ID as the endpoint
    Show answer & explanation

    Correct answer: A — A stdio connection with the command and any arguments needed to launch the server process

    • A. Correct. For servers that communicate over standard input/output, the dialog expects a stdio type along with the command and arguments needed to launch the server process.
    • B. Incorrect. HTTP connections are for servers reachable over a URL; a locally launched stdio process isn't addressed by an IP-based HTTP endpoint.
    • C. Incorrect. Named pipes configured through SQL Server Configuration Manager control SQL Server's own client protocols, not how Copilot launches an MCP server.
    • D. Incorrect. The custom MCP server dialog doesn't offer a WebSocket type keyed on a process ID; only HTTP and stdio connection types are supported.

    Subdomain 1.4: Design and implement SQL solutions by using AI-assisted tools

    13.A team commits a .github/copilot-instructions.md file to their repository so Copilot tailors its SQL suggestions to their standards. A developer clones the repository into SSMS, but Copilot Chat responses never reflect the file's guidance. What is most likely missing?

    1. A.The developer hasn't enabled loading instructions from .github/copilot-instructions.md files in Tools > Options > GitHub > Copilot > Copilot Chat.
    2. B.The file must be renamed to Database-Instructions.md before Copilot can read it.
    3. C.Custom instruction files only apply to Ask mode, not to Agent mode sessions.
    4. D.The repository must be marked as public before Copilot Chat will load its instruction files.
    Show answer & explanation

    Correct answer: A — The developer hasn't enabled loading instructions from .github/copilot-instructions.md files in Tools > Options > GitHub > Copilot > Copilot Chat.

    • A. Correct. Loading custom instructions from a repository's .github/copilot-instructions.md file requires enabling the corresponding setting in Copilot Chat options; without it the file is simply ignored.
    • B. Incorrect. The file must keep the exact name .github/copilot-instructions.md; renaming it would prevent Copilot from finding it rather than fix the problem.
    • C. Incorrect. Custom instructions aren't restricted to a single chat mode; the missing step is enabling the setting that loads the file, not the chat mode being used.
    • D. Incorrect. Repository visibility has no bearing on whether Copilot Chat loads a local repository's instruction file; private repositories work the same way once the setting is enabled.

    Subdomain 1.4: Design and implement SQL solutions by using AI-assisted tools

    14.Which practices reduce the security risk of using AI-assisted tools like GitHub Copilot when writing SQL against production systems? (Select all that apply.)(Select 3)

    1. A.Review AI-generated code before executing it, especially statements that modify or delete data.
    2. B.Grant the MCP server's underlying connection only the least privilege needed for its intended tools.
    3. C.Treat every AI suggestion as inherently safe once the model shows high confidence.
    4. D.Avoid pasting production credentials or sensitive data directly into chat prompts.
    5. E.Disable auditing on databases that Copilot Agent mode can access, to reduce noise.
    6. F.Assume generated T-SQL never needs testing because the model was trained on SQL Server documentation.
    Show answer & explanation

    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.

    Domain 2: Secure, optimize, and deploy database solutions

    Subdomain 2.1: Implement data security and compliance

    15.An application needs to run LIKE pattern-matching searches and range comparisons directly against an encrypted column, and also wants to re-encrypt that column in place during key rotation without exporting data out of the database. Which capability satisfies both requirements?

    1. A.Always Encrypted with secure enclaves
    2. B.Always Encrypted using randomized encryption only, without enclaves
    3. C.Dynamic Data Masking applied to the same column
    4. D.Row-Level Security applied to the same column
    Show answer & explanation

    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.

    Subdomain 2.1: Implement data security and compliance

    16.A security team is designing granular Dynamic Data Masking access for a customer table. Which of the following statements about the DDM permission model are correct? (Select all that apply.)(Select 3)

    1. A.Members of the db_owner role can view unmasked data without any additional grant
    2. B.The UNMASK permission can be granted at the column, table, schema, or database level
    3. C.Masking changes the actual stored data value in the underlying table
    4. D.Combined with Microsoft Entra ID, UNMASK access can be managed for users, groups, and applications
    5. E.Dynamic Data Masking alone guarantees protection against all methods of data exfiltration, including bulk export
    6. F.Dynamic Data Masking requires Always Encrypted to already be configured on the same column
    Show answer & explanation

    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.

    Subdomain 2.1: Implement data security and compliance

    17.A developer forgets to add a permissions block for the Orders entity in dab-config.json. Because Data API builder is secure by default, the entity becomes ____ to all roles.

    1. A.inaccessible
    2. B.fully readable
    3. C.writable but not readable
    Show answer & explanation

    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.

    Subdomain 2.2: Optimize database performance

    18.An OLTP application on Azure SQL Database is experiencing frequent blocking between readers and writers on a heavily updated table. The team wants readers to see a transactionally consistent snapshot of data without acquiring shared locks, while keeping the default READ COMMITTED isolation level in application code. Which database-level configuration should they enable?

    1. A.Enable READ_COMMITTED_SNAPSHOT so SELECT statements read row versions instead of taking shared locks
    2. B.Change the default isolation level to SERIALIZABLE to enforce strict consistency
    3. C.Enable lock escalation to the table level to reduce the number of locks taken
    4. D.Add NOLOCK query hints to every SELECT statement in the application
    Show answer & explanation

    Correct answer: A — Enable READ_COMMITTED_SNAPSHOT so SELECT statements read row versions instead of taking shared locks

    • A. Correct. Turning on the READ_COMMITTED_SNAPSHOT database option makes READ COMMITTED queries read a versioned snapshot of committed data instead of acquiring shared locks, which removes reader/writer blocking while application code and isolation level syntax stay unchanged.
    • B. Incorrect. SERIALIZABLE takes the most restrictive locks, including key-range locks, which would increase blocking rather than reduce it.
    • C. Incorrect. Forcing table-level lock escalation reduces lock memory overhead but makes concurrent readers and writers block each other more, not less.
    • D. Incorrect. NOLOCK hints avoid locks but allow dirty reads of uncommitted data, which does not provide transactionally consistent results as required.

    Subdomain 2.2: Optimize database performance

    19.When SQL Server detects a circular lock wait between two transactions, it resolves the situation by choosing one transaction as the ___ and rolling it back with error 1205.

    1. A.deadlock victim
    2. B.head blocker
    3. C.orphaned connection
    Show answer & explanation

    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.

    Subdomain 2.2: Optimize database performance

    20.A retail analytics team runs nightly batch reports directly against the production Azure SQL Database that also serves live order entry. Reports occasionally cause order entry transactions to wait noticeably. The team cannot change the report queries but wants to reduce blocking of order entry writers without changing the default READ COMMITTED isolation level used elsewhere in the app. Which single configuration change addresses this with the least application impact?

    1. A.Verify and enable the READ_COMMITTED_SNAPSHOT database option so report reads use row versions instead of shared locks
    2. B.Move all reporting queries to run inside explicit BEGIN TRAN / COMMIT blocks
    3. C.Increase the LOCK_TIMEOUT setting for the reporting connections
    4. D.Rebuild all clustered indexes on the tables used by the reports
    Show answer & explanation

    Correct answer: A — Verify and enable the READ_COMMITTED_SNAPSHOT database option so report reads use row versions instead of shared locks

    • A. Correct. Since this option is already the intended default behavior for Azure SQL Database and directly removes the need for shared locks on reads, confirming it is enabled resolves reader/writer blocking without any application code changes.
    • B. Incorrect. Wrapping reporting queries in explicit transactions does not by itself change locking behavior and could increase lock duration if not scoped carefully.
    • C. Incorrect. Increasing lock timeout only changes how long a session waits before erroring out; it does not reduce the underlying blocking against order entry writers.
    • D. Incorrect. Rebuilding indexes can improve query performance and reduce scan duration somewhat, but it does not eliminate the fundamental shared-lock-versus-exclusive-lock conflict causing blocking.

    Subdomain 2.3: Implement CI/CD by using SQL Database Projects

    21.A DBA needs to deploy the same .dacpac to 100 tenant databases as part of a nightly job, applying only the incremental changes needed for each. Which SqlPackage capability makes this safe to run repeatedly?

    1. A.Publish calculates the difference between the dacpac and each target and generates only the required ALTER statements
    2. B.Extract recreates every object from scratch on each target database every run
    3. C.Script always drops and recreates the entire target database before applying changes
    4. D.Import loads the dacpac as raw data rows into a staging table for manual review
    Show answer & explanation

    Correct answer: A — Publish calculates the difference between the dacpac and each target and generates only the required ALTER statements

    • A. Correct. The Publish action compares the source dacpac against the live target schema and emits only the ALTER/CREATE statements needed to close the gap, making repeated runs against many databases idempotent and safe.
    • B. Incorrect. Extract pulls a live database's schema out into project files; it is used for reverse-engineering or drift detection, not for pushing changes into many target databases.
    • C. Incorrect. Script only generates the T-SQL that Publish would run; it does not execute a full drop-and-recreate, and Publish itself performs targeted incremental changes, not destructive rebuilds.
    • D. Incorrect. There is no SqlPackage action that loads a dacpac as data rows; a dacpac is a compiled schema model, not a data payload for staging tables.

    Subdomain 2.3: Implement CI/CD by using SQL Database Projects

    22.A post-deployment script references three separate seed-data files using SQLCMD :r includes. What must be done in the .sqlproj file so the build process does not try to compile those referenced files as schema objects?

    1. A.Add Build Remove and None Include entries for each referenced file
    2. B.Add each referenced file as an additional PostDeploy entry
    3. C.Move each referenced file into the Tables folder so it's treated as data
    4. D.Add a SqlCmdVariable entry for each referenced file path
    Show answer & explanation

    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.

    Subdomain 2.3: Implement CI/CD by using SQL Database Projects

    23.Which of the following are true characteristics of SDK-style SQL database projects built on Microsoft.Build.Sql? (Select 3)(Select 3)

    1. A.They support .NET 8+ builds that run cross-platform on Windows, Linux, and macOS
    2. B.They use NuGet package references for database references
    3. C.They apply a default globbing pattern that automatically picks up new .sql files
    4. D.They are fully generally available in Visual Studio 2026
    5. E.They allow SQLCLR objects to build entirely on .NET 8 without .NET Framework
    Show answer & explanation

    Correct answers: A, B, C — They support .NET 8+ builds that run cross-platform on Windows, Linux, and macOS; They use NuGet package references for database references; They apply a default globbing pattern that automatically picks up new .sql files

    • A. Correct. SDK-style projects target .NET 8+, which is why they can build on Linux and macOS CI runners in addition to Windows, unlike the original MSBuild-based format.
    • B. Correct. SDK-style projects use NuGet package references for database references, aligning dependency management with the rest of the .NET ecosystem.
    • C. Correct. SDK-style projects apply a default globbing pattern for .sql files, so a new file dropped into the project folder is picked up automatically without a manual project-file entry.
    • D. Incorrect. Visual Studio 2026 supports only the original SQL project format; SDK-style project support is in preview in Visual Studio 2022, not GA in Visual Studio 2026.
    • E. Incorrect. SQLCLR objects are supported in SDK-style projects but still require .NET Framework to build, not pure .NET 8, making this the one functional exception.

    Subdomain 2.4: Integrate SQL solutions with Azure services

    24.A catalog API built with Data API builder exposes a Products entity with heavy read traffic and infrequent updates. To reduce database load, the team wants responses served from memory for 30 seconds before a fresh query is made, shared across multiple scaled-out DAB instances. Which entity-level configuration achieves this?

    1. A.cache with enabled true, ttl-seconds 30, and level L1L2
    2. B.cache with enabled true, ttl-seconds 30, and level L1
    3. C.rest with methods restricted to get only
    4. D.graphql with operation set to query
    Show answer & explanation

    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.

    Subdomain 2.4: Integrate SQL solutions with Azure services

    25.A monitoring team wants to run KQL queries across DAB request traces, correlate them with other Azure resource logs, and set up long-term retention and alerting rules in a centralized workspace, rather than viewing telemetry only in an application-scoped dashboard. Which Azure Monitor component should the DAB telemetry ultimately be routed to?

    1. A.Log Analytics workspace
    2. B.Application Insights Live Metrics
    3. C.Azure Advisor
    4. D.Azure Policy
    Show answer & explanation

    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.

    Subdomain 2.4: Integrate SQL solutions with Azure services

    26.Which of the following statements about Data API builder deployment options and cache levels are correct? (Select 2)(Select 2)

    1. A.The L1L2 cache level shares cached responses across scaled-out DAB instances using a distributed cache
    2. B.Azure Kubernetes Service is a documented deployment target for orchestrating scaled DAB containers
    3. C.The L1 cache level automatically replicates cached data to every other running DAB instance
    4. D.DAB configuration files cannot be split across multiple JSON files
    5. E.Running DAB from source code is intended as a permanent production hosting model
    Show answer & explanation

    Correct answers: A, B — The L1L2 cache level shares cached responses across scaled-out DAB instances using a distributed cache; Azure Kubernetes Service is a documented deployment target for orchestrating scaled DAB containers

    • A. Correct. The L1L2 cache level combines the in-memory tier with a distributed cache tier, which is what allows cached entity responses to remain consistent across multiple scaled-out DAB instances.
    • B. Correct. Azure Kubernetes Service appears among DAB's documented deployment options specifically for orchestrating and scaling containerized DAB replicas in production.
    • C. Incorrect. The L1 level is in-memory only and is explicitly not shared across instances, so it does not replicate cached data to other running replicas.
    • D. Incorrect. DAB explicitly supports splitting configuration across multiple files using the data-source-files array, as long as each file includes its own data-source and entities sections.
    • E. Incorrect. Running from source is documented as a development and build workflow, not a recommended permanent production hosting model; containerized or managed hosting options serve that purpose.

    Domain 3: Implement AI capabilities in database solutions

    Subdomain 3.1: Design and implement models and embeddings

    27.AI_GENERATE_CHUNKS is called with CHUNK_SIZE = 200 and OVERLAP = 25. Approximately how many characters of the preceding chunk are repeated at the start of the next chunk?

    1. A.50 characters
    2. B.25 characters
    3. C.175 characters
    4. D.200 characters
    Show answer & explanation

    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.

    Subdomain 3.1: Design and implement models and embeddings

    28.Change Data Capture (CDC) retains a more complete history of row changes than Change Tracking, which reports only the latest net change per row.

    1. A.True
    2. B.False
    Show answer & explanation

    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.

    Subdomain 3.1: Design and implement models and embeddings

    29.To confirm which external models are registered in a database along with their API_FORMAT and MODEL_TYPE, query the ______ catalog view.

    1. A.sys.external_models
    2. B.sys.credentials
    3. C.sys.dm_exec_requests
    Show answer & explanation

    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.

    Subdomain 3.2: Design and implement intelligent search

    30.Which of the following are valid distance metrics that can be specified in the METRIC argument of CREATE VECTOR INDEX or in VECTOR_DISTANCE? (Select all that apply.)(Select 3)

    1. A.cosine
    2. B.euclidean
    3. C.dot
    4. D.manhattan
    5. E.jaccard
    Show answer & explanation

    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.

    Subdomain 3.2: Design and implement intelligent search

    31.A team is evaluating whether their vector search deployment meets latency and quality goals after switching from exact search to an approximate vector index. Which combination of measurements gives the most complete picture of vector search performance?

    1. A.Query latency alongside recall compared against exact (ENN) results on a representative sample of queries
    2. B.Only the size in megabytes of the vector index on disk
    3. C.Only the number of dimensions configured on the VECTOR column
    4. D.Only the total row count in the table being searched
    Show answer & explanation

    Correct answer: A — Query latency alongside recall compared against exact (ENN) results on a representative sample of queries

    • A. Evaluating both latency and recall against an exact-search baseline captures the two things that matter for an ANN deployment: how fast it is and how much accuracy was traded away, which together indicate whether the index is tuned appropriately.
    • B. Index size on disk reflects storage cost but says nothing about query speed or whether returned neighbors are close enough to the true nearest neighbors, so it is an incomplete measure of performance on its own.
    • C. The configured dimension count is a fixed schema property, not a performance measurement, and does not vary based on how well the search performs at runtime.
    • D. Row count is useful context for deciding between ANN and ENN, but by itself it does not measure the actual latency or recall achieved by the current implementation.

    Subdomain 3.2: Design and implement intelligent search

    32.A retail search engineer is deciding whether a new product-search feature should use full-text search, vector search, or hybrid search. Which of the following statements correctly describe when to choose each approach? (Select all that apply.)(Select 3)

    1. A.Full-text search is well suited to exact matches on identifiers such as SKUs, model numbers, or error codes
    2. B.Vector search is well suited to matching conceptually similar content even when no keywords overlap
    3. C.Hybrid search is useful when both exact keyword precision and semantic recall are required in the same query
    4. D.Vector search guarantees exact keyword matches in every case because embeddings encode all words literally
    5. E.Full-text search always outperforms vector search on every type of query regardless of content
    Show answer & explanation

    Correct answers: A, B, C — Full-text search is well suited to exact matches on identifiers such as SKUs, model numbers, or error codes; Vector search is well suited to matching conceptually similar content even when no keywords overlap; Hybrid search is useful when both exact keyword precision and semantic recall are required in the same query

    • A. Full-text search excels at lexical, keyword-driven matching, making it a strong fit for structured identifiers like SKUs or error codes where exact term presence matters most.
    • B. Vector search compares semantic embeddings, so it can surface conceptually related content even without shared vocabulary, which is its core strength over keyword search.
    • C. Hybrid search combines both approaches and fuses their rankings, which is the recommended pattern when a workload needs both exact-term precision and semantic recall together.
    • D. Embeddings capture semantic meaning in a continuous vector space and do not guarantee that exact keywords are preserved or matched literally, so this claim is false.
    • E. Neither approach universally outperforms the other; each has strengths depending on whether the workload favors exact terms or conceptual similarity, so this blanket claim is false.

    Subdomain 3.3: Design and implement retrieval-augmented generation (RAG)

    33.A DBA grants a service account the minimum permission needed to run sp_invoke_external_rest_endpoint, without granting broader server-level rights. Which database permission must be granted to that account?

    1. A.EXECUTE ANY EXTERNAL ENDPOINT
    2. B.CONTROL SERVER
    3. C.ALTER ANY CREDENTIAL
    4. D.VIEW SERVER STATE
    Show answer & explanation

    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.

    Subdomain 3.3: Design and implement retrieval-augmented generation (RAG)

    34.A team is evaluating whether several proposed scenarios are good fits for a retrieval-augmented generation (RAG) pattern built on Azure SQL Database and a language model. Which of the following are appropriate RAG use cases? (Select all that apply.)(Select 3)

    1. A.Answering support questions by grounding responses in proprietary product documentation stored in SQL tables
    2. B.Summarizing a customer's recent order history retrieved from a transactional table for a support chatbot
    3. C.Training a new foundation language model from scratch using the company's sales history
    4. D.Grounding chatbot answers in a knowledge base without retraining the underlying model
    5. E.Applying Always Encrypted to protect sensitive columns from unauthorized viewing
    6. F.Replacing all OLTP indexing strategies with vector search for a high-write transactional workload
    Show answer & explanation

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

    • A. Grounding a chatbot in proprietary product documentation stored in the database is a textbook RAG scenario, retrieving relevant text at query time to inform the model's answer.
    • B. Retrieving current order history and passing it as context to the model so it can summarize it for a support conversation is a typical RAG use case that combines live data with the model's language ability.
    • C. Training a new foundation model from scratch is a completely different, far more expensive undertaking than RAG, which reuses an existing pretrained model with retrieved context.
    • D. Grounding answers in a knowledge base without retraining the model is the core value proposition of RAG, letting the model use current data without any fine-tuning step.
    • E. Always Encrypted is a data protection feature for confidentiality of sensitive columns and has nothing to do with grounding language model responses in retrieved data.
    • F. Replacing all OLTP indexing with vector search for a write-heavy transactional workload is a performance design decision unrelated to whether RAG is an appropriate pattern for a use case.

    Subdomain 3.3: Design and implement retrieval-augmented generation (RAG)

    35.Once encoded as UTF-8 for transmission, the payload sent to or received from sp_invoke_external_rest_endpoint must not exceed ___ in size.

    1. A.10 MB
    2. B.100 MB
    3. C.1 GB
    Show answer & explanation

    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.

    Want the full experience?

    These are just samples. Practice the full Microsoft Azure SQL AI Developer Associate (DP-800) question bank in quiz mode — free, no signup, with domain practice and exam simulation.

    Practice all 352 questions in quiz mode