Practice Free DP-800 Exam Online Questions

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?

Reveal Solution Hide Solution

You have an Azure SQL database that contains tables named dbo.ProduetDocs and dbo.ProductuocsEnbeddings. dbo.ProductOocs contains product documentation and the following columns:

• Docld (int)

• Title (nvdrchdr(200))

• Body (nvarthar(max))

• LastHodified (datetime2)

The documentation is edited throughout the day. dbo.ProductDocsEabeddings contains the following columns:

• Dotid (int)

• ChunkOrder (int)

• ChunkText (nvarchar(aax))

• Embedding (vector (1536))

The current embedding pipeline runs once per night

Vou need to ensure that embeddings are updated every time the underlying documentation content changes. The solution must NOT ‘equire a nightly batch process.

What should you include in the solution?

Reveal Solution Hide Solution

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 (Pll), secrets, and query result sets with any Al 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?

Reveal Solution Hide Solution

You have an SDK-style SQL database project stored in a Git repository. The project targets an Azure SQL database.

The CI build fails with unresolved reference errors when the project references system objects.

You need to update the SQL database project to ensure that dotnet build validates successfully by including the correct system objects in the database model for Azure SQL Database.

Solution: Add the Microsoft.SqlServer.Dacpacs.Azure.Master NuGet package to the project.

Does this meet the goal?

Reveal Solution Hide Solution

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: Run the following Transact-SQL statement.

DELETE FROM dbo.Orders

WHERE OrderDate < DATEADD(nonth, -36, SYSUTCDATETIME());

Does this meet the goal?

Reveal Solution Hide Solution

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_tresteOrder 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, tach 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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

After the INSERT → SET @OrderId = SCOPE_IDENTITY()

Inside CATCH → IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

The correct drag-and-drop choices are:

SET @OrderId = SCOPE_IDENTITY()

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

After the INSERT, the procedure should assign the newly generated identity value to the output parameter by using SCOPE_IDENTITY(). Microsoft documents that SCOPE_IDENTITY() returns the last identity value inserted in the same scope, which makes it the correct choice for returning the new OrderId from the procedure.

Inside the CATCH block, the procedure should use IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

before THROW. This ensures any open transaction is rolled back only when one actually exists, which prevents transaction-state issues and guarantees the failure aborts the transaction cleanly. Keeping THROW after the rollback is also the correct modern pattern because THROW re-raises the error to the caller with the original error information intact, giving consistent error behavior. This matches SQL Server best practice for TRY…CATCH transaction handling.

DRAG DROP

You have an Azure SQL database that supports an OLTP application.

You need to write Transact-SQL code that returns blocking chain details. The output must return only sessions that ate blocked or are blocking other sessions.

How should you complete the 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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

CTE inner source → FROM sys.dm_exec_requests

Join after sys.dm_exec_sessions AS s → LEFT OUTER JOIN sys.dm_exec_requests

Text retrieval → OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)

Input buffer retrieval → OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)

The correct drag-and-drop choices are based on how blocking-chain details are normally assembled in Azure SQL Database.

The CTE must read from sys.dm_exec_requests because the alias er is used with er.session_id and er.blocking_session_id, and those columns come from sys.dm_exec_requests. Microsoft documents that sys.dm_exec_requests returns information about executing requests and includes the blocking_session_id column used to identify blockers.

After FROM sys.dm_exec_sessions AS s, the correct join is LEFT OUTER JOIN sys.dm_exec_requests

so the query can still return sessions from sys.dm_exec_sessions even when a current request row is missing. This is useful when showing sessions that are blocked or blocking, while still attempting to attach current request details when available.

For batch text, use OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) because Microsoft documents sys.dm_exec_sql_text(sql_handle) as the function that returns the SQL batch text for the specified sql_handle.

For the input buffer, use OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) because Microsoft documents that sys.dm_exec_input_buffer takes session_id and request_id and returns event_info, which is commonly used when sys.dm_exec_sql_text is null or when you want the last command text.

So the completed code uses:

FROM sys.dm_exec_requests

LEFT OUTER JOIN sys.dm_exec_requests

OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)

OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)

DRAG DROP

You have an Azure SQL database named DB1 that contains two tables named knowledgebase and query_cache. knowledge_base contains support articles and embeddings. query_cache contains chat questions, responses, and embeddings DB1 supports an Al-enabled chat agent.

You need to design a solution that meets the following requirements:

• Serializes the retrieved rows from knowledee_base

• Extracts the answer field from the response

• Extracts the embeddings to store in query_cache

You will call the external large language model (LLM) by using the sp_irwoke_external_re standpoint stored procedure.

Which Transact-SGL commands should you use for each requirement? To answer, drag the appropriate commands to the correct requirements. Each command may be used once, mote 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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

The correct mapping is:

FOR JSON PATH

JSON_VALUE

JSON_QUERY

To serialize the retrieved rows from knowledge_base, the correct command is FOR JSON PATH. Microsoft documents that FOR JSON formats query results as JSON, and PATH mode is the standard

way to shape relational rows into JSON for downstream application or AI use.

To extract the answer field from the response, the correct command is JSON_VALUE because answer is a single scalar field. Microsoft states that JSON_VALUE is used to extract a scalar value from JSON text.

To extract the embeddings to store in query_cache, the correct command is JSON_QUERY because embeddings are returned as a JSON array, not a scalar. Microsoft states that JSON_QUERY extracts an object or array from JSON text, which is exactly the right behavior for an embeddings payload.

The unused options are not the best fit here:

OPENJSON is mainly for shredding JSON into rows and columns.

AI_GENERATE_CHUNKS is for chunking text, not extracting fields from a response payload.

VECTOR_DISTANCE computes similarity between vectors and is unrelated to JSON extraction.

FOR XML PATH produces XML, not JSON.

You have a SQL database in Microsoft Fabric that contains a column named Payload. pay load 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, [email protected].

You need to return a normalized email value that removes the subaddressing, for example, user! + [email protected] must be normalized to [email protected].

Which Transact SQL expression should you use?

Reveal Solution Hide Solution

HOTSPOT

Your company has an ecommerce catalog in a Microsoft SQL Server 202b database named SalesDB SalesDB contains a table named products, products contains the following columns:

• product.id (int)

• product_name (nvarchar(200))

• description (nvarchar(max))

• category (nvarchar(50))

• brand (nvarchar(W))

• price (decimal)

• sku (nvarchar(40))

The description fields ate updated dairy by a content pipeline, and price can change multiple times per day. You want customers to be able to submit natural language queries and apply structured filters for brand and price. You plan to store embeddings in a new VECTOR(1536) column and use VECTOR_SEARCH(… METRIC=’ cosine’ …).

For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

The first statement is Yes. Embeddings are used to represent the semantic meaning of content, and vector search is for conceptually similar matches over that content. Here, the semantically meaningful fields are product_name, category, and description. Using those together supports natural-language search, while brand and price can be handled as structured filters outside the embedding itself. This is an inference from Microsoft’s guidance that vector search works over embeddings representing content meaning, while filters remain part of the nonvector query pipeline.

The second statement is No. price changes multiple times per day and is a structured numeric attribute, not stable semantic content. Since the requirement already says customers can apply structured filters for brand and price, price does not need to be embedded into the text. Embedding volatile numeric values would also make embeddings stale faster without improving the semantic-search objective. This is again an inference grounded in Microsoft’s distinction between vector similarity over content and filtering/sorting over nonvector fields.

The third statement is Yes. In SQL Server’s vector type, the default underlying base type is float32 unless float16 is specified explicitly.