Free Microsoft DP-800 practice questions

10 free Microsoft DP-800 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 101 questions. Work through them, then open each answer to check your reasoning.

Get all 101 questions (US$39) · Download these 10 as a PDF

Question 1

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?

  1. From Visual Studio Code, modify your GitHub Copilot Chat user settings.
  2. Add a –debug flag when you start the GitHub Copilot Chat extension.
  3. Delete .github/copilot-instructions.md.
Show answer and explanation

Correct answer: A. From Visual Studio Code, modify your GitHub Copilot Chat user settings.

Chat user settings. GitHub Copilot repository-level instructions in .github/copilot-instructions.md apply globally to all users by default. To disable them for yourself without affecting others, you modify your personal GitHub Copilot Chat user settings in Visual Studio Code, which allows individual users to override repository-level instructions at the user level.

Why the other options are wrong

  • B. The –debug flag is used for debugging extension behavior, not for disabling repository-level instructions.
  • C. Deleting the file would affect all users, not just yourself, which violates the requirement to not affect other users.

Question 2

You have an Azure SQL database that contains the following SQL graph tables:

• A NODE table named dbo. Person

• An EDGE table named dbo. Knows

Each row in dbo. Person contains the following columns:

PersonID (int) DisplayName (nvarchar(100)) You need to use a MATCH operator and exactly two directed Knows relationships to return the PersonID and DisplayName of people that are reachable from the person identified by an input parameter named @StartPersonId.

Which Transact-SQL query should you use?

Exhibit for question 2

Show answer and explanation

Correct answer: D. SELECT p3.PersonId, p3.DisplayName FROM

AS p1, dbo.Knows AS k1, dbo.Person AS p2, dbo.Knows AS k2, dbo.Person AS p3 WHERE p1.PersonId = @StartPersonId AND MATCH(p1-(k1)- >p2-(k2)->p3); The query requires exactly two directed Knows relationships to find people reachable from @StartPersonId. Option D correctly uses a single MATCH clause with the path p1-(k1)- >p2-(k2)->p3, which represents two consecutive directed edges: the first Knows relationship from p1 to p2, and the second from p2 to p3. The WHERE clause filters to start from @StartPersonId and returns p3's PersonId and DisplayName, which are the people reachable through exactly two hops. The syntax properly chains the relationships in one MATCH expression, which is the correct way to specify a multi-hop path in SQL graph queries.

Why the other options are wrong

  • A. This query selects p2 instead of p3, which would only reach people at one hop distance, not two, and it incorrectly filters on DisplayName equality.
  • B. This query uses backwards arrows (p3<-(k2)-p2<-(k1)-p1) indicating reverse direction and uses multiple separate MATCH clauses rather than a single chained path, which is not the correct syntax for this requirement.
  • C. This query uses two separate MATCH clauses instead of chaining them into a single path expression, which is not the proper syntax for expressing a multi-hop relationship in SQL graph.

Question 3

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?

Exhibit for question 3

  1. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘+.*$’, ‘’)
  2. REGEXP_SUBSTR(JSON_VALUE(Payload, ‘$.customer_email’), ‘^[^+]+@.*$=’)
  3. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘+.*@’, ‘@’)
  4. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘+.*’, ‘’)
Show answer and explanation

Correct answer: A. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘+.*$’, ‘’)

‘$.customer_email’), ‘+.*$’, ‘’) The task requires extracting the customer email from the JSON Payload using JSON_VALUE(Payload, '$.customer_email'), then removing the subaddressing portion (everything from the '+' character to just before the '@' symbol). Option A uses REGEXP_REPLACE with the pattern '+.*$' which matches the '+' character followed by any characters until the end of the string, replacing this entire portion with an empty string. This correctly transforms 'user1+promo@contoso.com' to 'user1@contoso.com'. This is the correct approach because it removes the '+' and everything after it up to the end, leaving only the local part before any '+' and the domain intact.

Why the other options are wrong

  • B. REGEXP_SUBSTR is a function for extracting substrings, not replacing; additionally, the pattern and syntax are incorrect for this normalization task.
  • C. This pattern '+.*@' matches from '+' to '@' inclusive and replaces with '@', which would result in 'user1@@contoso.com' (double @), not the desired output.
  • D. This pattern '+.*' matches the '+' and everything after it including the domain, resulting in just 'user1', losing the '@contoso.com' portion entirely.

