Free DP-800 Practice Questions & Study Guide: Developing AI-Enabled Database Solutions

By /

Official Exam Details: (DP-800)

Exam Code:
DP-800
Passing Score:
70%
Total Question Bank:
104 Verified Questions
Exam Duration:
120 Minutes

Exam Format & Question Types

This certification exam features multiple-choice questions (single and multi-select), interactive Hotspots, Hot Areas, and Drag-and-Drop scenario configurations designed to test real-world technical competency.

DP-800: Developing AI Solutions on Azure – Study Guide & Architectural Master Blueprint

Welcome, aspiring AI Architects and Developers! This study guide and architectural blueprint is meticulously crafted to prepare you for the DP-800 certification exam, “Developing AI Solutions on Azure.” As an expert cloud architect and exam author, I’ve distilled the core knowledge, architectural patterns, and practical insights you’ll need to master this challenging certification. Your success hinges on understanding not just the services, but how to effectively integrate and deploy them into robust, intelligent solutions.

Exam Overview & Profile

  • Target Audience: This exam is designed for AI Developers, Data Scientists, and Solution Architects who possess a foundational understanding of Azure and strong programming skills (preferably Python or C#). Candidates should be proficient in leveraging Azure Cognitive Services, Bot Framework, and Azure AI Search to build intelligent applications.
  • Question Count: Typically, candidates can expect between 40-60 questions, comprising multiple-choice, drag-and-drop, case studies, and hands-on lab simulations.
  • Duration: The exam duration is generally 150 minutes, including time for reviewing instructions and questions.
  • Passing Score Context: A scaled score of 700 or higher is required to pass. This score reflects a comprehensive understanding of the exam objectives across all domains.

Deep-Dive Domain Breakdown

The DP-800 exam evaluates your ability to design, build, and deploy AI solutions on Azure. Here’s a detailed architectural breakdown of the core concepts:

1. Analyzing and Interpreting Vision Solutions (25-30%)

  • Azure Computer Vision: Understand its capabilities for image analysis (object detection, scene description, OCR, spatial analysis), generating thumbnails, and content moderation. Architect solutions for large-scale image processing pipelines.
  • Custom Vision: Design and implement custom image classification and object detection models. Master data labeling, model training, evaluation, and deployment (cloud or edge).
  • Azure Face API: Leverage for face detection, verification, identification, and emotion recognition. Understand person group management and responsible AI considerations for facial recognition.
  • Architectural Patterns: Integrating Vision services with Azure Storage for data input, Azure Functions/Logic Apps for event-driven processing, and Azure Cosmos DB for metadata storage.

2. Analyzing and Interpreting Language Solutions (25-30%)

  • Language Understanding (LUIS): Develop robust conversational AI by designing intents, entities, and utterances. Understand pattern matching, prebuilt domains, and active learning for continuous improvement.
  • Text Analytics: Implement solutions for sentiment analysis, key phrase extraction, named entity recognition (NER), and language detection. Understand the differences and use cases for each.
  • QnA Maker: Create and manage knowledge bases from various sources (FAQs, manuals, websites). Master active learning, chit-chat, and multi-turn conversations for interactive experiences.
  • Azure Translator: Integrate real-time text and speech translation into applications, understanding supported languages and customization options.
  • Architectural Patterns: Combining LUIS for intent resolution with QnA Maker for factual answers, orchestrating with Bot Framework, and scaling with Cognitive Services containers.

3. Building and Integrating AI Bots (20-25%)

  • Azure Bot Service: Design and provision bots, selecting appropriate templates (e.g., Echo Bot, Basic Bot). Understand bot registration, configuration, and security best practices.
  • Bot Framework SDK: Develop conversational flows using dialogs, prompt types, and state management (user, conversation, private conversation state). Integrate LUIS and QnA Maker for intelligence.
  • Channel Integration: Configure bots for various channels (Web Chat, Microsoft Teams, Slack, Facebook Messenger). Understand channel-specific requirements and message formats.
  • Architectural Patterns: Deploying bots as Azure Web Apps, integrating with Azure AD for authentication, and monitoring bot performance with Application Insights.

4. Implementing Knowledge Mining Solutions (10-15%)

  • Azure AI Search: Design and implement knowledge mining solutions by creating search indexes, indexers, and skillsets. Understand data sources, cognitive skills (e.g., OCR, entity extraction, key phrase extraction), and custom skills.
  • Enrichment Pipelines: Architect data enrichment workflows to extract insights from unstructured data (e.g., PDFs, images, text documents).
  • Architectural Patterns: Connecting Azure AI Search to various data sources (Azure Blob Storage, Azure SQL Database), orchestrating index refreshes, and integrating with front-end search experiences.

5. Applying Responsible AI Principles (5-10%)

  • Understand the six principles of Responsible AI: fairness, reliability and safety, privacy and security, inclusiveness, transparency, and accountability.
  • Apply these principles when designing and deploying AI solutions, considering potential biases, data privacy, and model interpretability.
  • Architectural Considerations: Implementing data governance, secure data handling, and logging for auditing and accountability.

High-Yield Exam Tips & Scenario Traps

  • Cognitive Services Choice: Be able to articulate when to use a pre-trained Cognitive Service versus developing a custom model. Pre-trained services offer speed and ease for common tasks; custom models are for unique, domain-specific requirements.
  • Security is Paramount: Understand how to secure Azure AI services. Key concepts include Managed Identities for authentication, Azure Private Endpoints for network isolation, and Access Keys/Resource-based authorization.
  • Scalability & Cost: Differentiate between pricing tiers for various services. Consider batch vs. real-time processing and strategies for cost optimization (e.g., using containers for specific workloads).
  • LUIS vs. QnA Maker: A common trap. LUIS is for understanding user *intent* and extracting *entities*. QnA Maker is for answering *specific questions* from a curated knowledge base. They often work together in sophisticated bots.
  • Error Handling & Monitoring: Implement robust error handling (e.g., retry logic for API calls) and monitor AI solutions using Azure Monitor and Application Insights.
  • Responsible AI in Scenarios: Expect questions that challenge your ability to identify and mitigate biases, ensure data privacy, and provide transparency in AI solutions.

Frequently Asked Questions (FAQ)

Q1: How should I structure my study time effectively for the DP-800 exam?

A: A balanced approach is crucial. Dedicate significant time to hands-on labs for each service mentioned in the domains. Actively build small projects using Azure Cognitive Services, Bot Framework, and Azure AI Search. Complement this with official Microsoft Learn modules and documentation to grasp theoretical concepts and best practices. Finally, incorporate practice tests to identify knowledge gaps and familiarize yourself with the exam format. Don’t underestimate the value of practical coding experience in Python or C# for implementing AI solutions.

Q2: What development tools and environments are essential for DP-800 preparation?

A: You should be comfortable with Visual Studio Code as your primary IDE. Key tools include the Bot Framework Emulator for testing bots, Azure CLI or Azure PowerShell for resource management, and Postman or similar tools for testing Cognitive Services REST APIs. For language-specific development, ensure your environment is set up for Python (with relevant SDKs like azure-ai-textanalytics, azure-cognitiveservices-vision-computervision, etc.) or C# (.NET SDKs). Familiarity with the Azure portal for configuring and monitoring services is also critical.

Q3: How important is practical, hands-on experience compared to theoretical knowledge for passing this exam?

A: Practical, hands-on experience is paramount for DP-800. The exam often presents scenario-based questions where you’ll need to make architectural decisions, troubleshoot issues, or select the correct service/configuration. Without direct experience deploying and interacting with these services, it’s challenging to confidently answer such questions. Merely memorizing facts about services is insufficient; you must understand their practical application, limitations, and how they integrate within a broader solution.

Q4: What’s the best strategy for approaching the case study questions in the exam?

A: For case studies, first, thoroughly read the entire scenario to understand the business requirements, existing infrastructure, and constraints (e.g., budget, performance, security). Identify keywords and key problems to solve. Then, break down the problem into smaller, manageable components. For each component, evaluate which Azure AI service or combination of services best fits the technical and business needs, considering trade-offs like cost, accuracy, and complexity. Always justify your architectural choices based on the information provided in the case study.

Free DP-800 Sample Practice Questions

Test your knowledge with these sample scenario questions complete with verified explanations:

Case Study: Contoso Ltd.

Instructions
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

To start the case study –
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

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 FeedbackJson column has a full-text index and stores JSON documents in the following format.


The support staff at Contoso never has the UNMASK permission.

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.

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.

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 accuracy JSON 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 MaintenanceEvents table updates the corresponding value in the LastModifiedUtc column 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 FeedbackJson column of the CustomerFeedback table:

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.

QUESTION 1

Scenario: Contoso Ltd.

You need to generate embeddings to resolve the issues identified by the analysts. Which column

should you use?

  • A. vehicleLocation
  • B. incidentDescrlption
  • C. incidentType
  • D. SeverityScore
Click to View Verified Solution & Explanation

Correct Option: B

Explanation:

✅ **Reasoning: Embeddings capture semantic meaning from text. incident** Description: provides free-form text detailing the nature and specifics of incidents. This descriptive content is essential for AI models to understand, categorize, and resolve diverse issues identified by analysts through semantic analysis and similarity search. ❌ Why the other choices are incorrect:

 

  • Option A is incorrect: vehicleLocation indicates geographical position, not the semantic details of an incident. Embeddings of locations are possible but not primary for issue resolution from a content perspective.
  • Option C is incorrect: incidentType is a categorical label. While it can be encoded, it lacks the detailed textual information necessary for deep semantic understanding of unique issues.
  • Option D is incorrect: SeverityScore is a numerical value. Embeddings are designed for rich, unstructured data like text, not direct numerical scores. Numeric values are used differently in models.

 

General Practice Questions

QUESTION 2 [HOT_AREA]

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.

 

✔ Correct Answer:

Hotspot Canvas

Click to View Verified Solution & Explanation
Explanation: ✅ **Reasoning: JSON_VALUE(f.FeedbackJson, '$.text') AS FeedbackText correctly extracts a scalar value from a JSON column using a specified path. This provides the actual feedback text as a distinct column in the query results, crucial for analysis.

✅

Reasoning: JSON_VALUE(f.FeedbackJson, '$.text'), @KnownIssue** Description: ) AS SimilarityScore conceptually calculates a similarity score. Though syntactically appearing incomplete as a standalone expression, it represents the arguments and alias for an implied similarity function (e.g., semantic similarity) in DP-800 context, comparing feedback text with a known issue.

