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

Correct answer: B — A scalar user-defined function (UDF).

• A. Incorrect. A stored procedure with an output parameter can return a value, but it cannot be used directly in SELECT, WHERE, or ORDER BY clauses. Stored procedures are executed as standalone statements and do not return values inline in queries.
• B. Correct. A scalar user-defined function accepts parameters, performs calculations, and returns a single scalar value such as an integer. Because it can be used like an expression, it is appropriate for use in SELECT, WHERE, and ORDER BY clauses across multiple queries and views.
• C. Incorrect. A view does not accept parameters, so it cannot directly take two date inputs to calculate business days. A computed column also cannot provide a reusable parameterized routine across different queries and views.
• D. Incorrect. A table-valued function returns a table result set, not a single integer. Although functions can be used in queries, this requirement specifically says the routine must return an integer, which fits a scalar function rather than a table-valued one.

Correct answer: B — False

• A. The statement is false because a stored procedure can call a user-defined function, so the claim that it cannot is incorrect.
• B. The statement is false because a stored procedure can call a user-defined function, making the statement false.

Correct answer: B — Inline table-valued function

• A. Incorrect. Multi-statement table-valued functions are not inlined by the query optimizer. They execute as a separate operation, often materializing results into a table variable, which is less optimizable and can perform poorly when joined to large tables.
• B. Correct. Inline table-valued functions are expanded inline by the query optimizer, integrating their logic directly into the calling query. This behaves similarly to a parameterized view, avoiding row-by-row evaluation and improving performance, especially when joining to large tables.
• C. Incorrect. Scalar functions return a single value, not a table, so they cannot be used in a join scenario. They also suffer from row-by-row execution, which would not solve the performance issue.
• D. Incorrect. Stored procedures can return result sets but cannot be directly used like a table expression in a JOIN. They are not inlined into a query in the same way as an inline table-valued function.

Correct answer: D — 2100

• A. Incorrect. 256 is below the actual maximum limit for stored procedure parameters in Azure SQL Database, which follows the SQL Server limit of 2,100.
• B. Incorrect. 512 is also below the documented maximum of 2,100 parameters for stored procedures in Azure SQL Database.
• C. Incorrect. 1024 is still below the maximum supported parameter count. Azure SQL Database allows up to 2,100 parameters, matching SQL Server.
• D. Correct. Azure SQL Database supports up to 2,100 parameters for stored procedures, as documented by Microsoft. This limit is the same as SQL Server.

Correct answer: A — sp_executesql

• A. Correct. sp_executesql is the system stored procedure in SQL Server and Azure SQL Database used to execute dynamic SQL. It supports parameterized queries and is the recommended method for dynamic SQL execution, improving plan reuse and security.
• B. Incorrect. sp_dynamic is not a valid system stored procedure in SQL Server or Azure SQL Database. It does not exist as a built-in stored procedure.
• C. Incorrect. sp_run is not a valid system stored procedure for executing dynamic SQL. It is not part of the standard T-SQL system stored procedures.
• D. Incorrect. sp_query is not a valid system stored procedure in SQL Server or Azure SQL Database. It is not used for dynamic SQL execution.

Correct answer: B — Prompt and context are sent to the cloud and may be logged for service improvement without content exclusions.

• A. Incorrect. AI-assisted coding tools like GitHub Copilot are typically cloud-backed services. Prompts and code context are transmitted to the cloud for processing, not processed solely on the local machine. This is a common misconception, and the transmission of data to the service provider depends on the tool and its configuration.
• B. Correct. A key security implication is that prompts and surrounding context are sent to the cloud for processing. Depending on the provider's policies, this data may be retained or logged for service improvement. This creates a risk of exposing sensitive code, data, or secrets if users include them in prompts without applying content exclusions or organizational controls.
• C. Incorrect. AI-assisted tools do not automatically exclude database schema, query results, or other code snippets from being sent to the AI model. Developers must manually ensure that sensitive data is not included in prompts, as exclusion is not automatic and requires user caution or explicit organizational policies.
• D. Incorrect. AI assistants do not reliably detect and mask personally identifiable information (PII) in prompts automatically. Organizations and developers must implement their own data-loss prevention, redaction, and governance controls before sending sensitive information to an AI service.

