Latest [Jul 24, 2026] Real Microsoft DP-800 Exam Dumps Questions
DP-800 Dumps To Pass Microsoft Certified: SQL AI Developer Exam in One Day (Updated 118 Questions)
Microsoft DP-800 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
NEW QUESTION # 29
What is a key benefit of embedding vectors in SQL databases?
- A. Semantic search capability
- B. Improved indexing
- C. Faster backups
- D. Reduced storage
Answer: A
Explanation:
Vector embeddings enable similarity search, allowing semantic queries beyond exact matches.
NEW QUESTION # 30
Case Study 1 - Contoso
Existing Environment
Azure Environment
Contoso has an Azure subscription in North Europe that contains the corporate infrastructure.
The current infrastructure contains a Microsoft SQL Server 2017 database. The database contains the following tables.
The FeedbackJsoncolumn has a full-text index and stores JSON documents in the following format.
The support staff at Contoso never has the UNMASKpermission.
Problem Statements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the following:
* AI workloads
* Vector search
* Modernized API access
* Retrieval Augmented Generation (RAG) pipelines
Sometimes the ingestion pipeline fails due to malformed JSON and duplicate payloads.
The engineers at Contoso report that the following dashboard query runs slowly.
You review the execution plan and discover that the plan shows a clustered index scan.
VehicleIncidentReportsoften contains details about the weather, traffic conditions, and location. Analysts report that it is difficult to find similar incidents based on these details.
Requirements
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support AI-powered semantic search over incident reports.
Security Requirements
Contoso identifies the following security requirements:
* Restrict the support staff from viewing Personally Identifiable Information (PII) data, which is full email addresses and phone numbers.
* Enforce row-level filtering so that analysts see only incidents for the fleets to which they are assigned. The analysts can be assigned to multiple fleets.
Database Performance and Requirements
Contoso identifies the following telemetry requirements:
* Telemetry data must be stored in a partitioned table.
* Telemetry data must provide predictable performance for ingestion and retention operations.
* latitude, longitude, and accuracyJSON properties must be filtered by using an index seek.
Contoso identifies the following maintenance data requirements:
* Ensure that any changes to a row in the MaintenanceEventstable updates the corresponding value in the LastModifiedUtccolumn to the time of the change.
* Avoid recursive updates.
AI Search, Embeddings, and Vector Indexing
Contoso plans to implement semantic search over incident data to meet the following requirements:
* Embeddings must be stored in dedicated Azure SQL Database tables.
* Embeddings must be generated from rich natural language fields.
* Chunking must preserve semantic coherence.
* Hybrid search must combine the following:
- Vector similarity
- Keyword filtering or boosting
Development Requirements
The development team at Contoso will use Microsoft Visual Studio Code and GitHub Copilot and will retrieve live metadata from the databases.
Contoso identifies the following requirements for querying data in the FeedbackJsoncolumn of the CustomerFeedbacktable:
* Extract the customer feedback text from the JSON document.
* Filter rows where the JSON text contains a keyword.
* Calculate a fuzzy similarity score between the feedback text and a known issue description.
* Order the results by similarity score, with the highest score first.
You need to generate embeddings to resolve the issues identified by the analysts. Which column should you use?
- A. SeverityScore
- B. IncidentDescription
- C. VehicleLocation
- D. IncidentType
Answer: B
Explanation:
Scenario: VehicleIncidentReports often contains details about the weather, traffic conditions, and location. Analysts report that it is difficult to find similar incidents based on these details.
The column you should use to generate embeddings is IncidentDescription.
Why this is the correct choice:
Semantic Meaning: Embeddings are designed to capture the semantic meaning of rich, unstructured text. The IncidentDescription column likely contains the detailed natural language notes about weather, traffic, and location that the analysts are struggling to search effectively using standard methods.
Vector Search: By converting the text in IncidentDescription into mathematical vectors (embeddings), you can perform a semantic vector search. This allows the database to find
"similar" incidents based on the context of the description rather than just matching exact keywords or categories.
Incorrect:
[Not A]
VehicleLocation: Typically contains specific coordinates or addresses which are better handled by spatial data types or exact matching.
[Not C]
IncidentType: Is likely a categorical label (e.g., "Fender Bender", "Multi-car pileup") and too simple for meaningful embedding generation.
[Not D]
SeverityScore: Is a numeric value that represents a magnitude, not a semantic concept.
Reference:
https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography
NEW QUESTION # 31
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 # 32
You have an Azure SQL database that supports a customer-facing API. The API calls a stored procedure named dbo.GetCustomerOrders thousands of times per hour.
After a deployment that updated indexes and statistics, users report that the API endpoint backed by dbo.
Getcustomerorders is slower. In Query Store, the same query now has two persisted execution plans. During the last hour, the newer plan had a significantly higher average duration and CPU time than the older plan.
You need to restore the previous performance quickly, without changing the API code.
Which Transact-SQL command should you run?
- A. ALTER DATABASE
- B. DBCC FREEPROCCACHE
- C. EXEC sys.sp_query_store_set_hints
- D. EXEC sp_query_store_force_plan
Answer: D
Explanation:
The scenario says Query Store already shows two persisted execution plans for the same query, and the older plan performed much better than the newer one during the last hour. Microsoft documents that sp_query_store_force_plan is used to force a particular plan for a particular query in Query Store .
That makes it the fastest way to restore the previously good plan without changing application code , which is exactly what the question requires.
Why the other options are not the best fit:
* sp_query_store_set_hints is for adding or updating Query Store hints to influence compilation behavior, but when you already know the exact older good plan, Microsoft points to plan forcing as the direct remedy.
* DBCC FREEPROCCACHE clears cached plans broadly and is disruptive; it does not guarantee a return to the known good plan.
* ALTER DATABASE is too general and does not directly restore the prior execution plan.
So the right Transact-SQL command is:
EXEC sp_query_store_force_plan
using the relevant @query_id and @plan_id from Query Store for the older, better-performing plan.
Microsoft also notes that when a plan is forced, SQL Server tries to use that plan whenever it encounters the query again.
NEW QUESTION # 33
You need to enable similarity search to provide the analysts with the ability to retrieve the most relevant health summary reports. The solution must minimize latency.
What should you include in the solution?
- A. a full-text index on the Fmbeddings (vector (1536)) column
- B. a vector index on the Embedding* (vector (1536)) column
- C. a computed column that manually compares vector values
- D. a standard nonclustered index on the Fmbeddings (vector (1536)) column
Answer: B
Explanation:
The correct answer is D because the requirement is to enable similarity search over embedding vectors and to minimize latency . Microsoft documents that CREATE VECTOR INDEX is specifically used to create an index on vector data for approximate nearest neighbor (ANN) search , which is designed to accelerate vector similarity queries compared to exact k-nearest-neighbor scans.
This matches the scenario exactly. The VehicleHealthSummary table already includes an Embeddings (vector (1536)) column. In Microsoft SQL platforms, embeddings are stored in vector columns and queried for semantic similarity. To improve performance and reduce response time, Microsoft recommends a vector index , not a regular B-tree nonclustered index and not a full-text index. A vector index is purpose-built for finding the most similar vectors efficiently.
The other options are not appropriate:
* A would require manual comparison logic and would increase latency rather than minimize it.
* B is incorrect because a standard nonclustered index is not the index type used for vector similarity operations.
* C is incorrect because full-text indexes are for textual token-based search, not numeric vector embeddings.
Microsoft's current documentation is explicit that vector indexes support approximate nearest neighbor search , and that the optimizer can use the ANN index automatically for vector queries. That is the exam- aligned design choice when the goal is fast retrieval of the most relevant health summary reports from an embeddings column.
NEW QUESTION # 34
Hotspot Question
You have an Azure SQL database that contains a table named dbo.SupportTickets.
dbo.SupportTickets contains a JSON column named Payload and a datetime column CreatedAt.
You need to generate a report for the last seven days that meets the following requirements:
- Returns exactly one row per customer per day
- For each customer and day, returns the earliest ticket
- Includes the customer ID stored in Payload
How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 35
Case Study 2 - Fabrikam
Existing Environment
Azure Environment
Fabrikam has a single Azure subscription in the East US 2 Azure region. The subscription contains an Azure SQL database named DB1. DB1 contains the following tables:
* Patients
* Employees
* Procedures
* Transactions
* UsefulPrompts
* ProcedureDocuments
You store a column master key as a secret in Azure Key Vault.
You have an on-premises application named TransactionProcessing that uses a hard-coded username and password in a connection string to access DB1.
Problem Statements
Users report that after executing a long-running stored procedure named sp_UpdateProcedureForPatient, updates to the underlying data are sometimes inconsistent.
Requirements
Planned Changes
Fabrikam plans to manage all changes to Azure SQL Database objects by using source control in GitHub. Every pull request submitted to production will be validated before it can be merged.
Deployments must use the Release configuration.
Security Requirements
Fabrikam identifies the following security requirements:
* The TransactionProcessing application must use a passwordless connection to DB1.
* The Employees table contains two columns named TaxID and Salary that must be encrypted at rest.
* Auditors must have a tamper-evident history of transactions with cryptographic proof of changes to the employee data.
Database Performance Requirements
Records accessed by using sp_UpdateProcedureForPatient must NOT be changed by other transactions while the stored procedure runs.
AI Search, Embeddings, and Vector Indexing
Fabrikam identifies the following AI-related requirements:
* Queries to the ProcedureDocuments table must use Reciprocal Rank Fusion (RRF).
* Users must be able to query the data in DB1 by using prompts in Copilot in Microsoft Fabric.
* The UsefulPrompts table will store prompts that doctors can use to help diagnose patient illness by connecting to an Azure OpenAI endpoint.
Development Requirements
Fabrikam identifies the following development requirements:
* Provide the functionality to retrieve all the transactions of a given patient between two dates, showing a running total.
* Expose a Data API builder (DAB) configuration file to enable Azure services to perform the following operations over a REST API:
- Read data from the procedures table without authentication.
- Read and insert data into the Transactions table once authenticated.
- Execute the sp_UpdateProcedurePatient stored procedure.
* Provide the functionality to retrieve a list of the names of patients who underwent medical procedures during the last 30 days.
* Information for each medical procedure will be stored in a table. The table will be used with a large language model (LLM) for user querying and will have the following structure.
DAB
You create a DAB configuration file that meets the development requirements for DB1 and includes the following entities.
Hotspot Question
You are evaluating the DAB configuration.
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 # 36
What is a common pattern for integrating LLMs with SQL?
- A. Direct SQL execution inside LLM
- B. RAG pipeline
- C. Backup restore
- D. Manual CSV export
Answer: B
Explanation:
RAG ensures accurate, up-to-date responses using SQL data.
NEW QUESTION # 37
You have an Azure SQL table that contains the following data.
You need to retrieve data to be used as context for a large language model (LLM). The solution must minimize token usage.
Which formal should you use to send the data to the LLM?
- A.
- B.
- C.
- D.
Answer: B
Explanation:
The correct choice is Option A because it provides the relevant semantic context the LLM needs while avoiding an unnecessary field that would add tokens without improving answer quality.
For LLM grounding and RAG-style context, Microsoft guidance emphasizes mapping and sending the fields that contain text pertinent to the use case . In this FAQ scenario, the useful context is the ProductName , the Question , and the Answer . Those three fields help the model understand both the subject domain and the actual Q & A pair. By contrast, FaqId is just a technical identifier and generally adds no semantic value for response generation, so including it wastes tokens.
That is why Option A is better than the others:
* Option A keeps the meaningful text fields and removes the low-value identifier.
* Option B is too minimal because it includes only the answer text as Prompt, which strips away the product and question context the LLM may need for accurate grounding.
* Option C keeps FaqId but omits ProductName, which can be important disambiguating context.
* Option D includes everything, but that does not minimize token usage because it keeps the unnecessary FaqId.
NEW QUESTION # 38
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 leferences 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 an artifact reference to the Azure SQL Database master.dacpac file.
Does this meet the goal?
- A. Yes
- B. No
Answer: B
Explanation:
For an SDK-style SQL database project targeting Azure SQL Database , Microsoft recommends using the Azure SQL system DACPAC as a NuGet package reference rather than adding a direct artifact reference to master.dacpac for new SDK-style development. Microsoft's SQL Database Projects documentation says direct .dacpac artifact references are not recommended for new development in SDK-style projects ; instead, use NuGet package references .
Because the goal is specifically to make dotnet build validate successfully with the correct Azure SQL system objects , adding an artifact reference to master.dacpac is not the recommended SDK-style solution. It can work in some project styles, but it does not meet the stated goal as the proper approach for SDK-style Azure SQL projects.
NEW QUESTION # 39
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.
Answer:
Explanation:
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)
NEW QUESTION # 40
You have an Azure SQL database named ToDo that contains a table named dbo.ToDo.
Your company plans to develop an Azure Functions app to run whenever the rows in dbo.ToDo change. The app will process INSERT, UPDATE, and DELETE events by using the Azure SQL trigger binding.
You need to configure ToDo to support the planned app.
What should you do?
- A. Enable change tracking on ToDo and dbo.ToDo.
- B. On dbo.ToDo, create a Data Definition Language (DDL) trigger that calls the Azure Functions HTTP endpoint.
- C. On dbo.ToDo, create a Data Manipulation Language (DML) trigger that calls the Azure Functions HTTP endpoint.
- D. Enable change data capture (CDC) on ToDo and dbo.ToDo.
Answer: A
Explanation:
To use the Azure SQL trigger for Azure Functions, your database table must have Change Tracking enabled. This is the underlying mechanism the trigger uses to monitor row-level modifications.
Required Table Configuration
To facilitate the app, you must follow these three steps:
1. Enable Change Tracking on the Database
Before the table can be tracked, the feature must be turned on at the database level.
2. Enable Change Tracking on the Table
The specific table the Function monitors must have tracking enabled.
3. Define a Primary Key
The Azure SQL trigger requires the table to have a Primary Key.
The trigger uses the Primary Key to identify which specific rows were changed.
If your table lacks a Primary Key, the Function will fail to start.
Reference:
https://demiliani.com/2025/05/08/azure-function-sql-trigger-how-to-use-it-and-why-it-can-be- useful-in-your-business-central-projects/
NEW QUESTION # 41
Hotspot Question
You have an Azure AI Search service and an index named hotels that includes a vector field 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.
How should you complete the REST request body? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 42
Hotspot Question
You have an Azure SQL database that contains a table named Table1. Table1 contains
25,000,000 rows of data and a datetime2 column named DateKey. The data in Table1 spans the years 2020 through 2021.
You need to partition the data in Table1 by year. The solution must minimize how long it takes to rebuild or reindex the table.
How should you complete the Transact-SQL code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 43
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
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(month, -36, SYSUTCDATETIME());
Does this meet the goal?
- A. Yes
- B. No
Answer: B
Explanation:
Correct:
* Identify the partition number for the oldest month, and then run the following Transact-SQL statement.
TRUNCATE TABLE dbo.Orders
WITH (PARTITIONS (partition number));
The best Transact-SQL statement to remove all rows for the oldest month while minimizing the impact on other queries is TRUNCATE TABLE with a WITH (PARTITIONS (...)) clause.
Why TRUNCATE TABLE ... WITH (PARTITIONS (...)) is Best
Efficiency: TRUNCATE TABLE is a Data Definition Language (DDL) operation that removes data by deallocating the data pages, which is a metadata operation and is very fast, regardless of the amount of data in the partition.
Minimal Logging: It uses less transaction log space compared to a DELETE statement, which logs each row deletion individually.
Low Impact on Concurrency: It performs a quick, partition-specific operation. A row-by-row DELETE would be a long-running transaction and could cause locking and blocking issues for other queries accessing the table.
Data Integrity: Because the table has a clustered index and is partitioned by the same column (aligned indexes), the TRUNCATE PARTITION operation is a fast, partition-level maintenance operation that targets only that specific data subset.
Incorrect:
* : Identify the partition scheme for the oldest month, and then run the following Transact-SQL statement.
ALTER TABLE dbo.Orders
DROP PARTITION SCHEME (partition_scheme_name);
The DROP PARTITION SCHEME statement removes the partition scheme object from the database but does not remove the data itself or free up the space, and it requires all tables to be moved off the scheme first, which is a complex operation. This does not meet the goal of removing the data efficiently.
* Run the following Transact-SQL statement.
DELETE FROM dbo.Orders
WHERE OrderDate < DATEADD(month, -36, SYSUTCDATETIME());
A standard DELETE statement, even with a WHERE clause that uses the partition column, can be a time-consuming, logged operation that causes locking and blocking on the main table, negatively impacting performance.
Reference:
https://stackoverflow.com/questions/63632963/truncate-partition-vs-drop-partition-performace- wise-which-one-is-efficient-an
NEW QUESTION # 44
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. Delete .github/copilot-instructions.md.
- B. Add a --debug flag when you start the GitHub Copilot Chat extension.
- C. From Visual Studio Code, modify your GitHub Copilot Chat user settings.
Answer: C
Explanation:
To disable GitHub Copilot repository-level instructions for yourself without affecting others, you can modify your User Settings in Visual Studio Code. This allows you to override or ignore specific repository-wide configurations like copilot-instructions.md at a personal level.
How to Disable Repository-Level Instructions
1. Open User Settings: Press Ctrl+, (Windows/Linux) or Cmd+, (macOS) to open the VS Code Settings editor.
2. Search for Copilot Chat: In the search bar, type github.copilot.chat.customInstructions.
3. Configure Custom Instructions:
Find the setting for Github > Copilot > Chat: Custom Instructions.
Ensure the User tab is selected at the top to apply changes only to your account and not the shared workspace.
4. Toggle via Chat UI:
Open the Chat view (Ctrl+Alt+I or Cmd+Shift+L).
Click the Configure Chat (gear icon) at the bottom of the chat panel.
Select the Instructions tab and uncheck or remove any active repository-level files to disable their influence on your session.
Reference:
https://code.visualstudio.com/docs/copilot/customization/custom-instructions
NEW QUESTION # 45
You have an Azure SQL database that contains a column named Notes.
A security review discovers that Notes contains sensitive data.
You need to ensure that the data is protected so that neither the stored values nor the query inputs reveal information about the actual data. The solution must prevent a user from inferring relationships or repetitions in the data based on the encrypted output.
Which should you use?
- A. Always Encrypted with deterministic encryption
- B. Always Encrypted with secure enclaves
- C. Always Encrypted with randomized encryption
- D. row-level security (RLS)
Answer: C
Explanation:
To protect sensitive Azure SQL database columns so that neither stored values nor query inputs reveal actual data--and to prevent inference of relationships/repetitions--Always Encrypted with Randomized Encryption is recommended. This approach ensures that data is encrypted on the client side, and the database engine never handles plaintext data or keys.
Key Recommendations:
Always Encrypted (Randomized): Use randomized encryption, which produces different encrypted values (ciphertext) for the same plaintext, preventing pattern analysis.
Azure Key Vault: Store column encryption keys in Azure Key Vault for secure, manageable key storage.
Client-side Encryption: Data is encrypted/decrypted within the application's memory using the SQL client driver, ensuring the database engine or high-privileged users cannot access the data.
Incorrect:
[Not D]
Deterministic Encryption: Allows matching queries (e.g., WHERE Column = 'Value'), but allows attackers to infer patterns and repetitions in data.
Transparent Data Encryption (TDE): Protects data at rest, but SQL users can still see plaintext data, violating the requirement.
Reference:
https://oneuptime.com/blog/post/2026-02-16-how-to-configure-always-encrypted-in-azure-sql- database/view
NEW QUESTION # 46
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.
Answer:
Explanation:
Explanation:
The correct SARGable pattern for filtering a single calendar day is to use a half-open date range :
o.CreateDate > = @StartDate
AND o.CreateDate < @EndDate
with:
SET @EndDate = DATEADD(day, 1, @StartDate)
This is the correct design because it keeps the function off the column and applies it only to the parameter.
That allows SQL Server and Azure SQL to use an index on CreateDate efficiently, which is the key requirement for a SARGable predicate. Microsoft documents DATEADD as the standard function for adding one day to a date value, which makes it the right way to derive the exclusive upper boundary for the next day.
The incorrect choices are the ones that wrap CreateDate in CONVERT(...), because expressions like:
CONVERT(char(10), CreateDate, 121) = ...
make the predicate non-SARGable and typically prevent efficient seeks on an index over CreateDate.
So the completed procedure is:
CREATE PROCEDURE dbo.usp_SearchOrders
@StartDate date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndDate date;
SET @EndDate = DATEADD(day, 1, @StartDate);
SELECT o.CreateDate,
o.OrderId,
o.ShipDate
FROM dbo.Orders AS o
WHERE o.CreateDate > = @StartDate
AND o.CreateDate < @EndDate;
END;
NEW QUESTION # 47
Drag and Drop Question
You have a Microsoft SQL Server 2025 database that contains a table named dbo.CustomerMessages. dbo.CustomerMessages contains two columns named MessageID (int) and MessageRaw (nvarchar(max)).
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 MessageID, 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.
Answer:
Explanation:
NEW QUESTION # 48
You have an Azure SQL database that contains tables named dbo.ProductDocs and dbo.ProductDocsEmbeddings. dbo.ProductDocs contains product documentation and the following columns:
- DocID (int)
- Title (nvarchar(200))
- Body (nvarchar(max))
- LastModified (datetime2)
The documentation is edited throughout the day.
dbo.ProductDocsEmbeddings contains the following columns:
- DocID (int)
- ChunkOrder (int)
- ChunkText (nvarchar(max))
- Embedding (vector(1536))
The current embedding pipeline runs once per night.
You need to ensure that embeddings are updated every time the underlying documentation content changes. The solution must NOT require a nightly batch process.
What should you include in the solution?
- A. fixed-size chunking
- B. a smaller embedding model
- C. change tracking on dbo.ProductDocs
- D. table triggers
Answer: C
Explanation:
To ensure embeddings are updated immediately when the product information table changes, you should implement Azure SQL Database Change Tracking combined with Azure Functions triggered via Event Grid. This approach eliminates daily batch processes by enabling an event- driven architecture that triggers embedding regeneration only for modified rows.
Reference:
https://medium.com/@granthgharewal/why-change-tracking-needs-to-be-enabled-for-sql-trigger- azure-functions-7d230a669fb8
NEW QUESTION # 49
You need to create a table in the database to store the telemetry data. You have the following Transact-SQL code.
Answer:
Explanation:
Explanation:
The first statement is No . The requirement says telemetry data must be stored in a partitioned table to provide predictable performance for ingestion and retention operations. However, the shown CREATE TABLE statement does not define a partition function or partition scheme, and the table is created with a regular clustered primary key on TelemetryId. Microsoft's partitioning guidance states that creating a partitioned table requires a partition function , a partition scheme , and creating the table or index on that partition scheme using a partitioning column. None of that appears in the code, so the table is not partitioned.
The second statement is Yes . The code creates a JSON index named JI_VehicleTelemetry_Location on LocationJson for these specific JSON paths: $.location.latitude, $.location.longitude, and $.location.accuracy.
That matches the requirement that those JSON properties must be filterable by using an index seek .
Microsoft documents that JSON indexing is used to optimize filtering and sorting on JSON properties, and the index only helps for the properties included in the index definition.
The third statement is No . The JSON index is defined only for latitude, longitude, and accuracy. A query filtering on $.location.heading references a different path that is not included in the index definition, so that query would not use JI_VehicleTelemetry_Location for that predicate. JSON indexes are path-specific; they do not automatically cover unrelated properties in the same JSON document.
NEW QUESTION # 50
Drag and Drop Question
You have an Azure SQL database that stores sales data and contains tables named Sales and Products. Sales contains three columns named SalesDate, ProductKey, and TotalSale, Sales is 10 TB and is loaded nightly by using a batch process. Most reporting queries scan large portions of Sales, filter on SalesDate or ProductKey, and use SUM() to aggregate TotalSale.
Products is relatively small and is used primarily for point lookups and joins to Sales.
You need to recommend which indexes to create to optimize the reporting queries. The solution must minimize storage requirements.
Which type of index should you recommend for each table? To answer, drag the appropriate index types to the correct tables. Each index type 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 # 51
......
DP-800 Exam Brain Dumps - Study Notes and Theory: https://actualtests.realvalidexam.com/DP-800-real-exam-dumps.html