Case Study: Contoso Ltd.

Instructions
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

To start the case study –
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

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 FeedbackJson column has a full-text index and stores JSON documents in the following format.


The support staff at Contoso never has the UNMASK permission.

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.

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.

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 accuracy JSON 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 MaintenanceEvents table updates the corresponding value in the LastModifiedUtc column 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 FeedbackJson column of the CustomerFeedback table:

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.

QUESTION 3 [DRAG_DROP]

Scenario: Contoso Ltd.

You need to meet the database performance requirements for maintenance data

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.

 

Question Interface:

Question Interface
✔ Correct Answer / Solution View:

Solution Canvas

Click to View Verified Solution & Explanation
Explanation:

✅

 

Reasoning: For an AFTER UPDATE trigger, the INNER JOIN inserted i clause connects the base table (m) with the inserted pseudo-table (i), which holds the new data. The primary key (MaintenanceId) is used to uniquely identify and match the updated rows in both tables.
 

✅

 

Reasoning: This condition (m.LastModifiedUtc <> i.LastModifiedUtc) ensures the LastModifiedUtc column is updated by the trigger only if its value changed as part of the initial UPDATE statement. This meets performance requirements by preventing unnecessary writes and associated index updates if the user’s statement did not modify this specific column.

QUESTION 4 [HOT_AREA]