Question 4

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?

Exhibit for question 4

  1. RETURN DATEDIFF(year, GETDATE(), @OrderDate);
  2. DATEDIFF(month, @orderdate, GETDATE()) / 12
  3. DATEPART(year, GETDATE()) – DATEPART(year, @orderdate)
  4. RETURN DATEDIFF(year, @OrderDate, GETDATE());
Show answer and explanation

Correct answer: D. RETURN DATEDIFF(year, @OrderDate, GETDATE());

In a scalar user-defined function, line 05 must contain a RETURN statement that calculates and returns the result. Option D, RETURN DATEDIFF(year, @OrderDate, GETDATE()), correctly uses DATEDIFF with the year parameter to calculate whole years between the input date and the current date, with the correct parameter order (earlier date first, later date second) to produce a positive integer result. The RETURN keyword is required in the function body to return a value to the caller.

Why the other options are wrong

  • A. Uses DATEDIFF with parameters in reverse order (GETDATE() before @OrderDate), producing a negative result rather than positive.
  • B. Missing the RETURN keyword required in scalar UDF syntax, and dividing months by 12 does not reliably produce whole years (e.g., 11 months / 12 = 0, not 1).
  • C. Subtracting year values only gives the difference in calendar years, not the number of whole years elapsed; for example, 2024-12-31 minus 2023-01-01 would return 1 year but is nearly 2 full years.

Question 5

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?

  1. dab init –database-type mssql –connection-string “secretref:DAB_CONFIG_BASE64” –host-mode Production –config dab-config.json
  2. dab init –database-type mssql –connection-string “@env(‘MSSQL_CONNECTION_STRING’)” –host-mode Production –config da-onfig.json
  3. dab init –database-type mssql –connection-string “secretref:mssql-connectio-tring” –host-mode Production –config dab-config.json
  4. dab init –database-type mssql –connection-string “@env(‘DAB_CONFIG_BASE64’)” –host-mode Production –config dab-config.json
Show answer and explanation

Correct answer: B. dab init –database-type mssql –connection-string “@env(‘MSSQL_CONNECTION_STRING’)” –host-mode Production –config da-onfig.json

“@env(‘MSSQL_CONNECTION_STRING’)” –host-mode Production –config da-onfig.json When initializing DAB in Azure Container Apps with secrets, the connection string should be referenced using the @env() function syntax to read from environment variables that map to Container Apps secrets. The MSSQL_CONNECTION_STRING secret is the appropriate secret to reference for the SQL connection string, not the DAB_CONFIG_BASE64 which is for the configuration itself.

Why the other options are wrong

  • A. The secretref syntax is incorrect for referencing connection strings in DAB; secretref refers to configuration references, not environment variables.
  • C. The secretref syntax is not the correct way to reference Container Apps secrets; the secret name reference is also incorrect.
  • D. DAB_CONFIG_BASE64 is used for the DAB configuration encoding, not for the SQL connection string itself.

Question 6

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?

  1. Apply STRING_ESCAPE(MessageText, ‘json’) before calling REGEXP_SUBSTR.
  2. Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
  3. Add a COLLATE Latin1_General_CS_AS clause to MessageText before calling REGEXP_SUBSTR.
  4. Run TRY_CONVERT(varchar(max), MessageText) before calling REGEXP_SUBSTR.
Show answer and explanation

Correct answer: B. Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.

REGEXP_SUBSTR. REGEXP_SUBSTR has limitations with nvarchar(max) data types in SQL Server/Fabric SQL. To use REGEXP_SUBSTR reliably on a nvarchar(max) column, the data must be cast to a supported type like nvarchar(4000) before the regex operation is applied, ensuring the function can process the string correctly.

Why the other options are wrong

  • A. STRING_ESCAPE is used for escaping special characters for JSON/XML, not for enabling REGEXP_SUBSTR functionality.
  • C. COLLATE clauses affect string comparison and sorting behavior, not REGEXP_SUBSTR compatibility with nvarchar(max).
  • D. TRY_CONVERT to varchar(max) does not solve the nvarchar(max) limitation; the issue requires casting to a smaller, supported size.

