Microsoft DP-800 Online Practice Questions and Exam Preparation

Microsoft DP-800 Online Questions & Answers

• Question 1:

  HOTSPOT

  You are creating a table that will store customer profiles.

  You have the following Transact-SQL code.

  

  For each of the following statements, select Yes if the statement is true.

  Otherwise, select No. NOTE: Each correct selection Is worth one point.

  

  

• Question 2:

  You need to implement a hybrid query in Azure SQL that combines:

  vector similarity over an Embedding column keyword relevance over a Description column

  The final ranking formula must use the vector distance and the full-text ranking value returned by the keyword search.

  Which full-text function should you use for the keyword portion?

  A. CONTAINS
  B. FREETEXT
  C. CONTAINSTABLE
  D. LIKE
  C. CONTAINSTABLE

• Question 3:

  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 ieferences 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.Mastet NuGet package to the project.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 4:

  DRAG DROP

  You have a Microsoft SQL Servei 2025 database that contains a table named dbo.Customer-Messages, dbo. Customer-Messages contains two columns named HessagelD (int) and MessageRaw (nvarchar(iux)). MessageRaw can contain a phone number in multiple formats. and some rows do NOT contain a phone number. You need to write a single SELECT query that meets the following requirements:

  - The query must return Message ID, RawNumber. DigitsOnly, and PhoneStatus.

  - RawNumber must contain the first substring that matches a phone-number pattern, or NULL if no match exists.

  - DigitsOnly must remove all non-digit characters from RawNumber. or return NULL.

  - PhoneStatus must return Valid when a phone number exists in MessageRaw. otherwise return Missing.

  How should you complete the Transact-SQL query? 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.

  Select and Place:

  

  

• Question 5:

  You have an Azure SQL database that contains a table named dbo.Telemetry. dbo.Telemetry has a column named Payload that stores JSON documents in nvarchar(max).

  A reporting query frequently filters by JSON_VALUE(Payload, '$.deviceType') and returns TelemetryId, EventTime, and Payload. The query currently performs full table scans.

  You need to reduce query cost and avoid lookups for the returned columns.

  What should you do?

  A. Create a computed column that uses JSON_QUERY(Payload, '$.deviceType') and create a clustered index on the computed column.
  B. Create a computed column that uses JSON_VALUE(Payload, '$.deviceType') and create a nonclustered index that includes TelemetryId, EventTime, and Payload.
  C. Create a check constraint on JSON_VALUE(Payload, '$.deviceType').
  D. Convert Payload to xml and create a primary XML index.
  B. Create a computed column that uses JSON_VALUE(Payload, '$.deviceType') and create a nonclustered index that includes TelemetryId, EventTime, and Payload.

• Question 6:

  You have an Azure SQL database.

  You deploy Data API builder (DAB) to Azure Container Apps by using the mcr.nicrosoft.com/azure-databases /data-api-builder:latest image.

  You have the following Container Apps secrets:

  - MSSQL_COMNECTiON_STRrNG that maps to the SQL connection string.

  - DAB_C0HFT6_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
  B. dab init --database-type mssql --connection-string " @env( ' MSSQL_CONNECTION_STRING ' ) " --host-mode Production --config dab-config.json

• Question 7:

  You have a database named DB1. The schema is stored in a Git repository as an SDK-style SQL database project.

  You have a GitHub Actions workflow that already runs dotnet build and produces a database artifact.

  You need to add a deployment step that publishes the dacpac file to an Azure SQL database by using the secrets stored in GitHub repository secrets.

  What should you include in the workflow?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

• Question 8:

  HOTSPOT

  You need to create a table in the database to store the telemetry data.

  You have the following Transact-SQL code.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  

  

• Question 9:

  You have an Azure SQL database named SalesDB on a logical server named sales-sql01.

  You have an Azure App Service web app named OrderApi that connects to SalesDB by using SQL authentication.

  You enable a user-assigned managed identity named OrderApi-Id for OrderApi.

  You need to configure OrderApi to connect to SalesDB by using Microsoft Entra authentication.

  The managed identity must have read and write permissions to SalesDB.

  Which Transact-SQL statements should you run in SalesDB?

  A. CREATE LOGIN [OrderApi-Id] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id]; ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];
  B. CREATE USER [OrderApi-Id] WITH PASSWORD = ' P@ssw0rd! ' ; ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id]; ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];
  C. CREATE USER [OrderApi-Id] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id]; ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];
  D. CREATE LOGIN [OrderApi-Id] WITH PASSWORD = ' P@ssw0rd! ' ; ALTER SERVER ROLE sysadmin ADD MEMBER [OrderApi-Id];
  C. CREATE USER [OrderApi-Id] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [OrderApi-Id]; ALTER ROLE db_datawriter ADD MEMBER [OrderApi-Id];

• Question 10:

  You need a reusable number generator for invoice identifiers across multiple tables in an Azure SQL database.

  The numbering object must be created independently of any table, and inserts should retrieve the next number when needed.

  Which pair should you use?

  A. IDENTITY column and DBCC CHECKIDENT
  B. CREATE SEQUENCE and NEXT VALUE FOR
  C. UNIQUEIDENTIFIER and NEWID()
  D. ROW_NUMBER() and ORDER BY
  B. CREATE SEQUENCE and NEXT VALUE FOR