Scenario: Contoso Ltd.

You need to meet the development requirements for the FeedbackJson column.
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.
 

✔ Correct Answer:

Hotspot Canvas

Click to View Verified Solution & Explanation
Explanation:

The user needs to construct a T-SQL query that performs a full-text search, extracts a value from a JSON column, calculates a similarity score, and orders the results. The solution correctly identifies the appropriate T-SQL functions for each step based on the case study requirements.

  1. SELECT Clause (First Box): The requirement is to ‘Extract the customer feedback text from the JSON document’. The JSON_VALUE(f.FeedbackJson, '$.text') AS FeedbackText expression is the correct choice. The JSON_VALUE function is specifically designed to extract a scalar value (the feedback text) from a JSON string using a specified path ($.text). This makes the text available as a standard column in the result set.

  2. WHERE Clause (Second Box): The requirement is to ‘Filter rows where the JSON text contains a keyword’. The case study mentions that the FeedbackJson column has a full-text index. Therefore, using the CONTAINS(FeedbackJson, @Keyword) predicate is the correct and most performant method. It leverages the full-text index to efficiently find rows matching the keyword, which is superior to alternatives like LIKE that would cause a table scan.

  3. ORDER BY Clause (Third Box): The requirement is to ‘Order the results by similarity score, with the highest score first’. The query already calculates this score in the SELECT list and assigns it the alias SimilarityScore. The correct way to sort the results is to reference this alias in the ORDER BY clause. Since DESC is already provided, selecting SimilarityScore completes the logic.

General Practice Questions

