Free Microsoft DP-800 Practice Test Questions MCQs

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 0orderDate 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?

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());

You have an Azure SQL database that contains a table named dbo.orders, dbo.orders contains a column named createDate that stores order creation dates. You need to create a stored procedure that filters Orders by CreateDate for a single calendar day. The solution must be SARGable.
How should you complete the Transact-SQL code? 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.

You have a Microsoft SQL Server 2025 instance that has a managed identity enabled. You have a database that contains a table named dbo.ManualChunks. dbo.ManualChunks contains product manuals.
A retrieval query already returns the top five matching chunks as nvarchar(max) text. You need to call an Azure OpenAI REST endpoint for chat completions. The solution must provide the highest level of security.
You write the following Transact-SG1 code.
What should you insert at line 02?

A. Option A

B. Option B

C. Option C

D. Option D

E. Option E

You have a SQL database in Microsoft Fabric that contains a table named WebSite. Logs. WebSite.Logs stores application telemetry data. Website.Logs contains a nvarehar(iMx) column named log that stores JSON documents You have a daily report that filters by the $.severity JSON property and returns Logld. LogDateTime, and log. The report frequently causes full table scans. You need to modify Website. Logs to support efficient filtering by $. severity and avoid key lookups for the columns returned by the report. How should you complete the Transact-SQL code to avoid full table scans? 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.

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-instruetions.md

You have an Azure SQL database that supports the OLTP workload of an order-processing application.
During a 10-minute incident window, you run a dynamic management view query and discover the following:
Session 72 is sleeping with open_transaction_count = 1.
Multiple other sessions show blocking_session_id = 72 in sys.dm_exec_requests.
sys.dm_exec_input_buffer(72, NULL) returns only BEGIN TRANSACTION UPDATE Sales.Orders.
Users report that updates to Sales.Orders intermittently time out during the incident window. The timeouts stop only after you manually terminate session 72.
What is a possible cause of the blocking?

A. A long-running SELECT statement is blocking writers.

B. Session 72 caused a deadlock.

C. An explicit transaction was started but not committed or rolled back.

D. A lock escalation occurred.

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 HATCH 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. Option A

B. Option B

C. Option C

D. Option D

You have an Azure Al Search service and an index named hotels that includes a vector Held named DescriptionVector.
You query hotels by using the Search Documents REST API.
You add semantic ranking to the hybrid search query and discover that some queries return fewer results than expected, and captions and answers are missing.
You need to complete the hybrid search request to meet the following requirements:
• Include more documents when ranking.
• Always include captions and answers.

You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders, dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month. You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.orders. Solution: Identify the partition number for the oldest month, and then run the following Transact-SQL statement. TRUNCATE TABIE dbo.Orders WITH (PARTITIONS (partition number)); Does this meet the goal?

A. Yes

B. No

You have an Azure SQL database that contains a table named stores, stores contains a column named description and a vector column named embedding.
You need to implement a hybrid search query that meets the following requirements: • Uses full-text search on description for the keyword portion
• Returns the top 20 results based on a combined score that uses a weighted formula of 60% vector distance and 40% full-text rank
How should you configure the query components? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point