12 free Microsoft DP-800 practice questions from the CertStash pack of 101, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 12 free here, all 101 in the pack, and each step includes the one before it.
Get all 101 questions (US$39) · Download the first 10 as a PDF
Question 1
You have an Azure SQL database that contains tables named dbo. ProductDocs and dbo. ProductDocsEmbeddings. dbo. ProductDocs contains product documentation and the following columns: DocID (int) Title (nvarchar(200)) Body (nvarchar(max)) LastModified (datetime2) The documentation is edited throughout the day. dbo. ProductDocsEmbeddings contains the following columns: DocID (int) ChunkOrder (int) ChunkText (nvarchar(max)) Embedding (vector(1536)) The current embedding pipeline runs once per night. You need to ensure that embeddings are updated every time the underlying documentation content changes. The solution must NOT require a nightly batch process. What should you include in the solution?
Show answer and explanation
Correct answer: C. table triggers
Table triggers provide the mechanism to execute code immediately when dbo.ProductDocs is modified, enabling real-time embedding updates without requiring a nightly batch process. When a trigger fires on INSERT, UPDATE, or DELETE operations, it can invoke an Azure SQL Database external language procedure or application logic to regenerate and update the corresponding embeddings in dbo.ProductDocsEmbeddings synchronously with the documentation changes.
Why the other options are wrong
- A. Fixed-size chunking is a data preparation technique unrelated to triggering updates; it does not solve the problem of detecting and responding to content changes in real time.
- B. A smaller embedding model reduces computational overhead but does not address the core requirement of updating embeddings automatically when documentation changes.
- D. Change tracking enables detection of modified rows but does not automatically trigger embedding regeneration; it requires a separate process to poll and act on tracked changes, still necessitating scheduled intervention rather than real-time updates.
Question 2
Your team is developing an Azure SQL dataset solution from a locally cloned GitHub repository by using Microsoft Visual Studio Code and GitHub Copilot Chat. You need to disable the GitHub Copilot repository-level instructions for yourself without affecting other users. What should you do?
Show answer and explanation
Correct answer: A. From Visual Studio Code, modify your GitHub Copilot Chat user settings.
GitHub Copilot repository-level instructions in .github/copilot-instructions.md apply to all users of that repository. To disable them for yourself without affecting others, you modify your personal GitHub Copilot Chat user settings in Visual Studio Code, which allows individual configuration overrides.
Why the other options are wrong
- B. The --debug flag is for debugging output and does not control instruction behavior.
- C. Deleting the file would affect all users in the repository, not just yourself.
Question 3
You have an Azure SQL database. You deploy Data API builder (DAB) to Azure Container Apps by using the mcr.microsoft.com/azure-databases/data-api-builder:latest image. You have the following Container Apps secrets: MSSQL_CONNECTION_STRING that maps to the SQL connection string DAB_CONFIG_BASE64 that maps to the DAB configuration You need to initialize the DAB configuration to read the SQL connection string. Which command should you run?
Show answer and explanation
Correct answer: B. dab init --database-type mssql --connection-string “@env(‘MSSQL_CONNECTION_STRING’)” --host-mode Production --config dab-config.json
In Azure Container Apps, container secrets are referenced using the @env() syntax to access environment variables. The MSSQL_CONNECTION_STRING secret should be referenced as @env('MSSQL_CONNECTION_STRING') to retrieve the SQL connection string value from the Container Apps secrets configuration.
Why the other options are wrong
- A. The secretref syntax is incorrect for Container Apps; DAB_CONFIG_BASE64 is for the DAB configuration file, not the connection string.
- C. The secretref syntax does not apply to Container Apps secrets; this is not the correct reference method.
- D. DAB_CONFIG_BASE64 is the base64-encoded configuration file, not the connection string itself.
Question 4
You have a SQL database in Microsoft Fabric that contains a nvarchar (max) column named MessageText. An ID is always contained within the first paragraph of MessageText. You need to write a Transact-SQL query that uses REGEXP_SUBSTR to extract the ID from MessageText. What should you include in the query?
Show answer and explanation
Correct answer: B. Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
REGEXP_SUBSTR in SQL Server has limitations with nvarchar(max) columns and works reliably with smaller string lengths. Casting MessageText to nvarchar(4000) before calling REGEXP_SUBSTR ensures compatibility and allows the function to properly extract the ID from the first paragraph without encountering data type limitations.
Why the other options are wrong
- A. STRING_ESCAPE is used for escaping special characters in JSON/HTML contexts, not for enabling REGEXP_SUBSTR functionality.
- C. A COLLATE clause specifies collation rules for string comparison but does not enable REGEXP_SUBSTR to work with max columns.
- D. TRY_CONVERT to varchar(max) does not solve the issue; it still uses a max-length type that causes problems with REGEXP_SUBSTR.
Question 5
You have an Azure SQL database that contains database-level Data Definition Language (DDL) triggers, including a trigger named ddl_Audit. You need to prevent ddl_Audit from firing during the next deployment. The trigger object must remain in place. Which Transact-SQL statement should you use?
Show answer and explanation
Correct answer: D. DISABLE TRIGGER
The DISABLE TRIGGER statement is used to prevent a trigger from firing while keeping the trigger object in the database. This allows you to temporarily suspend the ddl_Audit trigger during deployment without deleting or removing it, and it can be re-enabled afterward with ENABLE TRIGGER.
Why the other options are wrong
- A. ALTER TRIGGER modifies the trigger definition but does not disable it from firing.
- B. ALTER DATABASE is used for database-level settings and does not control individual trigger execution.
- C. ALTER SERVER AUDIT SPECIFICATION manages auditing policies, not trigger execution.
- E. ALTER DATABASE AUDIT SPECIFICATION manages database auditing, not trigger behavior.
Question 6
Your development team uses GitHub Copilot Chat in Microsoft SQL Server Management Studio (SSMS) to generate and run Transact-SQL queries against an Azure SQL database named DB1. DB1 contains tables that store sensitive customer data. You need to ensure that any Transact-SQL queries that run from GitHub Copilot Chat in SSMS are restricted by the same permissions as the developer’s database login. What prevents the GitHub Copilot Chat-run queries from accessing data beyond the developer’s access?
Show answer and explanation
Correct answer: B. GitHub Copilot Chat runs queries by using the developer’s database identity and permissions.
GitHub Copilot Chat in SSMS executes queries using the authenticated developer's database identity and applies the same permission restrictions as that user's login. This means any Transact-SQL queries generated and run by Copilot are subject to the developer's role-based access control and cannot access data beyond what the developer is authorized to see.
Why the other options are wrong
- A. GitHub Copilot Chat does not run queries in a read-only sandbox; it executes real queries against the actual database.
- C. Queries execute against the database directly with the developer's permissions; filtering on the client side is not how database security works.
- D. GitHub Copilot Chat does not apply different RLS policies; it uses the same policies that apply to the developer's login.
Question 7
You have an Azure SQL database named AdventureWorksDB that contains a table named dbo. Employee. You have a C# Azure Functions app that uses an HTTP-triggered function with an Azure SQL input binding to query dbo. Employee. You are adding a second function that will react to row changes in dbo. Employee and write structured logs. You need to configure AdventureWorksDB and the app to meet the following requirements: Changes to dbo. Employee must trigger the new function within five seconds. Each invocation must process no more than 100 changes. Which two database configurations should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Show answer and explanation
Correct answer: C, D. Enable change tracking on the dbo. Employee table; Enable change tracking at the database level.
Change tracking must be enabled at both the database level and on the specific table to track row changes. Enabling change tracking at the database level activates the infrastructure, and enabling it on dbo.Employee allows the system to track modifications. These tracked changes can then be detected by Azure Functions within the specified time window without the overhead of triggers or CDC.
Why the other options are wrong
- A. Creating a DML trigger would introduce processing overhead; change tracking is the preferred method for Azure Functions.
- B. Sql_Trigger_MaxBatchSize controls trigger batching behavior, not change tracking batch sizes.
- E. Sql_Trigger_PollingIntervalMs is a trigger-related parameter; change tracking uses different polling mechanisms managed by Azure.
- F. CDC is more heavyweight than change tracking and is not necessary when using Azure Functions with change tracking.
Question 8
You have an Azure SQL database named SalesDB that contains a table named dbo. Articles. dbo. Articles contains two million articles with embeddings. The articles are updated frequently throughout the day. You query the embeddings by using VECTOR_SEARCH. Users report that semantic search results do NOT reflect the updates until the following day. You need to ensure that the embeddings are updated whenever the articles change. The solution must minimize CPU usage on SalesDB. Which embedding maintenance method should you implement?
Show answer and explanation
Correct answer: B. Enable change data capture (CDC) on dbo. Articles and use an Azure Functions app to process CDC changes.
Using change data capture (CDC) on dbo.Articles with an Azure Functions app to process CDC changes provides efficient, event-driven embedding updates. CDC tracks only the rows that have changed, and Azure Functions processes these changes asynchronously, minimizing CPU impact on SalesDB while ensuring embeddings are updated shortly after articles change, typically within minutes rather than waiting until the next day.
Why the other options are wrong
- A. VECTOR_DISTANCE is a different function for calculating distances; it does not solve the staleness problem with embeddings.
- C. Regenerating all embeddings hourly for two million articles would consume significant CPU resources, contrary to the requirement to minimize CPU usage.
- D. Row-level triggers calling AI_GENERATE_EMBEDDINGS would create synchronous processing overhead and significantly impact database CPU during frequent updates.
Question 9
You have a GitHub Codespaces environment that has GitHub Copilot Chat installed and is connected to a SQL database in Microsoft Fabric named DB1. DB1 contains tables named Sales.Orders and Sales.Customers. You use GitHub Copilot Chat in the context of DB1. A company policy prohibits sharing customer Personally Identifiable Information (PII), secrets, and query result sets with any AI service. You need to use GitHub Copilot Chat to write and review Transact-SQL code for a new stored procedure that will join Sales.Orders to Sales.Customers and return customer names and email addresses. The solution must NOT share the actual data in the tables with GitHub Copilot Chat. What should you do?
Show answer and explanation
Correct answer: D. Ask GitHub Copilot Chat to generate the stored procedure by using schema details only.
To protect PII and maintain security compliance without sharing actual data with GitHub Copilot Chat, you should provide only the database schema details (table names, column names, data types) and describe what the stored procedure should accomplish. Copilot can generate appropriate Transact-SQL code based on schema information alone without exposure to actual customer names, email addresses, or other sensitive data.
Why the other options are wrong
- A. Pasting actual rows containing email addresses violates the company policy prohibiting sharing PII with AI services.
- B. Providing result sets with customer names and email addresses directly shares sensitive data with GitHub Copilot Chat, which violates the policy.
- C. Providing the database connection string allows Copilot to access and retrieve actual data, which violates the policy against sharing customer PII and data.
Question 10
You have a SQL database in Microsoft Fabric that contains a column named Payload. Payload stores customer data in JSON documents that have the following format. Data analysis shows that some customers have subaddressing in their email address, for example, user1+promo@contoso.com. You need to return a normalized email value that removes the subaddressing, for example, user1 +promo@contoso.com must be normalized to user1@contoso.com. Which Transact-SQL expression should you use?
Show answer and explanation
Correct answer: A. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*$’, ‘’)
The task requires removing the subaddressing portion (everything after and including the '+' sign) while preserving the domain. Option A uses REGEXP_REPLACE with the pattern '\+.*$' which matches a plus sign followed by any characters until the end of the string, and replaces this entire match with an empty string. For input 'user1+promo@contoso.com', this pattern matches '+promo@contoso.com' and removes it, leaving 'user1@contoso.com'. This correctly normalizes the email by removing the subaddressing while keeping the base email and domain intact.
Why the other options are wrong
- B. REGEXP_SUBSTR is a substring extraction function, not a replacement function, and cannot be used to normalize data by removing characters.
- C. This pattern '\+.*@' would match from the plus sign through to the @ symbol inclusive, replacing it with just '@', resulting in 'user1@@contoso.com' which is incorrect.
- D. This pattern '\+.*' would match the plus sign and everything after it to the end of the string, removing both the subaddressing and the domain entirely, leaving only 'user1'.
Question 11
You have an Azure SQL database. You need to create a scalar user-defined function (UDF) that returns the number of whole years between an input parameter named @OrderDate and the current date/time as a single positive integer. The function must be created in Azure SQL Database. You write the following code. What should you insert at line 05?
Show answer and explanation
Correct answer: D. RETURN DATEDIFF(year, @OrderDate, GETDATE());
The function must return the number of whole years between @OrderDate and the current date as a positive integer. DATEDIFF(year, @OrderDate, GETDATE()) calculates the difference in years from the order date to today, producing a positive result when the order date is in the past. The RETURN keyword is required in a scalar UDF to return the computed value. The argument order matters: DATEDIFF(year, startdate, enddate) gives positive values when enddate is later than startdate.
Why the other options are wrong
- A. Has arguments in reverse order (GETDATE() as start, @OrderDate as end), which produces negative values for past order dates.
- B. Lacks the RETURN keyword required for a scalar UDF, and dividing months by 12 does not produce whole years accurately due to rounding.
- C. Only subtracts year components without considering actual date intervals, failing for dates within the same calendar year and lacking the RETURN keyword.
Question 12
You have an Azure SQL database that contains a table named Rooms. Rooms was created by using the following Transact-SQL statement. You discover that some records in the Rooms table contain NULL values for the Owner field. You need to ensure that all future records have a value for the Owner field. What should you add?
Show answer and explanation
Correct answer: B. a check constraint
To ensure that all future records have a value for the Owner field, add a check constraint such as CHECK (Owner IS NOT NULL). This constraint enforces the rule that the Owner column cannot contain NULL values for any new records inserted into the table.
Why the other options are wrong
- A. A foreign key constraint validates referential integrity to another table but does not prevent NULL values in the Owner field.
- C. A nonclustered index improves query performance but has no effect on preventing NULL values from being inserted.
- D. A unique constraint ensures values are distinct but does not prevent NULL values, NULL is allowed in unique columns.
That was 12 of 101.
The full Microsoft DP-800 pack has all 101 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.
Not ready yet? Get the first 16 free as one PDF by email. Get the Half Pack →