Correct answer: A — Restart the VS Code window to reload extensions and the instruction file.

• A. Correct. Restarting the VS Code window forces the editor and Copilot extension to reload the workspace state and pick up the updated instruction file. This is the recommended first troubleshooting step for refreshing cached configuration.
• B. Incorrect. Clearing the Copilot chat history does not force Copilot to reload the repository instruction file. The instruction file is cached at the extension level, not in the chat history.
• C. Incorrect. There is no standard 24-hour sync cycle for Copilot instructions. Updates to the instruction file should be reflected after a reload or reopening the workspace.
• D. Incorrect. Uninstalling and reinstalling the extension is an extreme and unnecessary measure. A simple reload of VS Code is sufficient to resolve the issue.

Correct answer: B — GPT-4o

• A. Incorrect. GPT-3.5-turbo is an older model with lower accuracy and a more limited knowledge cutoff, making it less suitable for complex tasks requiring up-to-date information.
• B. Correct. GPT-4o is the most advanced model available in GitHub Copilot Chat, offering superior reasoning, higher accuracy, and the most current knowledge, making it ideal for complex DAX measures.
• C. Incorrect. The Codex model is older and primarily optimized for code generation, but it lacks the broad reasoning capabilities and recent knowledge updates of GPT-4o.
• D. Incorrect. The Default model may not be the most advanced option; it often defaults to an older version, so selecting a specific newer model like GPT-4o is better for maximum accuracy.

Correct answer: A — Member or Admin role in the Fabric workspace.

• A. Correct. Users must have at least the Member or Admin role in the Fabric workspace to access the Copilot pane. This role provides sufficient permissions to interact with workspace resources and use AI-assisted features.
• B. Incorrect. The Contributor role on Fabric capacity is related to resource allocation and management, not a requirement for accessing the Copilot pane. Workspace-level permissions are needed.
• C. Incorrect. Read-only access to the warehouse is insufficient. Copilot requires permissions to author and modify content, which are provided by higher workspace roles like Member or Admin.
• D. Incorrect. While the tenant-level Copilot feature must be enabled, users still need specific workspace permissions (Member or Admin) to see and use the Copilot pane.

Correct answers: A, D — Enable content exclusions for directories containing sensitive algorithms.; Sanitize sensitive comments from code before committing.

• A. Correct. Enabling content exclusions for directories containing sensitive algorithms prevents GitHub Copilot from using or suggesting code from those directories, directly reducing the risk of exposure by limiting the source material available to the assistant.
• B. Incorrect. Using Copilot behind a corporate VPN may help with network access control but does not meaningfully prevent Copilot from processing or suggesting sensitive code. The primary risk is data exposure through code context, not transport security.
• C. Incorrect. GitHub Copilot does not currently offer a self-hosted on-premises model option for processing prompts. This is not a standard mitigation for Copilot's data exposure concerns.
• D. Correct. Sanitizing sensitive comments before committing reduces the likelihood that secrets, business rules, or proprietary logic are sent to Copilot as contextual input. Comments are often overlooked but can still expose important information to AI tools.
• E. Incorrect. Rotating the Copilot authentication token does not reduce exposure of sensitive code or prompts to the assistant. Token rotation is an account security practice, not a control for limiting code-context disclosure.
• F. Incorrect. Using a dedicated Git branch for Copilot testing may help with workflow isolation but does not inherently reduce the amount of sensitive code Copilot can access. The sensitive content remains in the repository unless explicitly excluded or sanitized.

Correct answer: B — CustomerID

• A. OrderDate is a date attribute used for filtering or grouping by time, but it does not guarantee uniqueness because multiple orders can occur on the same date. It is not suitable as a primary key.
• B. CustomerID is a natural key that uniquely identifies each customer. In a customer dimension table, it is the appropriate choice for a primary key, ensuring each row represents a distinct customer.
• C. ProductID uniquely identifies a product and would be the primary key for a product dimension table, but it is not the best choice for a customer dimension. The question is about a customer table, so ProductID is not correct.
• D. SalesAmount is a measure or value column, not an identifier. It can repeat across many rows and is not suitable for uniquely identifying a record. It should be stored as a fact in a sales table, not as a primary key.

Correct answer: C — Temporal table