QUESTION 5 [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_CreateOrder 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. 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.

Question Interface:

Question Interface
✔ Correct Answer / Solution View:

Solution Canvas

Click to View Verified Solution & Explanation
Explanation:

Audit of the Correct Answer

The provided solution correctly implements standard T-SQL transactional error handling using a TRY/CATCH block.

✅ Step 1: SET @OrderId = SCOPE_IDENTITY() matches with -> First Blank (inside TRY block)
Reasoning: This statement must execute after a successful INSERT to capture the auto-generated identity value for the new order. The SCOPE_IDENTITY() function returns the last identity value created in the current scope, which is then assigned to the @OrderId output parameter. This occurs before the transaction is committed.

✅ Step 2: IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION matches with -> Second Blank (inside CATCH block)
Reasoning: If an error occurs within the TRY block, control transfers to the CATCH block. This line checks if a transaction is currently active (@@TRANCOUNT > 0) and, if so, rolls it back. This prevents partial data modifications and ensures atomicity. The subsequent THROW statement then re-raises the original error to the caller.

QUESTION 6

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-instructions.md.
Click to View Verified Solution & Explanation

Correct Option: A

Explanation:

The core requirement is to disable GitHub Copilot’s repository-level instructions for yourself without affecting other users. This implies a user-specific configuration change.

User settings in Visual Studio Code are personal to an individual user and apply globally across all their VS Code instances unless overridden by workspace settings. Modifying your GitHub Copilot Chat user settings allows you to customize the extension’s behavior for your personal use without impacting the shared repository or other team members’ configurations.

Why other options are incorrect:

  • Option B is incorrect: Adding a --debug flag when starting the extension is used for troubleshooting and obtaining verbose logs. It does not disable or modify the functional behavior of repository-level instructions.
  • Option C is incorrect: Deleting .github/copilot-instructions.md would remove the repository-level instructions for all users of the repository (once committed and pushed). This directly contradicts the requirement to disable them only for yourself without affecting others.

QUESTION 7

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 MATCH 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. options/DP800_2a33e995b0.webp
  • B. options/DP800_27ad21291a.webp
  • C. options/DP800_276e287e7b.webp
  • D. options/DP800_7ecb9af861.webp
Click to View Verified Solution & Explanation

Correct Option: D

Explanation:

MATCH operator defines the traversal pattern in a SQL Graph query.

The pattern (p1-(k1)->p2-(k2)->p3) means:

Start from p1 (the person identified by @StartPersonId),

Follow the first directed edge k1 to p2,

Then follow the second directed edge k2 to p3.

The query returns the PersonId and DisplayName of people reachable in exactly two hops.

QUESTION 8

You have a SQL database in Microsoft Fabric that contains a column named Payload. Payload 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, user1+promo@contoso.com.

You need to return a normalized email value that removes the subaddressing, for example, user1 +promo@contoso.com must be normalized to user1@contoso.com.

Which Transact-SQL expression should you use?

  • A. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*$’, ‘’)
  • B. REGEXP_SUBSTR(JSON_VALUE(Payload, ‘$.customer_email’), ‘^[^+]+@.*$=’)
  • C. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*@’, ‘@’)
  • D. REGEXP_REPLACE(JSON_VALUE(Payload, ‘$.customer_email’), ‘\+.*’, ‘’)
Click to View Verified Solution & Explanation

Correct Option: C

Explanation:

The problem requires normalizing email addresses by removing subaddressing (e.g., ‘+promo’) while retaining the user part and the domain. Given an email like user1+promo@contoso.com, the goal is user1@contoso.com.

Option C uses REGEXP_REPLACE(JSON_VALUE(Payload, '$.customer_email'), '\+.*@', '@'). This expression first extracts the email using JSON_VALUE. Then, REGEXP_REPLACE applies the pattern \+.*@. The \+ matches the literal plus sign, .* matches any characters zero or more times, and @ matches the at sign. This pattern effectively captures everything from the ‘+’ up to and including the first ‘@’ sign of the domain. By replacing this entire matched segment with just '@', the subaddressing part is removed, achieving the desired normalization.

QUESTION 9 [DRAG_DROP]

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.

Question Interface:

Question Interface
✔ Correct Answer / Solution View:

Solution Canvas

Click to View Verified Solution & Explanation
Explanation:

The provided solution correctly implements a SARGable query to filter records for a single calendar day. It avoids applying functions to the indexed column (CreateDate) in the WHERE clause, which allows the query optimizer to use an index seek for better performance.

Explanation of Choices

✅ Step 1: DATEADD(day, 1, @StartDate) matches with -> SET @EndDate =

Reasoning: To filter for a single day, we need a date range. This code sets the @EndDate variable to be the day after @StartDate. This creates the exclusive upper boundary for our search, ensuring we capture all time values within the target day up to midnight.

✅ Step 2: @StartDate matches with -> WHERE o.CreateDate >=

Reasoning: This sets the inclusive lower boundary of the date range. Comparing the CreateDate column directly with the @StartDate parameter is SARGable, as no function is applied to the column itself, allowing for efficient index usage.

✅ Step 3: @EndDate matches with -> AND o.CreateDate <

Reasoning: This sets the exclusive upper boundary using the previously calculated @EndDate. The condition CreateDate < @EndDate correctly includes all moments in time on @StartDate up to, but not including, the start of the next day. This completes the SARGable predicate.

QUESTION 10

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 @OrderDate 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());
Click to View Verified Solution & Explanation

Correct Option: D

Explanation: The question asks for a scalar user-defined function (UDF) that returns the number of whole years between an input parameter @OrderDate and the current date/time (GETDATE()) as a single positive integer. The output must be inserted at line 05, which is inside the BEGIN...END block of the UDF, thus requiring a RETURN statement.

  • Option D: RETURN DATEDIFF(year, @OrderDate, GETDATE()); correctly uses the DATEDIFF function. DATEDIFF(year, startdate, enddate) calculates the number of year boundaries crossed between startdate and enddate. By placing @OrderDate as the startdate and GETDATE() as the enddate, the function will return a positive integer representing the number of years if @OrderDate is earlier than GETDATE(), satisfying the requirement for a positive integer. It also includes the necessary RETURN keyword for a scalar UDF.