Question 7

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?

  1. ALTER TRIGGER
  2. ALTER DATABASE
  3. ALTER SERVER AUDIT SPECIFICATION
  4. DISABLE TRIGGER
  5. ALTER DATABASE AUDIT SPECIFICATION
Show answer and explanation

Correct answer: D. DISABLE TRIGGER

The DISABLE TRIGGER statement is used to disable triggers while keeping them in place for future re-enabling. This allows the trigger object to remain in the database but prevents it from firing during deployment, which is exactly what the requirement specifies.

Why the other options are wrong

  • A. ALTER TRIGGER modifies trigger definitions but does not prevent them from firing.
  • B. ALTER DATABASE is used for database-level properties, not trigger state management.
  • C. ALTER SERVER AUDIT SPECIFICATION is for audit specifications, not triggers.
  • E. ALTER DATABASE AUDIT SPECIFICATION is for audit specifications, not triggers.

Question 8

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?

  1. GitHub Copilot Chat runs queries in a read-only sandbox that is isolated from production database permissions.
  2. GitHub Copilot Chat runs queries by using the developer’s database identity and permissions.
  3. GitHub Copilot Chat filters query results on the client side to remove rows the developer is unauthorized to see.
  4. GitHub Copilot Chat uses different row-level security (RLS) policies than the developer.
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 inherits their permissions. The database engine enforces access control based on the developer's login credentials, so queries can only access data that the developer's role has permission to view.

Why the other options are wrong

  • A. GitHub Copilot Chat does not run in a read-only sandbox; it executes real queries against the database with full permissions based on the user's identity.
  • C. Result filtering on the client side would not be a reliable security mechanism; database-level permission enforcement is the actual control.
  • D. GitHub Copilot Chat does not use different RLS policies; it respects the same policies and permissions as the developer's login.

Question 9

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.

  1. Create an AFTER trigger on dbo. Employee for Data Manipulation Language (DML).
  2. Set Sql_Trigger_MaxBatchSize to 100.
  3. Enable change tracking on the dbo. Employee table.
  4. Enable change tracking at the database level.
  5. Set Sql_Trigger_PollingIntervalMs to 5000.
  6. Enable change data capture (CDC) for dbo. Employee table changes.
Show answer and explanation

Correct answer: C, D

C. Enable change tracking on the dbo. Employee table. D. Enable change tracking at the database level. To enable Azure Functions to react to row changes in SQL with a five-second response time and batch 100 changes per invocation, you must enable change tracking at the database level (D) and then enable it on the specific table dbo.Employee (C). Change tracking provides the mechanism for detecting changes that Azure Functions can poll within the five-second window, with batching handled by the Azure Functions binding configuration.

Why the other options are wrong

  • A. Creating a DML trigger is not the appropriate mechanism for Azure Functions integration; change tracking is the recommended approach for this scenario.
  • B. Sql_Trigger_MaxBatchSize is not a valid SQL Server configuration parameter for this use case.
  • E. Sql_Trigger_PollingIntervalMs is not a standard SQL Server setting; polling intervals are configured in the Azure Functions binding, not the database.
  • F. CDC (Change Data Capture) is more complex and heavyweight than change tracking for this requirement and is not necessary when change tracking provides the needed functionality.

Question 10

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?

Exhibit for question 10

  1. a foreign key
  2. a check constraint
  3. a nonclustered index
  4. a unique constraint
Show answer and explanation

Correct answer: B. a check constraint

To prevent NULL values in the Owner field for all future records, you must add a NOT NULL constraint to the column definition. A check constraint can enforce business rules and value conditions, but the most direct solution is the NOT NULL constraint itself. However, among the given options, a check constraint is the only mechanism listed that can enforce data quality rules at the column level to prevent NULL values when properly configured (e.g., CHECK (Owner IS NOT NULL)). This ensures that any INSERT or UPDATE operation will fail if a NULL value is attempted for the Owner field.

Why the other options are wrong

  • A. A foreign key establishes relationships between tables but does not prevent NULL values in the referencing column.
  • C. A nonclustered index improves query performance but does not enforce constraints on column values or prevent NULLs.
  • D. A unique constraint ensures uniqueness of values but allows NULL values; multiple NULL entries can exist in a unique column.

That was 10 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.

Get the full pack