• A. Incorrect. A memory-optimized table is designed for high-throughput, low-latency OLTP scenarios by keeping data in memory and using memory-optimized indexes. It does not provide automatic tracking of historical data changes.
• B. Incorrect. A clustered columnstore table is optimized for large-scale analytics and data warehousing workloads with columnar compression. It does not automatically preserve historical versions of rows.
• C. Correct. A temporal table is designed to keep track of row history automatically by storing current and historical versions of data. This makes it the best choice when you need built-in support for querying how data changed over time.
• D. Incorrect. A partitioned table improves manageability and performance by splitting data across partitions based on a partition function. It does not provide automatic history tracking of data changes.

Correct answer: B — VARCHAR(10)

• A. Incorrect. CHAR(10) WITH CI_AS is not a valid standalone data type definition in SQL Server/Azure SQL. CI_AS is a collation suffix, and collation is specified separately from the data type.
• B. Correct. VARCHAR(10) is a valid SQL data type definition for a variable-length non-Unicode string with a maximum length of 10 characters. If no collation is specified, the database or column default collation is used.
• C. Incorrect. NCHAR(10) is a valid fixed-length Unicode data type, but it is not the correct answer because VARCHAR(10) is a simpler variable-length non-Unicode type that is more commonly used unless Unicode is required.
• D. Incorrect. NVARCHAR(10) is a valid variable-length Unicode data type, but it is not the correct answer because VARCHAR(10) serves the same variable-length purpose without Unicode overhead.

Correct answers: A, D — Create an external table pointing to the data lake files; Use OPENROWSET to query the files directly

• A. Correct. Creating an external table allows you to query data lake files as if they were database tables, providing a persistent and schema-defined approach to directly access the data.
• B. Incorrect. Defining an external file format is necessary for interpreting the file structure, but it alone does not enable direct querying; it must be used with an external data source and table or OPENROWSET.
• C. Incorrect. An external data source points to the storage location, but it does not directly query the files; it is a prerequisite for external tables or OPENROWSET.
• D. Correct. OPENROWSET enables ad-hoc querying of data lake files directly without creating permanent database objects, making it a direct and flexible approach.
• E. Incorrect. BULK INSERT imports data into a database table, which moves the data rather than querying it directly in the lake.
• F. Incorrect. Replicating data from the lake to the database involves data movement, not direct querying of the files in place.

Correct answer: B — IDENTITY

• A. Incorrect. SEQUENCE is a database object that generates a sequence of numeric values separately from a table, not a column property. It is not the standard per-table auto-increment feature.
• B. Correct. IDENTITY is a column property that automatically generates incrementing numeric values for each row. It is commonly used for surrogate keys and is the standard way to auto-number rows in SQL Server.
• C. Incorrect. NEWID is a function that generates a uniqueidentifier (GUID) value, not an incrementing numeric value. It is used for unique identifiers but not for automatic sequential numbering.

Correct answer: B — GET

• A. Incorrect. POST is used to create a new resource or submit data, not to retrieve data.
• B. Correct. GET is the standard HTTP method for retrieving data from a REST API.
• C. Incorrect. PUT is used to update or replace an existing resource, not to retrieve data.
• D. Incorrect. DELETE is used to remove a resource, not to retrieve data.

Correct answer: B — ttl

• A. Incorrect. 'enabled' is not a standard HTTP cache directive or parameter for controlling cache duration. It typically indicates whether caching is turned on, not the expiration time.
• B. Correct. 'ttl' (time to live) is a widely used term for specifying how long cached data remains valid before expiration. In Azure-integrated solutions, TTL is a key setting for cache duration.
• C. Incorrect. 'cache-control' is an HTTP header that can include multiple directives, but it is not a single value for cache lifetime. The TTL value would be set within cache-control or similar configuration.
• D. Incorrect. 'stale-while-revalidate' is a cache directive that allows serving stale content while asynchronously revalidating, but it is not the generic term for cache expiration duration.
• E. Incorrect. 'location' typically refers to a geographic region or storage location, not a caching duration parameter.

Correct answer: E — DDL changes