Why the other choices are incorrect:

  • Option A: RETURN DATEDIFF(year, GETDATE(), @OrderDate); is incorrect because it reverses the startdate and enddate parameters. If @OrderDate is in the past (e.g., ‘2023-01-01’) and GETDATE() is ‘2024-01-01’, this would return a negative number (-1), violating the requirement for a “single positive integer.”
  • Option B: DATEDIFF(month, @orderdate, GETDATE()) / 12 is a more accurate way to calculate complete elapsed years (like age) by dividing the total months by 12 using integer division. However, it is missing the crucial RETURN statement required for a scalar UDF at line 05.
  • Option C: DATEPART(year, GETDATE()) - DATEPART(year, @orderdate) is incorrect. This approach only subtracts the year components, which does not accurately represent “whole years” elapsed. For example, if @OrderDate is ‘2023-12-31’ and GETDATE() is ‘2024-01-01’, this would return 1, even though only one day has passed, not a whole year.

QUESTION 11

You have an Azure SQL database.

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

You have the following Container Apps secrets:

MSSQL_CONNECTION_STRING that maps to the SQL connection string

DAB_CONFIG_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
Click to View Verified Solution & Explanation

Correct Option: B

Explanation:

✅

Option B (Correct)

Reasoning: Data API builder (DAB) allows referencing environment variables for sensitive information like connection strings directly within its configuration. The syntax for this is @env('ENVIRONMENT_VARIABLE_NAME'). In this scenario, the Azure Container Apps secret for the SQL connection string is named MSSQL_CONNECTION_STRING. Therefore, the command needs to instruct DAB to read this environment variable. The command dab init --database-type mssql --connection-string “@env(‘MSSQL_CONNECTION_STRING’)” --host-mode Production --config dab-config.json correctly specifies the database type as MSSQL, points the connection string to the MSSQL_CONNECTION_STRING environment variable using the proper @env() syntax, sets the host mode to Production, and specifies the output configuration file.

❌ Why the other choices are incorrect:

  • Option A is incorrect: The secretref: syntax is used within the Data API builder configuration file itself to reference secrets managed by the DAB runtime, not directly in the dab init command for an environment variable. Additionally, DAB_CONFIG_BASE64 is described as mapping to the DAB configuration, not the SQL connection string.
  • Option C is incorrect: Similar to option A, secretref: is not the correct syntax for referencing environment variables during the dab init command. Even if it were, mssql-connection-string does not match the provided secret name MSSQL_CONNECTION_STRING.
  • Option D is incorrect: While @env() is the correct syntax for referencing environment variables, DAB_CONFIG_BASE64 is stated to map to the DAB configuration, not the SQL connection string. Using this would attempt to use the base64 encoded configuration as the database connection string, which is incorrect.

QUESTION 12

You have a SQL database in Microsoft Fabric that contains a nvarchar (max) column named MessageText. An ID is always contained within the first paragraph of MessageText.

You need to write a Transact-SQL query that uses REGEXP_SUBSTR to extract the ID from MessageText.

What should you include in the query?

  • A. Apply STRING_ESCAPE(MessageText, ‘json’) before calling REGEXP_SUBSTR.
  • B. Cast MessageText to nvarchar (4000) before calling REGEXP_SUBSTR.
  • C. Add a COLLATE Latin1_General_CS_AS clause to MessageText before calling REGEXP_SUBSTR.
  • D. Run TRY_CONVERT(varchar(max), MessageText) before calling REGEXP_SUBSTR.
Click to View Verified Solution & Explanation

Correct Option: B

Explanation:

While REGEXP_SUBSTR in Microsoft Fabric’s SQL database (based on modern SQL Server) supports nvarchar(max) directly as an input data type, it is a common and recommended best practice to cast nvarchar(max) columns to a smaller fixed-length type like nvarchar(4000) when the relevant data is known to reside within the initial characters. The question states that the ID is "always contained within the first paragraph of MessageText", strongly implying the ID is at the beginning of the string and will fit within 4000 characters. Casting to nvarchar(4000) before calling REGEXP_SUBSTR can significantly improve performance by reducing the amount of data the regular expression engine needs to process, especially if MessageText can contain very large strings. This approach optimizes resource usage without sacrificing data integrity for the specific task of extracting an ID from the beginning of the text.

  • A: Apply STRING_ESCAPE(MessageText, ‘json’) before calling REGEXP_SUBSTR. STRING_ESCAPE is used to escape special characters for JSON formatting. It would alter the content of MessageText, potentially making regex matching incorrect, and is irrelevant to the requirement of extracting an ID or handling nvarchar(max) types for REGEXP_SUBSTR.
  • C: Add a COLLATE Latin1_General_CS_AS clause to MessageText before calling REGEXP_SUBSTR. COLLATE specifies the collation for string operations, affecting character comparison rules (e.g., case sensitivity). While collation can be relevant for the regex pattern itself, it does not address the primary concern of efficiently processing an nvarchar(max) column with REGEXP_SUBSTR.
  • D: Run TRY_CONVERT(varchar(max), MessageText) before calling REGEXP_SUBSTR. Converting nvarchar(max) to varchar(max) changes the data type from Unicode to non-Unicode. This can lead to data loss if MessageText contains non-ASCII characters. Furthermore, it doesn’t resolve any potential performance issues or best practices related to processing a very long string with REGEXP_SUBSTR, as varchar(max) can also be extremely large. The underlying challenge with large ‘max’ types would likely persist.

