📘 Free DP-800 Sample Questions
DRAG DROP -
You have an Azure SQL database that contains a table named dbo.Orders.
You have an application that calls a stored procedure named dbo.usp_CreateOrder to insert rows into dbo.Orders.
When an insert fails, the application receives inconsistent error details.
You need to implement error handling to ensure that any failures inside the procedure abort the transaction and
return a consistent error to the caller.
How should you complete the stored procedure? To answer, drag the appropriate values to the correct targets.
Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or
scroll to view content.
NOTE: Each correct selection is worth one point.
A
Correct Answer:
A.
Explanation:
SET @OrderId = SCOPE_IDENTITY()
This function retrieves the last identity value generated in the current session and current scope. It is the
safest way to get the ID of the row you just inserted.
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
This is a critical safety mechanism. It checks if there is an active, uncommitted transaction before attempting
to roll back. If an error occurred and the transaction wasn't committed, this command reverts any partial
changes, preventing "orphaned" or corrupt data.
Why the other answer are incorrect:
BEGIN CATCH
Why it is wrong: The template code in the Answer Area already has the structural BEGIN CATCH statement
written statically right above the blank slot. Dragging another one inside the block would break the T-SQL
block sequence, resulting in a syntax compilation failure.
RAISERROR('CreateOrder failed', 16, 1)
ROLLBACK TRANSACTION (Without the @@TRANCOUNT Check)
THROW
Why it is wrong: Modern T-SQL best practices (and Microsoft certification guidelines) favor the use of THROW
over the legacy RAISERROR function inside catch blocks. THROW re-raises the exact original execution
exception with its true error number, severity, and line-number metadata preserved. RAISERROR overrides
this context with a custom message, making debugging much harder.
Why it is wrong: Executing a raw, un-shielded ROLLBACK TRANSACTION inside a catch block is an anti
pattern. If a runtime error (or a global setting like SET XACT_ABORT ON) automatically aborts and rolls back
the active transaction before control lands in the catch block, calling rollback a second time will cause the
database engine to crash with Error 3903 ("The ROLLBACK TRANSACTION request has no corresponding
BEGIN TRANSACTION.").
Why it is wrong: The keyword THROW; is already statically written in the template code immediately below
the second empty slot. Dragging a second one into that space would result in consecutive duplicate
statements (THROW; THROW;), causing a T-SQL parsing error.
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?
A
From Visual Studio Code, modify your GitHub Copilot Chat user settings.
B
Add a -- debug flag when you start the GitHub Copilot Chat extension.
C
Delete .github/copilot-instructions.md.
Correct Answer:
A. From Visual Studio Code, modify your GitHub Copilot Chat user settings.
Explanation:
A. From Visual Studio Code, modify your GitHub Copilot Chat user settings.
Option A allows you to turn off the loading of repository-level custom instructions locally. In Visual Studio
Code, you can navigate to your user-level editor settings (Settings > Extensions > GitHub Copilot / Copilot
Chat) and uncheck or disable the configuration that automatically applies repository instruction files. Because
this modification is done in your individual User Settings, it applies exclusively to your local environment and
will not alter the repository or affect any other team members.
Why the other options are incorrect:
files like repository instructions.
Option B (Add a --debug flag...) is incorrect. Command-line flags like --debug are used for viewing detailed
execution logs and diagnostic output. They do not control the loading or parsing of feature-level functional
Option C (Delete .github/copilot-instructions.md) is incorrect. If you delete this file from your workspace and
commit/push the change, it completely removes the custom instructions for the entire team. This directly
violates the requirement to disable it without affecting other users.
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?
A
A
SELECT p2.PersonId, @startPersonId
FROM dbo.Person AS p1, dbo.Knows AS k1, dbo.Person AS p2, dbo.Knows AS k2, dbo.Person AS p3
WHERE p1.DisplayName = p2.DisplayName
AND MATCH(p1-(k1)->p2-(k2)->p3);
SELECT p3.PersonId, p3.DisplayName FROM dbo.Person AS p1
JOIN dbo.Knows AS k1 ON 1 = 1
JOIN dbo.Person AS p2 ON 1 = 1
JOIN dbo.Knows AS k2 ON 1 = 1
JOIN dbo.Person AS p3 ON 1 = 1
WHERE p1.PersonId = @startpersonId
AND MATCH(p3 <- (k2)-p2 <- (k1)-p1);
B
B
SELECT p3.PersonId, p3.DisplayName
FROM dbo.Person 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) AND MATCH(p2-(k2)->p3);
C
C
SELECT p3.PersonId, p3.DisplayName
FROM dbo.Person 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);
Correct Answer:
C. C
SELECT p3.PersonId, p3.DisplayName
FROM dbo.Person 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);
Explanation:
FROM dbo.Person AS p1, dbo.Knows AS k1, ...: This declares the tables involved. In graph terms, Person are the
Nodes and Knows are the Edges (relationships) connecting them.
WHERE p1.PersonId = @StartPersonId: This sets the starting point of the search to a specific user provided by
the variable @StartPersonId.
AND MATCH(p1-(k1)->p2-(k2)->p3): This is the core graph pattern. It instructs the database to traverse the
graph starting at p1, following a "knows" edge (k1) to a person (p2), and then another "knows" edge (k2) to a
third person (p3).
Why the other Options are Incorrect:
Option A: This logic attempts to filter columns using a redundant tautology predicate (WHERE
p1.DisplayName = p1.DisplayName). Because it completely omits the required input filter constraint
(@StartPersonId), the engine runs blind without an indexed anchor point.
Option B: This reverses the physical execution trajectory inside the graph pattern topology. Writing the
relationship blocks backward (p3-(k2)->p2-(k1)->p1) implies that the target entities know the root variable,
rather than retrieving the accounts that are reachable from the input parameter user.
Option C: This splits the traversal layout into two disconnected, isolated MATCH() constraints separated by an
AND operator. SQL Graph does not support evaluating disparate independent match statements across
shared variable streams in this format; chaining multi-hop structures directly inside a single single match
block is syntactically mandatory.
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.
{
"date": "2020-01-25",
"customer_email": "user@contoso.com",
......
}
Data analysis shows that some customers have subaddressing in their email address, for example,
[email protected].
You need to return a normalized email value that removes the subaddressing, for example, user1 [email protected]
must be normalized to [email protected].
Which Transact-SQL expression should you use?
A
REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*$’, ‘’)
B
REGEXP_SUBSTR(JSON_VALUE(Payload, ‘$.customer_email’), ‘^[^+]+@.*$=’)
C
REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*@’, ‘@’)
D
REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*’, ‘’)
Correct Answer:
C. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*@’, ‘@’)
Explanation:
C. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*@’, ‘@’)
JSON_VALUE(Payload, ‘$.customer_email’): This correctly extracts the email string from your JSON object.
‘\+.*@’ (The Regex Pattern):
\+ matches the literal plus sign (escaped because + is a special character in regex).
.* matches any characters following the plus sign.
@ stops the match at the domain separator.
‘@’ (The Replacement): By replacing the entire +... segment with @, you effectively "stitch" the username part
directly to the domain part, resulting in the normalized email.
Comparison with Other Options
A and D: These use ‘\+.*$’ or ‘\+.*’. If you replace the + and everything after it with an empty string, you would
end up with user1@contoso.com only if the regex is very carefully constructed to stop before the @. If it
removes the @ and the domain, you would lose the essential part of the email address.
B: REGEXP_SUBSTR is used to extract a matching string rather than replace a portion of it. While you could
technically extract parts and concatenate them, it is far less efficient than using a simple replacement.
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.
01 CREATE FUNCTION dbo.ufnYearsSinceOrder (@OrderDate datetime2)
02 RETURNS int
03 AS
04 BEGIN
05
06 END
What should you insert at line 05?
A
RETURN DATEDIFF(year, GETDATE(), @OrderDate);
B
DATEDIFF(month, @orderdate, GETDATE()) / 12
C
DATEPART(year, GETDATE()) - DATEPART(year, @orderdate)
D
RETURN DATEDIFF(year, @OrderDate, GETDATE());
Correct Answer:
D. RETURN DATEDIFF(year, @OrderDate, GETDATE());
Explanation:
D. RETURN DATEDIFF(year, @OrderDate, GETDATE()).
DATEDIFF Syntax: The Microsoft Transact-SQL DATEDIFF function uses the syntax DATEDIFF(datepart,
startdate, enddate). It measures the boundaries crossed from the earlier date (startdate) to the later date
(enddate).Correct Order: To return a positive integer representing the years elapsed up to the current date,
the earlier input parameter (@OrderDate) must be the startdate and the current date/time (GETDATE()) must
be the enddate.Scalar UDF Requirement: Because this line is part of a scalar User-Defined Function (UDF), it
requires the RETURN keyword to output the computed value back to the caller.
Why the other choices are incorrect:
Option A reverses the order of the dates (GETDATE() as start, @OrderDate as end), which would result in a
negative integer.
Option B and Option C are missing the mandatory RETURN keyword required by a T-SQL scalar user-defined
function to exit and pass back the data.
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?
A
dab init --database-type mssql --connection-string “secretref:DAB_CONFIG_BASE64” --host-mode
Production --config dab-config.json
B
dab init --database-type mssql --connection-string “@env(‘MSSQL_CONNECTION_STRING’)” --host-mode
Production --config dab-config.json
C
dab init --database-type mssql --connection-string “secretref:mssql-connection-string” --host-mode
Production --config dab-config.json
D
dab init --database-type mssql --connection-string “@env(‘DAB_CONFIG_BASE64’)” --host-mode Production --config dab-config.json
Correct Answer:
B. dab init --database-type mssql --connection-string “@env(‘MSSQL_CONNECTION_STRING’)” --host-mode
Production --config dab-config.json
Explanation:
B. dab init -- database-type mssql -- connection-string "@env('MSSQL_CONNECTION_STRING')" -- host-
mode Production -- config dab-config.json
The @env() function: Data API builder (DAB) natively supports the @env() string substitution function to
securely pass configuration settings (like connection strings) into the dab-config.json file at runtime instead of
hardcoding them.
Targeting the Right Secret: The environment variable mapping to your SQL database connection string is
named MSSQL_CONNECTION_STRING. Therefore, you must specify @env('MSSQL_CONNECTION_STRING')
to read that precise value.
Why the other choices are incorrect:
Options A & C: The secretref: syntax is a native platform feature used by Azure Container Apps or Kubernetes
within YAML definitions, but it is not understood natively by the Data API builder (DAB) command-line
interface (dab init).
Option D: This targets the wrong secret environment variable (DAB_CONFIG_BASE64), which contains the
base64-encoded configuration file of DAB itself, rather than the database connection string.
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?
A
Apply STRING_ESCAPE(MessageText, ‘json’) before calling REGEXP_SUBSTR.
B
Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
C
Add a COLLATE Latin1_General_CS_AS clause to MessageText before calling REGEXP_SUBSTR.
D
Run TRY_CONVERT(varchar(max), MessageText) before calling REGEXP_SUBSTR.
Correct Answer:
B. Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
Explanation:
B. Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
Why this is the correct choice:
Why the other choices are incorrect:
Data Type Limitations: In the T-SQL implementation of Regular Expression functions (such as
REGEXP_SUBSTR), large object data types like nvarchar(max) or varchar(max) are not supported directly as
the input string_expression.
Explicit Casting: Because the problem specifies that the required ID is guaranteed to reside within the first
paragraph of the column, casting the nvarchar(max) data down to a standard size like nvarchar(4000) ensures
compliance with the function's argument limits without losing the necessary search text.
Option A (STRING_ESCAPE) adds control characters (like escaping slashes) to make data safe for JSON
formatting. It does not alter the underlying max data type limit or help with regular expression matching.
Option C (COLLATE) changes the collation rules (such as case sensitivity or accent sensitivity), but it keeps
the underlying data type as nvarchar(max), which remains incompatible with the function.
Option D (TRY_CONVERT) translates the data to varchar(max). This maintains a large-object max specifier that
is still incompatible with the regular expression engine and introduces a risk of cutting off non-ASCII
Unicode characters.
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?
A
ALTER TRIGGER
B
ALTER DATABASE
C
ALTER SERVER AUDIT SPECIFICATION
D
DISABLE TRIGGER
E
ALTER DATABASE AUDIT SPECIFICATION
Correct Answer:
D. DISABLE TRIGGER
Explanation:
Why others are incorrect:
ALTER TRIGGER: Used to modify the definition (the code inside) of an existing trigger, not to toggle its active
state.
ALTER DATABASE: Used to modify database settings, not individual trigger states.
ALTER SERVER AUDIT SPECIFICATION / ALTER DATABASE AUDIT SPECIFICATION: These are used for
auditing features in SQL Server (tracking events for compliance/security) and are separate objects from
standard DDL triggers.
D. DISABLE TRIGGER
To prevent a DDL trigger from firing while keeping the object present in the database, you use the DISABLE
TRIGGER statement.
Syntax: You would execute DISABLE TRIGGER ddl_Audit ON DATABASE; (since ddl_Audit is a database-level
trigger).
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?
A
GitHub Copilot Chat runs queries in a read-only sandbox that is isolated from production database
permissions.
B
GitHub Copilot Chat runs queries by using the developer’s database identity and permissions.
C
GitHub Copilot Chat filters query results on the client side to remove rows the developer is unauthorized to
see.
D
GitHub Copilot Chat uses different row-level security (RLS) policies than the developer.
Correct Answer:
B. GitHub Copilot Chat runs queries by using the developer’s database identity and permissions.
Explanation:
B. GitHub Copilot Chat runs queries by using the developer’s database identity and permissions.
Why this is the correct choice:
Native Security Boundaries: When using GitHub Copilot Chat integrated within SQL Server Management
Studio (SSMS), the AI tool does not possess independent server-side credentials or elevated background
service permissions.
Execution Context: Any query generated and executed via Copilot's chat interface implicitly relies on the
active connection established by the user. Therefore, it automatically operates under the developer's current
database security context and login identity. If a user does not have permission to read a table containing
sensitive customer data, any query Copilot attempts to run against that table will be blocked by the engine
itself.
Why the other choices are incorrect:
rather than running in an isolated read-only sandbox.
through client-side row filtration.
Option A is incorrect. Copilot interacts directly with your connected database window or database context
Option C is incorrect. Query security enforcement happens on the server side via the relational engine, not
Option D is incorrect. Copilot respects whatever Row-Level Security (RLS) policies are configured for the
user's active database login; it does not implement separate rules.
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.
A
Create an AFTER trigger on dbo.Employee for Data Manipulation Language (DML).
B
Set Sql_Trigger_MaxBatchSize to 100.
C
Enable change tracking on the dbo.Employee table.
D
Enable change tracking at the database level.
E
Set Sql_Trigger_PollingIntervalMs to 5000.
F
Enable change data capture (CDC) for dbo.Employee table changes.
Correct Answer:
C. Enable change tracking on the dbo.Employee table.
Explanation:
C. Enable change tracking on the dbo.Employee table.
D. Enable change tracking at the database level.
Why these are the correct choices:
To configure an event-driven architecture using the Azure SQL trigger for Azure Functions, you must enable
SQL Change Tracking within the target database. Setting up change tracking requires exactly these two
database-side modifications:
1. At the Database level (Option D): Turns on the native tracking engine for the database host (ALTER
DATABASE AdventureWorksDB SET CHANGE_TRACKING = ON).
2. At the Table level (Option C): Instructs the engine to track explicit row modifications inside the target
table (ALTER TABLE dbo.Employee ENABLE CHANGE_TRACKING).
Why the other choices are incorrect:
Options B and E (Sql_Trigger_MaxBatchSize and Sql_Trigger_PollingIntervalMs) are the correct configuration
settings to handle the 100-batch limit and 5-second polling interval requirements, but they are Application
Settings managed within the Azure Functions host configuration (local.settings.json or Azure Portal
Configuration pane)—not database configurations.
Option A is incorrect. The Azure SQL Function trigger relies natively on internal SQL Server change tracking
tables and query leases, not custom user-managed DML database AFTER triggers.
Option F is incorrect. Change Data Capture (CDC) is an alternative data-tracking feature, but it is not natively
supported or utilized by the Azure Functions Azure SQL trigger extension.
Questions: 1-10 out of 147
Continue Full Practice..
GET ALL 147 QUESTIONS