• A. Incorrect. INSERT is a DML operation and is not logged by default in Azure SQL Database audit logs unless explicitly configured.
• B. Incorrect. UPDATE is a DML operation and is not logged by default in Azure SQL Database audit logs unless explicitly configured.
• C. Incorrect. DELETE is a DML operation and is not logged by default in Azure SQL Database audit logs unless explicitly configured.
• D. Incorrect. TRUNCATE is a DML operation (though minimally logged) and is not logged by default in Azure SQL Database audit logs unless explicitly configured.
• E. Correct. DDL changes (e.g., CREATE, ALTER, DROP) are logged by default in Azure SQL Database audit logs as part of schema change tracking.

Correct answer: A — True

• A. The statement is true because Azure Data Factory is a cloud-based ETL and data integration service that supports connectors for both on-premises SQL Server and Azure SQL Database, enabling seamless data orchestration across hybrid environments.
• B. The statement is false because Azure Data Factory cannot directly connect to on-premises data sources without additional setup, but it does support such connections through self-hosted integration runtimes, so the statement remains true.

Correct answer: B — runtime

• A. "data-source" refers to the origin of data, not the execution environment that hosts integration logic. It is not the term used for the component that runs pipelines.
• B. Correct. In Azure Synapse Analytics and Azure Data Factory, the runtime (e.g., Integration Runtime or Synapse Runtime) is the component that executes and hosts pipeline activities and integration logic.
• C. "entities" typically represent data model objects or records, not the execution environment. They are not used to describe the component that runs pipelines.
• D. "authentication" relates to verifying identity and access permissions, not to the hosting or execution of pipelines. It is not the correct term for this context.

Correct answer: A — Create a nonclustered index on the column used in the filter predicate.

• A. Creating a nonclustered index on the column used in the filter predicate allows the query optimizer to perform an index seek instead of a table scan, significantly improving performance for filtered queries.
• B. Dropping the clustered index on the primary key would degrade performance as it provides physical ordering and efficient access for many queries; it does not solve the filter on a non-key column.
• C. Rewriting the query to use a custom cursor is typically much slower than set-based operations and does not address the root cause of the table scan.
• D. Executing DBCC FREEPROCCACHE clears the plan cache, forcing recompilation, but does not address the missing index causing the table scan; it may also cause temporary performance degradation.

Correct answer: B — sys.dm_exec_query_stats

• A. Incorrect. sys.dm_exec_cached_plans returns information about cached execution plans, such as plan handles and cache use, but it does not provide aggregated runtime statistics like total_worker_time or execution_count. It is useful for examining plan cache contents, not query performance totals.
• B. Correct. sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans, including total_worker_time, execution_count, total_logical_reads, and similar metrics. This DMV is commonly used to identify expensive or frequently executed queries.
• C. Incorrect. sys.dm_exec_requests shows currently executing requests and their current session/request state, such as CPU time and wait information. It reflects live activity rather than aggregated statistics for cached query plans.
• D. Incorrect. sys.dm_os_performance_counters exposes SQL Server performance counters at the instance level, not per cached query plan statistics. It is useful for broader server monitoring, but not for fields like total_worker_time and execution_count.

Correct answer: A — sp_query_store_force_plan

• A. Correct. `sp_query_store_force_plan` forces a specific execution plan for a query in Query Store. To identify queries with multiple plans, you first query Query Store views (e.g., sys.query_store_plan) to find the plan IDs, then use this procedure to force the most efficient one.
• B. Incorrect. `sp_query_store_unforce_plan` removes a previously forced plan, enabling the query optimizer to choose a new plan. It does not force a plan and is used when you want to stop forcing a specific plan.
• C. Incorrect. `sp_query_store_flush_db` flushes the in-memory portion of Query Store to disk. It is used for persistence management, not for analyzing or forcing execution plans.
• D. Incorrect. `sp_query_store_remove_query` removes a query and all its associated plans from Query Store. It is used for cleanup, not for selecting or forcing an optimal execution plan.

Correct answers: A, B, F — Query text and execution time; Actual execution plans; CPU time per query