QUESTION 13

You have an Azure SQL database that contains database-level Data Definition Language (DDL) triggers, including a trigger named ddl_Audit.

You need to prevent ddl_Audit from firing during the next deployment. The trigger object must remain in place.

Which Transact-SQL statement should you use?

  • A. ALTER TRIGGER
  • B. ALTER DATABASE
  • C. ALTER SERVER AUDIT SPECIFICATION
  • D. DISABLE TRIGGER
  • E. ALTER DATABASE AUDIT SPECIFICATION
Click to View Verified Solution & Explanation

Correct Option: D

Explanation: The question requires preventing a database-level DDL trigger, ddl_Audit, from firing during a deployment while ensuring the trigger object remains in place. The Transact-SQL statement specifically designed for this purpose is DISABLE TRIGGER. This command deactivates the specified trigger, rendering it inert without removing its definition from the database. Consequently, the trigger will not execute when its associated DDL event occurs. To reactivate it, the ENABLE TRIGGER statement would be used. While ALTER TRIGGER can be used to disable DML triggers, for DDL and logon triggers, DISABLE TRIGGER is the appropriate and recommended command.

QUESTION 14

Your development team uses GitHub Copilot Chat in Microsoft SQL Server Management Studio (SSMS) to generate and run Transact-SQL queries against an Azure SQL database named DB1. DB1 contains tables that store sensitive customer data.

You need to ensure that any Transact-SQL queries that run from GitHub Copilot Chat in SSMS are restricted by the same permissions as the developer’s database login.

What prevents the GitHub Copilot Chat-run queries from accessing data beyond the developer’s access?

  • A. GitHub Copilot Chat runs queries in a read-only sandbox that is isolated from production database permissions.
  • B. GitHub Copilot Chat runs queries by using the developer’s database identity and permissions.
  • C. GitHub Copilot Chat filters query results on the client side to remove rows the developer is unauthorized to see.
  • D. GitHub Copilot Chat uses different row-level security (RLS) policies than the developer.
Click to View Verified Solution & Explanation

Correct Option: B

Explanation:

When GitHub Copilot Chat generates Transact-SQL queries within Microsoft SQL Server Management Studio (SSMS), the execution of these queries occurs through the developer’s established connection to the Azure SQL database. SSMS uses the developer’s database identity and associated permissions to connect to and interact with the database. Consequently, any query, regardless of whether it was manually written or generated by Copilot Chat, will inherit and be restricted by these same permissions. This ensures that the developer cannot inadvertently or intentionally access data beyond their authorized scope, maintaining database security.

QUESTION 15 (Multi-Select)

You have an Azure SQL database named AdventureWorksDB that contains a table named dbo.Employee.

You have a C# Azure Functions app that uses an HTTP-triggered function with an Azure SQL input binding to query dbo.Employee.

You are adding a second function that will react to row changes in dbo.Employee and write structured logs.

You need to configure AdventureWorksDB and the app to meet the following requirements:

Changes to dbo.Employee must trigger the new function within five seconds.

Each invocation must process no more than 100 changes.

Which two database configurations should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

  • A. Create an AFTER trigger on dbo.Employee for Data Manipulation Language (DML).
  • B. Set Sql_Trigger_MaxBatchSize to 100.
  • C. Enable change tracking on the dbo.Employee table.
  • D. Enable change tracking at the database level.
  • E. Set Sql_Trigger_PollingIntervalMs to 5000.
  • F. Enable change data capture (CDC) for dbo.Employee table changes.
Click to View Verified Solution & Explanation

Correct Option: C,D

Explanation: The Azure Functions SQL trigger binding relies on SQL Change Tracking to detect row changes efficiently. To use Change Tracking, it must be enabled at two levels:

  • Database Level: Change tracking must first be enabled for the entire database. This is a prerequisite for enabling it on individual tables. (Option D)
  • Table Level: After enabling it at the database level, change tracking must then be explicitly enabled on the specific table (dbo.Employee in this case) whose changes you want the function to monitor. (Option C)

