(2026) DP-800 Exam Dumps, Practice Test Questions BUNDLE PACK
Microsoft Certified: SQL AI Developer Certification DP-800 Sample Questions Reliable
NEW QUESTION # 62
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? lo 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.
Answer:
Explanation:
Explanation:
The correct drag-and-drop mapping is based on the documented behavior of the new SQL regular expression functions.
For RawNumber , the requirement is to return the first substring in MessageRaw that matches the phone- number pattern, or NULL if nothing matches. That is exactly what REGEXP_SUBSTR does: it extracts the matched substring from the source text. Microsoft documents REGEXP_SUBSTR as the function that
"extracts parts of a string based on a regular expression pattern" and returns the matched occurrence.
For DigitsOnly , you first need the matched phone substring, then remove all non-digit characters from it. The correct combined expression is REGEXP_REPLACE( REGEXP_SUBSTR( so the matched substring is passed into REGEXP_REPLACE, which strips characters matching \D. Microsoft documents REGEXP_REPLACE as returning a modified source string with matching patterns replaced. Using it around REGEXP_SUBSTR satisfies the "digits only or NULL" requirement in one select expression.
For PhoneStatus , the requirement is simply to return Valid when a phone number exists and Missing otherwise. That is a Boolean test, so REGEXP_LIKE is the right function. Microsoft documents REGEXP_LIKE as returning a Boolean value indicating whether the input matches the regex pattern.
NEW QUESTION # 63
Hotspot Question
Your company has an ecommerce catalog in a Microsoft SQL Server 2025 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(50))
- price (decimal)
- sku (nvarchar(40))
The description fields are updated daily 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.
Answer:
Explanation:
NEW QUESTION # 64
You have a database named db1. The schema is stored in a Git repository as an SDK-style SQL database project The repository Contains the following GitHub Action workflow.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
* Unit tests run automatically whenever changes are pushed to main. # Yes
* Schema validation occurs during the Build step. # Yes
* Schema validation occurs during the Deploy step. # No
The first statement is Yes . The workflow is configured to trigger on both push to main and pull_request targeting main. The unit-tests job has this condition:
if: github.ref == ' refs/heads/main '
On a push to main , GitHub sets github.ref to refs/heads/main, so the condition is true and the unit-tests job runs. GitHub's workflow syntax documentation confirms that push.branches: [main] triggers on pushes to main, and the github.ref value for branch pushes is the fully qualified ref such as refs/heads/main.
The second statement is Yes . The Build step runs:
dotnet build db1.sqlproj --configuration Release
For an SDK-style SQL database project, the build process produces a .dacpac and validates the database project model as part of compilation/build. Microsoft's SQL database project documentation describes SDK- style SQL projects as the project format used for SQL Database Projects, and Microsoft's command-line build documentation is specifically about building a .dacpac from that SQL project. That means schema-level project validation happens during build.
The third statement is No . The Deploy step uses:
SqlPackage /Action:Publish ...
Microsoft documents that SqlPackage Publish incrementally updates the target database schema to match the source .dacpac. That is a deployment operation, not the primary schema-validation stage of the SQL project source itself. In this workflow, the schema is validated when the SQL project is built into the .dacpac; the deploy step applies that built artifact to the target database.
NEW QUESTION # 65
You have an Azure SQL database that supports an AI-enabled product catalog API. The database contains a table named Sales.Orders. Sales.Orders contains 20 million rows and has a nonclustered index on a column named CreateDate.
The API passes a date parameter named @OrderDate.
You have a stored procedure named get_latest_day_orders that filters by running the following query.
WHERE CAST(CreateDate AS date) = @0rderDate.
A review of the query execution reveals that the query causes an index scan.
You need to modify the predicate to ensure that Microsoft SQL Server can use an index seek on CreateDate for a single day.
Which WHERE clause should you use?
- A. WHERE CreateDate LIKE CONVERT(char(10), @0rderDate, 121) + '%'
- B. WHERE LEFT(CONVERT(varchar(30), CreateDate, 121), 10) = CONVERT(char(10),
@0rderDate, 121) - C. WHERE CONVERT(char(10), CreateDate, 121) = CONVERT(char(10), @OrderDate, 121)
- D. WHERE CreateDate >= @OrderDate AND CreateDate < DATEADD(day, 1, @0rderDate)
Answer: D
Explanation:
To achieve an Index Seek on your CreateDate column, you must remove the CAST function from the column side of the equation. Wrapping a column in a function makes the expression non- SARGable (Searchable Argument), forcing SQL Server to scan the entire index rather than jumping to a specific range.
The Recommended WHERE Clause
To capture all rows for a single day while remaining SARGable, use a range comparison:
WHERE CreateDate >= @OrderDate
AND CreateDate < DATEADD(day, 1, @OrderDate)
Why This Works
Eliminates Functions on Columns: By keeping CreateDate "naked," the query optimizer can use the nonclustered index to find the exact starting point.
Handles Time Components: Since CreatedDate likely contains time data (DATETIME or DATETIME2), this logic captures everything from 00:00:00.000 up to (but not including) midnight of the following day.
Single Day Precision: It creates a "half-open" interval that is the standard best practice for date filtering in SQL Server.
Reference:
https://medium.com/@c.charalambos1998/how-to-design-indexes-in-sql-server-for-faster-query- performance-4a2279a9b512
NEW QUESTION # 66
You have an Azure container app named app1-contoso-001 that hosts a Data API builder (DAB) container in front of an Azure SQL database.
You add an entity named Todo that uses a source named dbo.todos.
Which URL pattern should clients use to access the Todo REST endpoint?
- A. https://appl-contoso-001.azurestaticapps.net/api/Todo
- B. https://appl-contoso-001.azurestaticapps.net/data-api/Todo
- C. https://appl-contoso-001.azurestaticapps.net/data-api/api/Todo
- D. https://appl-contoso-001.azurestaticapps.net/data-api/graphq1/Todo
Answer: A
Explanation:
To access the REST endpoint for the Todo entity, clients should use the following URL pattern:
https://<your-container-app-url>/api/Todo
Base URL: The unique FQDN of your Azure Container App.
Path Prefix: By default, DAB uses /api for RESTful services.
Entity Name: You must use the Entity Name defined in the configuration (Todo), not the underlying source name (dbo.Todos).
Reference:
https://learn.microsoft.com/en-us/azure/container-apps/connect-apps
NEW QUESTION # 67
You have an Azure SQL database that contains tables named dbo.Tickets and dbo.TicketNotes.
dbo.Tickets contains support tickets and dbo.TicketNotes contains ticket notes.
A retrieval query returns the top five relevant ticket notes for a user question.
You plan to implement a Retrieval Augmented Generation (RAG) pattern that meets the following requirements:
- Formats the retrieved relational data for large language model (LLM)
processing
- Sends the user question and retrieved context to an Azure OpenAI REST endpoint for chat completions
- Extracts the response text from the LLM response
Which Transact-SQL function should you use to extract the response text?
- A. OPENJSON(@response)
- B. JSON_VALUE(@response, '$.choices[0].message.content')
- C. JSON_QUERY(@response, '$.choices')
- D. JSON_MODIFY(@response, '$.result')
Answer: B
Explanation:
The Transact-SQL function used to extract the response text from the Azure OpenAI REST endpoint is JSON_VALUE.
When you call the Azure OpenAI REST endpoint using sp_invoke_external_rest_endpoint, the response is returned as a JSON string. To isolate the actual text content from the LLM, you must parse this JSON structure.
Function: JSON_VALUE(response_body, '$.choices[0].message.content')
Purpose: It extracts a scalar (text) value from a JSON string.
Path: In the OpenAI schema, the generated response is always located at
$.choices[0].message.content.
Reference:
https://pub.towardsai.net/mastering-retrieval-augmented-generation-from-zero-to-expert-in-rag- for-quickly-building-a-08141a308836
NEW QUESTION # 68
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.
- B.
- C.
- D.
Answer: D
Explanation:
Create the Database User and Grant Permissions
You must use Transact-SQL (T-SQL) to create a "contained database user" that represents the managed identity and assign it to the appropriate database roles for read/write access.
Connect to the database using an account with Entra Admin privileges (e.g., via Azure Data Studio or the Azure portal's Query editor).
Execute the following T-SQL commands in the context of the specific database (not the master database):
-- Create a user for the User-Assigned Managed Identity
-- Replace with the exact name of your user-assigned managed identity
CREATE USER [] FROM EXTERNAL PROVIDER;
-- Add the user to the db_datareader role (Read permissions)
ALTER ROLE db_datareader ADD MEMBER [];
-- Add the user to the db_datawriter role (Write permissions)
ALTER ROLE db_datawriter ADD MEMBER [];
Reference:
https://medium.com/@yuvchauhan15/managing-access-in-azure-sql-granting-read-and-write- privileges-to-a-managed-identity-8dacc4d205fd
NEW QUESTION # 69
You have an Azure SQL database that contains a table named dbo.Products. dbo.Products contains three columns named Embedding, Category, and Price. The Embedding column is defined as VECTOR(1536).
You use AI_GENERATE_EMBEDDINGS and VECTOR_SEARCH to support semantic search and apply additional filters on two columns named Category and Price.
You plan to change the embedding model from text-embedding-ada-002 to text-embedding-3- small. Existing rows already contain embeddings in the Embedding column.
You need to implement the model change. Applications must be able to use VECTOR_SEARCH without runtime errors.
What should you do first?
- A. Create a vector index on dbo.Products.Embedding.
- B. Convert the Embedding column to nvarchar(max).
- C. Normalize the vector lengths before storing new embeddings.
- D. Regenerate embeddings for the existing rows.
Answer: A
Explanation:
To ensure your applications can transition models without runtime errors while using VECTOR_SEARCH, you must first define a Vector Index that explicitly identifies the dimensions and distance metric.
Since you are moving from text-embedding-ada-002 to text-embedding-3-small, both models default to 1536 dimensions, which matches your existing column definition. To create the index as the first step, use the following SQL:
CREATE VECTOR INDEX idx_embedding ON YourTableName (Embedding)
WITH ( DISTANCE_METRIC = 'COSINE' );
Use code with caution.
Why this works:
Schema Consistency: Because both models use 1536 dimensions, you don't need to alter the VECTOR(1536) column type immediately.
Search Stability: Creating the index allows the engine to optimize the VECTOR_SEARCH function. As long as the incoming query vector (generated by the app) matches the dimensions of the stored vectors, the search will execute without a runtime dimension mismatch error.
Reference:
https://docs.couchbase.com/cloud/n1ql/n1ql-language-reference/vectorfun.html
NEW QUESTION # 70
Which SQL feature is commonly used to integrate AI-generated insights?
- A. Views
- B. External APIs
- C. Triggers
- D. Stored Procedures
Answer: B
Explanation:
AI models (like OpenAI) are typically accessed via APIs, which SQL apps call externally.
NEW QUESTION # 71
Which component handles security in Azure SQL?
- A. Azure CDN
- B. Azure Key Vault
- C. Azure Functions
- D. Azure AI Studio
Answer: B
Explanation:
Azure Key Vault securely stores secrets and keys.
NEW QUESTION # 72
You need to recommend a solution for the development team to retrieve the live metadata. The solution must meet the development requirements.
What should you include in the recommendation?
- A. Add the schema to a GitHub Copilot instruction file.
- B. Use an MCP server
- C. Include the database project in the code repository.
- D. Export the database schema as a .dacpac file and load the schema into a GitHub Copilot context window.
Answer: B
Explanation:
The best recommendation is to use an MCP server . In the official DP-800 study guide , Microsoft explicitly lists skills such as configuring Model Context Protocol (MCP) tool options in a GitHub Copilot session and connecting to MCP server endpoints, including Microsoft SQL Server and Fabric Lakehouse . That makes MCP the exam-aligned mechanism for enabling AI-assisted tools to work with live database context rather than static snapshots.
This also matches the stated development requirement: the team will use Visual Studio Code and GitHub Copilot and needs to retrieve live metadata from the databases . Microsoft's documentation for GitHub Copilot with the MSSQL extension explains that Copilot works with an active database connection , provides schema-aware suggestions , supports chatting with a connected database, and adapts responses based on the current database context . Microsoft also documents MCP as the standard way for AI tools to connect to external systems and data sources through discoverable tools and endpoints.
The other options do not satisfy the "live metadata" requirement as well:
* A .dacpac is a point-in-time schema artifact, not live metadata.
* A Copilot instruction file provides guidance, not live database discovery.
* Including the database project in the repository helps source control and deployment, but it still does not provide live database metadata by itself.
NEW QUESTION # 73
What challenge does AI introduce to SQL systems?
- A. Data privacy and hallucination risks
- B. Reduced performance always
- C. Increased cost only
- D. No challenges
Answer: A
Explanation:
AI may generate incorrect answers and requires strong governance and validation.
NEW QUESTION # 74
You have an Azure SQL database named SalesDB and an Azure App Service app named sales- api. SalesDB contains a table named dbo.Customers. dbo.Customers contains two columns named CreditCardNumber and TenantId.
Currently, sales-api connects to SalesDB by using SQL authentication with stored username and password.
You need to recommend a solution that meets the following requirements:
- Provides a passwordless method for sales-api to access SalesDB.
- Ensures that credit card numbers are NOT stored as plain text.
What should you include in the recommendation?
- A. managed identities and dynamic data masking
- B. Azure Key Vault and Always Encrypted
- C. Transparent Data Encryption (TDE) and row-level security (RLS)
- D. managed identities and Always Encrypted
Answer: D
Explanation:
To move from SQL authentication to a more secure, passwordless architecture while protecting credit card data, you should implement Azure Managed Identities for authentication and Always Encrypted for data protection.
1. Enable Passwordless Authentication
Replace your stored username and password with a Managed Identity. This allows the App Service to authenticate with the database using its own identity, managed by Azure.
Step 1A: Enable Identity on App Service
Step 1B: Grant Database Access
Set an Entra ID (Active Directory) Admin for your SQL Server.
Connect to your database as that admin and run the following to create a user for your app:
Step 1C: Update Connection String
2. Protect Credit Card Numbers
Use Always Encrypted to ensure credit card numbers are encrypted before they even reach the database. The database engine never sees the plain text.
Step 2A: Store Encryption Keys
Step 2B: Encrypt the Column
Use the Always Encrypted Wizard in SQL Server Management Studio (SSMS).
Select the credit card column and choose Randomized encryption for maximum security (unless you need to search by exact matches, in which case use Deterministic).
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted- database-engine
NEW QUESTION # 75
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. A lock escalation occurred.
- C. An explicit transaction was started but not committed or rolled back.
- D. Session 72 caused a deadlock.
Answer: C
NEW QUESTION # 76
You have an Azure SQL database that contains a table named knowledgebase, knowledgebase stores human resources (HR) policy documents and contains columns named title, content, category, and embedding.
You have an application named App1. App1 queries two relational tables named employee_pnofiles and benefits_enrollnent that contain HR data. App1 hosts a chatbot that calls a large language model (LLM) directly.
Users report that the chatbot answers general HR questions correctly but provides outdated or incorrect answers when policies change. The chatbot also fails to answer questions that reference internal policy documents by title or category.
You need to recommend a Retrieval Augmented Generation (RAG) solution to resolve the chatbot issues.
What should you recommend? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
The correct recommendation is to retrieve grounding data from knowledge_base and, at inference time, generate query embeddings and run a vector similarity search .
The chatbot currently answers some general HR questions but fails when policies change and when users ask about internal policy documents by title or category . That is exactly the kind of problem RAG is meant to solve: ground the LLM in the organization's proprietary content instead of relying on the model's training data or unrelated transactional tables. Microsoft's RAG guidance states that RAG extends LLMs by grounding responses in your own content and that, for agentic retrieval, knowledge bases unify knowledge sources for retrieval.
So the grounding data should come from knowledge_base , because that table stores the HR policy documents and already includes fields like title, content, category, and embedding. Those are the fields directly tied to the missing and outdated policy answers. By contrast:
* employee_profiles and benefits_enrollment are operational HR tables, not the authoritative store for policy-document grounding.
* PDF exports of the policies would be inferior to querying the indexed/structured knowledge base already prepared for retrieval.
* The LLM training data is specifically the wrong source when the issue is outdated internal content.
For the retrieval step, Microsoft's guidance says to use embeddings for vector queries and notes that vector similarity search matches concepts, not exact terms . This is especially important because users ask about policy documents by title or category and also phrase questions in ways that might not exactly match document wording. Generating a query embedding and then running a vector similarity search is the appropriate retrieval step in a RAG pipeline.
NEW QUESTION # 77
Drag and Drop Question
You have an Azure SQL database that contains a table named dbo.Customers. dbo.Customers contains the following columns:
- CustomerId (int)(primary key)
- ProfileJson (nvarchar(max))
You have an application that returns phone numbers in a format of +000 000-000-0000. The phone numbers are stored in ProfileJson.
You need to write a query that returns:
- One row per customer
- A PhoneNUmerals column that contains only the digits
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.
Answer:
Explanation:
NEW QUESTION # 78
You have a Microsoft SQL Server 2025 instance that contains a database named SalesDB SalesDB supports a Retrieval Augmented Generation (RAG) pattern for internal support tickets. The SQL Server instance runs without any outbound network connectivity.
You plan to generate embeddings inside the SQL Server instance and store them in a table for vector similarity queries.
You need to ensure that only a database user account named AlApplicationUser can run embedding generation by using the model.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Create a database audit specification on SalesDB owned by AlApplicationUser.
- B. Grant the control permission on SalesDB to AlApplicationUser.
- C. Create an external model project by using ONNX runtime and local paths.
- D. Grant the execute permission on the external model project to AlApplicationUser.
- E. Create an external model project that points to a Microsoft Foundry REST endpoint.
Answer: C,D
Explanation:
Because the SQL Server 2025 instance has no outbound network connectivity , the embedding model cannot rely on a remote REST endpoint such as Azure AI Foundry or Azure OpenAI. Microsoft's CREATE EXTERNAL MODEL documentation includes a local deployment pattern using ONNX Runtime running locally with local runtime/model paths . That is the right design when embeddings must be generated inside the SQL Server instance without external network access. Microsoft explicitly documents a local ONNX Runtime example for SQL Server 2025 and notes the required local runtime setup and model path configuration.
The permission requirement is handled by granting the application user access to use the external embeddings model. Microsoft's AI_GENERATE_EMBEDDINGS documentation states that, as a prerequisite, you must create an external model of type EMBEDDINGS that is accessible via the correct grants, roles, and/or permissions . Among the choices, the exam-appropriate action is to grant execute permission on the external model project to AlApplicationUser so only that database user can run embedding generation through the model.
NEW QUESTION # 79
Hotspot Question
You have an Azure SQL database that contains the following tables and columns.
Embeddings in the NotesEmbeddings and DescriptionEmbeddings tables have been generated from values in the Description and Notes columns of the Articles table by using different chunk sizes.
You need to perform approximate nearest neighbor (ANN) queries across both embedding tables.
The solution must minimize the impact of using different chunk sizes.
What should you use? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 80
What is Retrieval-Augmented Generation (RAG)?
- A. A query optimization technique
- B. A backup strategy
- C. A SQL indexing method
- D. Combining LLMs with external data sources
Answer: D
NEW QUESTION # 81
......
Microsoft DP-800 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
Prepare for the Actual Microsoft Certified: SQL AI Developer DP-800 Exam Practice Materials Collection: https://www.testkingpdf.com/DP-800-testking-pdf-torrent.html