• A. Correct. Query Store captures query text along with the execution time (duration) as part of runtime statistics. This helps track query performance over time.
• B. Correct. Query Store stores actual execution plans, allowing analysis of plan changes and query performance tuning.
• C. Incorrect. While Query Store does capture wait statistics per query in some contexts, the question expects only three types: query text/execution time, execution plans, and CPU time. Wait statistics are often considered part of runtime statistics but are not a primary category for this question.
• D. Incorrect. Database backup history is managed separately by Azure SQL Database backup services and is not captured by Query Store.
• E. Incorrect. Deadlock graphs are not part of Query Store data; they are obtained through Extended Events or system views like sys.dm_tran_locks.
• F. Correct. Query Store captures CPU time per query as part of runtime statistics, helping identify CPU-intensive queries and regressions.

Correct answers: A, C, D — The session IDs of the victim and survivor; The locks currently being held and requested; The complete SQL text of the involved statements

• A. Correct. The deadlock graph includes the session IDs (process IDs) of the processes involved, specifically the victim and the survivors. This helps identify which connections were part of the deadlock.
• B. Incorrect. The deadlock graph itself does not provide the exact timestamp as a primary data point; the timestamp is typically associated with the event that captured the graph, not the graph content itself.
• C. Correct. The deadlock graph shows the resources (locks) each process is holding and requesting, including lock modes and the conflicting resources, which is central to understanding the deadlock.
• D. Correct. The deadlock graph includes the input buffer (SQL text) of the statements that were executing at the time of the deadlock, allowing identification of the specific queries involved.
• E. Incorrect. The deadlock graph does not provide query execution plans. Execution plans must be obtained separately using tools like Query Store or actual execution plans.
• F. Incorrect. Index fragmentation levels are not part of the deadlock graph; they are analyzed using DMVs or index maintenance tools.

Correct answer: D — A pre-deployment gate in the release pipeline

• A. Incorrect. A branch policy with required reviewers and a CODEOWNERS file enforces code review before merging changes into a branch, but it does not control deployment-time approvals. It is a source control governance mechanism, not a deployment approval check.
• B. Incorrect. A secret variable stores sensitive data, such as an approver’s alias, but it cannot enforce approval workflows or permissions. It may be used as part of a manual validation task, but by itself it does not require a specific person to approve deployment.
• C. Incorrect. A service connection with approval checks controls access to external resources (e.g., Azure subscriptions) but does not enforce that a specific person approves code changes before deployment. It is about resource authorization, not deployment approvals.
• D. Correct. In Azure DevOps release pipelines, a pre-deployment gate can be configured to require manual approval from specified users or groups before proceeding with deployment. This directly enforces that a named approver must approve the deployment.

Correct answer: B — Automatically merging pull requests that pass all status checks

• A. Incorrect. While requiring a review from the data architecture team is a governance control, it is a manual approval process and does not represent an automated CI/CD pipeline capability. CI/CD emphasizes automation, so this is not a core practice for SQL Database Projects.
• B. Correct. Automatically merging pull requests after all status checks pass is a classic CI/CD automation practice. It streamlines code promotion while enforcing validation through build, test, or policy checks, making it a key best practice for SQL Database Projects.
• C. Incorrect. Sending email notifications on every commit can support collaboration but is not a CI/CD deployment or source control enforcement feature. It does not automate integration, validation, or release actions, so it is not a core CI/CD practice.
• D. Incorrect. Enforcing branch naming conventions is a repository policy that improves traceability and consistency, but it does not directly automate build, validation, or deployment flows. It is a source control best practice, not a CI/CD pipeline capability.

Correct answer: A — Add a step to generate a drift report and conditionally drop unknown objects

• A. Correct. Adding a pre-deployment step that generates a drift report using sqlpackage allows you to identify unknown objects. Conditionally dropping them helps maintain consistency between the project and the target database, ensuring successful deployment.
• B. Incorrect. There is no built-in 'block on drift' setting in SQL Database Projects or Azure DevOps pipelines for SQL deployments. The pipeline would not stop automatically for drift.
• C. Incorrect. Manually deleting objects from the portal is not scalable or reliable for CI/CD pipelines. It introduces human error and bypasses source control, which can lead to further inconsistencies.
• D. Incorrect. The /p:BlockOnPossibleDataLoss=false flag is used to bypass warnings about potential data loss during deployment, not to handle drift. Ignoring drift could overwrite unmanaged changes.

Correct answers: A, B — It is an open-source SQL Server unit testing framework; It supports mocking tables and functions to isolate tests