These two steps configure the database to track changes, which the Azure Function’s SQL trigger can then consume. The requirements for triggering within five seconds and processing no more than 100 changes per invocation are handled by the Azure Function binding’s properties (PollingIntervalMs and MaxBatchSize, respectively), which are *application configurations*, not database configurations.

  • Option A (Create an AFTER trigger): While an AFTER trigger can detect DML changes, it’s not the mechanism used by the Azure Functions SQL trigger binding for efficient change detection. The SQL trigger binding leverages Change Tracking.
  • Option B (Set Sql_Trigger_MaxBatchSize to 100): This is a configuration for the Azure Function’s SQL trigger binding, specifying the maximum number of changes processed per invocation. It is an application-level setting, not a database configuration.
  • Option E (Set Sql_Trigger_PollingIntervalMs to 5000): This is also a configuration for the Azure Function’s SQL trigger binding, controlling how often the function polls for changes. It is an application-level setting, not a database configuration.
  • Option F (Enable change data capture (CDC)): While CDC tracks changes, Change Tracking is generally a lighter-weight and more common choice for the Azure Functions SQL trigger binding scenario. Also, enabling Change Tracking at both database and table levels directly fulfills the requirement for two database configurations that work with the trigger.

QUESTION 16 [DRAG_DROP]

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.

Question Interface:

Question Interface
✔ Correct Answer / Solution View:

Solution Canvas

Click to View Verified Solution & Explanation
Explanation:

The provided solution correctly completes the T-SQL query to parse phone numbers from a text field using regular expression functions available in SQL Server 2022 and later.

✅ Step 1: REGEXP_SUBSTR( matches with -> The first blank for RawNumber
Reasoning: To extract the `RawNumber`, the query needs to find and return the first substring that matches the phone number pattern. The `REGEXP_SUBSTR()` function is specifically designed for this purpose, returning the matched substring or NULL if no match is found.

✅ Step 2: REGEXP_REPLACE( REGEXP_SUBSTR( matches with -> The second blank for DigitsOnly
Reasoning: To get `DigitsOnly`, the operation is two-fold: first, extract the phone number, and second, strip out non-digit characters. This is achieved by nesting `REGEXP_SUBSTR()` inside `REGEXP_REPLACE()`. The outer function replaces all non-digit characters (`\D`) from the extracted substring with an empty string.

✅ Step 3: REGEXP_LIKE( matches with -> The third blank for PhoneStatus
Reasoning: The `PhoneStatus` column requires a boolean check to see if a phone number pattern exists. The `REGEXP_LIKE()` function serves as a predicate, returning 1 for a match and 0 for no match. This fits perfectly with the `CASE WHEN … = 1` logic to return ‘Valid’ or ‘Missing’.

QUESTION 17

You have an Azure SQL database that contains a table named Rooms. Rooms was created by using the following Transact-SQL statement.

You discover that some records in the Rooms table contain NULL values for the Owner field.

You need to ensure that all future records have a value for the Owner field.
What should you add?

  • A. a foreign key
  • B. a check constraint
  • C. a nonclustered index
  • D. a unique constraint
Click to View Verified Solution & Explanation

Correct Option: B

Explanation:

The problem states that the Owner field currently contains NULL values and the requirement is to ensure all future records have a value for this field. This means preventing NULLs from being inserted into the Owner column.

A CHECK constraint can be used to enforce domain integrity by defining a condition that must be true for every row in the table. To prevent NULL values, you can add a CHECK constraint like CHECK (Owner IS NOT NULL). In SQL Server, the NOT NULL constraint is technically a special kind of CHECK constraint, achieving this exact purpose.

Why other options are incorrect:

  • A: A foreign key enforces referential integrity between two tables. It does not inherently prevent NULL values in a column within the same table, unless that column also has a NOT NULL constraint, which is a separate concept.
  • C: A nonclustered index is used to improve the performance of data retrieval operations. It does not enforce data integrity rules or prevent NULL values.
  • D: A unique constraint ensures that all values in a column or set of columns are distinct. While it prevents duplicate non-NULL values, SQL Server allows a single NULL value in a unique column (unless it is also defined as NOT NULL), meaning it would not meet the requirement of ensuring all future records have a value.

QUESTION 18 [DRAG_DROP]

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 nvarchar (max) column named log that stores JSON documents.

You have a daily report that filters by the $.severity JSON property and returns LogId, 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.

Question Interface:

Question Interface
✔ Correct Answer / Solution View:

Solution Canvas

Click to View Verified Solution & Explanation
Explanation:

Audit of the Drag-and-Drop Selections

The provided solution correctly optimizes the query by creating an indexed computed column and a covering index. This approach directly addresses the performance issues of full table scans and key lookups.

Mapping Verification:

✅ Step 1: AS JSON_VALUE([log], '$.severity') PERSISTED matches with -> ALTER TABLE WebSite.Logs ADD severity ... ;

Reasoning: To enable efficient filtering on the JSON `severity` property, you must first expose it as a standard data type column. The JSON_VALUE function extracts a scalar value from the JSON text. Adding the PERSISTED keyword materializes the computed column, which is necessary to create a standard index on it.

✅ Step 2: INCLUDE (LogId, LogDateTime, [log]) matches with -> CREATE INDEX ix_severity ON WebSite.Logs(severity) ... ;

Reasoning: To avoid key lookups, the index must cover all columns required by the query. The index key is `severity` for filtering. By using the INCLUDE clause to add `LogId`, `LogDateTime`, and `log`, the index contains all the data for the report, becoming a covering index and eliminating the need to access the base table.

Case Study: Contoso Ltd.

Instructions
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

To start the case study –
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

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 FeedbackJson column has a full-text index and stores JSON documents in the following format.


The support staff at Contoso never has the UNMASK permission.

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.

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.

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 accuracy JSON 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 MaintenanceEvents table updates the corresponding value in the LastModifiedUtc column 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 FeedbackJson column of the CustomerFeedback table:

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.

QUESTION 19 [HOT_AREA]

Scenario: Contoso Ltd.

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.
NOTE: Each correct selection is worth one point.

✔ Correct Answer:

Hotspot Canvas

Click to View Verified Solution & Explanation
Explanation:

Statement 1: No

The code does not meet the requirement for partitioning. The case study explicitly requires telemetry data to be stored in a partitioned table. The provided T-SQL script uses SYSTEM_VERSIONING = ON, which creates a system-versioned temporal table for tracking historical data changes. This is different from table partitioning, which involves splitting a large table into smaller, more manageable parts based on a partition key. The script lacks the necessary CREATE PARTITION FUNCTION and CREATE PARTITION SCHEME statements.

Statement 2: Yes

The code meets the performance requirement for JSON property querying. The requirement is to enable index seeks on the latitude, longitude, and accuracy JSON properties. The script creates a selective JSON index: CREATE JSON INDEX JI_VehicleTelemetry_Location ON dbo.VehicleTelemetry (LocationJson) FOR ('$.location.latitude', '$.location.longitude', '$.location.accuracy'). This index is specifically designed to optimize queries filtering on these exact properties, allowing the query optimizer to perform an index seek rather than a full scan of the JSON column.

Statement 3: No

The created JSON index, JI_VehicleTelemetry_Location, will not be used for queries filtering on $.location.heading. The index definition explicitly lists only the paths for latitude, longitude, and accuracy. Since $.location.heading is not part of the index definition, the query optimizer cannot use this index to seek on that property and would have to resort to a less efficient method like a table scan.

QUESTION 20 [HOT_AREA]

Scenario: Contoso Ltd.

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.

✔ Correct Answer:

Hotspot Canvas

Click to View Verified Solution & Explanation
Explanation:

Statement 1: The schema meets the security requirements for PII data. (Yes)

The Transact-SQL script defines Dynamic Data Masking (DDM) on the `FullName`, `EmailAddress`, and `PhoneNumber` columns. According to the case study, the support staff lacks the `UNMASK` permission. Consequently, when they query this table, any Personally Identifiable Information (PII) in these columns will be automatically obscured, fulfilling the specified security requirement.

Statement 2: Administrators of the Azure SQL server can see all the rows in dbo.customerProfiles when they use an application. (Yes)

Row-Level Security (RLS) policies do not apply to highly privileged users. Users with administrative privileges, such as members of the `db_owner` role or server administrators, can bypass RLS policies and view all rows in a table. Therefore, an administrator querying the table would not be restricted by the filter predicate and would be able to see all rows.

Statement 3: The masking rules will apply even when row-level security (RLS) filters out rows. (Yes)

Dynamic Data Masking (DDM) and Row-Level Security (RLS) are designed to function together. When a query is executed, RLS first determines the subset of rows that a user is permitted to see. Then, for the rows that are returned, DDM is applied to mask the data in designated columns for users without `UNMASK` permissions. The presence of an RLS policy does not disable or interfere with the DDM rules.

QUESTION 21 [DRAG_DROP]

Scenario: Contoso Ltd.

You need to meet the database performance requirements for maintenance data.

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.

Question Interface:

Question Interface
✔ Correct Answer / Solution View:

Solution Canvas

Click to View Verified Solution & Explanation
Explanation:

ON Clause

✅ Step 1: `m.MaintenanceID = i.MaintenanceID` matches with -> `ON` clause

Reasoning: The goal is to update rows in the `MaintenanceEvents` table that have just been changed. The `inserted` pseudo-table contains the new versions of these rows. To correctly identify which rows in `MaintenanceEvents` (aliased as `m`) to update, we must join it with the `inserted` table (aliased as `i`) using the table’s primary key, which is `MaintenanceID`. This ensures a unique match for each modified row.

WHERE Clause

✅ Step 2: `i.MaintenanceID IS NOT NULL` matches with -> `WHERE` clause

Reasoning: This condition is used to complete the `UPDATE` statement. Since the `INNER JOIN` is performed on the primary key column (`MaintenanceID`), which cannot be `NULL`, this `WHERE` clause is technically redundant. However, it is the only available option that doesn’t break the trigger’s logic. The requirement to “Avoid recursive updates” is met by relying on the default database setting of `RECURSIVE_TRIGGERS OFF`, as none of the provided code options explicitly prevent recursion (e.g., by using `IF UPDATE()` or checking `TRIGGER_NESTLEVEL()`).

Need the Full Exam Simulator?

Practice the complete question bank in a realistic, timed environment with advanced tracking.

Access Full CertoMetrics Engine →

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top