• A. tSQLt is an open-source framework specifically designed for unit testing in SQL Server. It allows developers to write and execute unit tests directly in T-SQL, making it a valuable tool for database CI/CD pipelines.
• B. tSQLt supports mocking of tables, views, and functions to isolate test cases. This ensures tests focus on the specific logic being tested without external dependencies, which is a core feature for effective unit testing.
• C. tSQLt is designed for unit testing within a single database. It does not natively support executing integration tests across multiple databases simultaneously; such scenarios typically require additional tooling or custom approaches.
• D. tSQLt does not provide a built-in graphical test runner for manual execution. Tests are usually executed via T-SQL scripts or integrated into CI/CD pipelines using tools like Visual Studio, Azure Data Studio, or command-line automation.
• E. tSQLt is focused on unit testing and does not natively integrate with load testing tools. Load or performance testing is handled by separate frameworks and tools outside the scope of tSQLt.

Correct answer: B — azure/get-keyvault-secrets

• A. Incorrect. The azure/login action authenticates to Azure, but does not retrieve secrets from Key Vault.
• B. Correct. The azure/get-keyvault-secrets action is specifically designed to retrieve secrets from Azure Key Vault in a GitHub Actions workflow.
• C. Incorrect. The azure/keyvault-certificates action is used to manage certificates, not to retrieve secrets.

Correct answer: A — Use ANN for faster searches with slightly reduced accuracy

• A. Correct. Approximate Nearest Neighbor (ANN) search trades a small amount of accuracy for significantly faster search performance, making it ideal for large-scale vector search where speed is prioritized over exactness.
• B. Incorrect. Exact Nearest Neighbor (ENN) search is used when precise, exhaustive results are needed, and it is generally slower than ANN. It does not provide faster searches with reduced accuracy.
• C. Incorrect. ANN is an approximate method and does not guarantee exact top-k results. For exact results, ENN should be used.
• D. Incorrect. ENN stands for Exact Nearest Neighbor and returns exact results, not approximate. Faster approximate results are associated with ANN.

Correct answer: A — The dimension must match the embedding model's output size

• A. Correct. The vector dimension must match the output size of the embedding model used to generate the vectors. This ensures compatibility and accurate similarity search results.
• B. Incorrect. While dimension can influence performance, it does not determine which vector index type you can use. The index type is chosen independently based on requirements.
• C. Incorrect. Vector dimensions do not need to be a power of two. No such requirement exists in Azure AI Search.
• D. Incorrect. The dimension is not automatically set by the engine; it must be explicitly defined in the index schema based on the embedding model output.

Correct answer: C — Use a CTE or derived table to rank results and compute RRF

• A. A UNION combines result sets but does not preserve the per-result-set rank information required for RRF. Additional ranking logic is needed, making this approach incomplete.
• B. A JOIN on document ID can merge rows from different result sets, but it does not incorporate the necessary rank values from each source to compute the RRF score correctly.
• C. Using a CTE or derived table allows you to rank each result set separately and then apply the RRF formula to combine scores. This is the correct manual implementation approach.
• D. Azure SQL/SQL Server does not provide a built-in RRF() function. RRF must be implemented manually using ranking logic in subqueries or CTEs.

Correct answer: B — Stores synonyms for expanding search terms

• A. Incorrect. This describes a stopword list, which removes common words like 'the' and 'and' to improve performance. A thesaurus is used for synonym expansion, not exclusion.
• B. Correct. A thesaurus stores synonym mappings so that searches can include equivalent or related terms, improving recall even when queries use different wording.
• C. Incorrect. Language tokenization rules are defined by a language analyzer or word breaker. A thesaurus does not handle tokenization.
• D. Incorrect. Specifying file extensions for binary indexing is a configuration for indexers or data sources, not a feature of a thesaurus.

Correct answer: B — False

• A. Incorrect. Microsoft recommends using separate subscriptions for each environment (development, staging, production) to minimize risk, manage access effectively, and simplify cost tracking. A single subscription increases the chance of accidental changes or deletions in production.
• B. Correct. As per official Azure documentation, using separate subscriptions per environment is a recommended best practice. This approach isolates resources, enhances security, and provides better control over access and costs. Resource groups can further organize resources within each subscription.