Free Test
Question: / 10
Quiz
Question 1/101/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
You need to generate embeddings to resolve the issues identified by the analysts. Which column
should you use?
should you use?
Select the answer:Select the answer
1 correct answerA.
vehicleLocation
B.
incidentDescrlption
C.
incidentType
D.
SeverityScore
Quiz
Question 2/102/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
You need to recommend a solution for the development team to retrieve the live metadata. The
solution must meet the development requirements.
What should you include in the recommendation?
solution must meet the development requirements.
What should you include in the recommendation?
Select the answer:Select the answer
1 correct answerA.
Export the database schema as a .dacpac file and load the schema into a GitHub Copilot context window.
B.
Add the schema to a GitHub Copilot instruction file.
C.
Use an MCP server
D.
Include the database project in the code repository.
Quiz
Question 3/103/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
You need to recommend a solution that will resolve the ingestion pipeline failure issues. Which two
actions should you recommend? Each correct answer presents part of the solution. NOTE: Each
correct selection is worth one point.
actions should you recommend? Each correct answer presents part of the solution. NOTE: Each
correct selection is worth one point.
Select multiple answer: (Choose 2)Select the answer
2 correct answersA.
Enable snapshot isolation on the database.
B.
Use a trigger to automatically rewrite malformed JSON.
C.
Add foreign key constraints on the table.
D.
Create a unique index on a hash of the payload.
E.
Add a check constraint that validates the JSON structure.
Quiz
Question 4/104/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
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.
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.
Check the image below to see the right answer:Select the answer
1 correct answerThe correct answer is A.
Explanation:
The question asks how to complete a Transact-SQL query to meet the development requirements for the FeedbackJson column. In this type of question, the correct option is the one that uses the proper JSON-related SQL syntax for extracting or handling the data in the required format.
If option A is identified as the correct answer, then it is the choice that correctly matches the expected Transact-SQL behavior for working with JSON data in the FeedbackJson column.
Since the image itself is not visible here, I can only confirm the stated correct option:
A is the correct selection.
If you want, I can also help explain why the other options are incorrect if you provide the answer choices or the query text from the image.
Explanation:
The question asks how to complete a Transact-SQL query to meet the development requirements for the FeedbackJson column. In this type of question, the correct option is the one that uses the proper JSON-related SQL syntax for extracting or handling the data in the required format.
If option A is identified as the correct answer, then it is the choice that correctly matches the expected Transact-SQL behavior for working with JSON data in the FeedbackJson column.
Since the image itself is not visible here, I can only confirm the stated correct option:
A is the correct selection.
If you want, I can also help explain why the other options are incorrect if you provide the answer choices or the query text from the image.
Quiz
Question 5/105/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
DRAG DROP
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.
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.
Check the image below to see the right answer:Select the answer
1 correct answerON → m.maintenanceId = i.maintenanceId
WHERE → m.LastModifiedUtc <> i.LastModifiedUtc
The correct drag-and-drop completion is:
ON m.maintenanceId = i.maintenanceId
WHERE m.LastModifiedUtc <> i.LastModifiedUtc
This satisfies the requirement to ensure that when a row in MaintenanceEvents changes, the
corresponding LastModifiedUtc value is updated to the current system time, while also helping avoid
unnecessary repeat updates.
The inserted pseudo-table in a SQL Server AFTER UPDATE trigger contains the rows that were just
updated. To update the matching row in the base table correctly, the trigger must join the target
table row to the corresponding row in inserted by the table’s primary key. In this schema,
MaintenanceId is the primary key for MaintenanceEvents, so the correct join is m.maintenanceId =
i.maintenanceId. Joining on VehicleId would be incorrect because multiple maintenance rows could
exist for the same vehicle, which could update unintended rows. Microsoft’s trigger documentation
explains that inserted and deleted are used to work with the affected rows and that multi-row logic
should be based on proper key matching.
The WHERE m.LastModifiedUtc <> i.LastModifiedUtc predicate is used to prevent the trigger from re-
updating rows where the timestamp already matches the value in inserted. That reduces redundant
writes and supports the requirement to avoid recursive or repeated update behavior. In practice, this
means the trigger updates only rows whose current stored timestamp differs from the just-updated
version. This is the exam-appropriate pattern for a self-updating timestamp column in an AFTER
UPDATE trigger.
WHERE → m.LastModifiedUtc <> i.LastModifiedUtc
The correct drag-and-drop completion is:
ON m.maintenanceId = i.maintenanceId
WHERE m.LastModifiedUtc <> i.LastModifiedUtc
This satisfies the requirement to ensure that when a row in MaintenanceEvents changes, the
corresponding LastModifiedUtc value is updated to the current system time, while also helping avoid
unnecessary repeat updates.
The inserted pseudo-table in a SQL Server AFTER UPDATE trigger contains the rows that were just
updated. To update the matching row in the base table correctly, the trigger must join the target
table row to the corresponding row in inserted by the table’s primary key. In this schema,
MaintenanceId is the primary key for MaintenanceEvents, so the correct join is m.maintenanceId =
i.maintenanceId. Joining on VehicleId would be incorrect because multiple maintenance rows could
exist for the same vehicle, which could update unintended rows. Microsoft’s trigger documentation
explains that inserted and deleted are used to work with the affected rows and that multi-row logic
should be based on proper key matching.
The WHERE m.LastModifiedUtc <> i.LastModifiedUtc predicate is used to prevent the trigger from re-
updating rows where the timestamp already matches the value in inserted. That reduces redundant
writes and supports the requirement to avoid recursive or repeated update behavior. In practice, this
means the trigger updates only rows whose current stored timestamp differs from the just-updated
version. This is the exam-appropriate pattern for a self-updating timestamp column in an AFTER
UPDATE trigger.
Quiz
Question 6/106/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
You need to recommend a solution to lesolve the slow dashboard query issue. What should you
recommend?
recommend?
Select the answer:Select the answer
1 correct answerA.
Create a clustered index on Lastupdatedutc.
B.
On Fleetid, create a nonclustered index that includes Lastupdatedutc. inginestatus, and BatteryHealth.
C.
On Lastupdatedutc. create a nonclustered index that includes Fleetid.
D.
On Fleetid, create a filtered index where lastupdatedutc > DATEADD(DAV, -7, SYSuTCOATETIME()).
Quiz
Question 7/107/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
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.
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.
Check the image below to see the right answer:Select the answer
1 correct answerThe schema meets the security requirements for PII data. → Yes
Administrators of the Azure SQL server can see all the rows in dbo.CustomerProfiles when they use
an application. → No
The masking rules will apply even when row-level security (RLS) filters out rows. → No
The first statement is Yes because the design combines two relevant SQL security controls for
personally identifiable information: Dynamic Data Masking (DDM) on sensitive columns such as
FullName, EmailAddress, and PhoneNumber, and Row-Level Security (RLS) to restrict which rows a
user can access based on RegionCode. Microsoft documents that DDM limits sensitive data exposure
for nonprivileged users, while RLS restricts row access according to the user executing the query.
Together, these are valid and appropriate controls for protecting PII in Azure SQL Database.
The second statement is No. Administrative users can view unmasked data because administrative
roles effectively have CONTROL, which includes UNMASK. However, that does not mean they
automatically see all rows through the application query path defined by the RLS policy. The security
policy filters rows based on SUSER_SNAME() and matching RegionCode, so row visibility is governed
by the predicate unless the policy is altered or bypassed administratively. DDM and RLS solve
different problems: DDM affects how returned values are shown, while RLS affects which rows are
returned at all.
The third statement is No because masking only applies to data that is actually returned in the query
result set. Microsoft describes DDM as hiding sensitive data in the result set of a query. If RLS filters a
row out, that row is not returned, so there is nothing left for masking to act on. In other words, RLS
eliminates inaccessible rows first from the user’s perspective, and DDM masks sensitive column
values only on rows the user is allowed to see.
Administrators of the Azure SQL server can see all the rows in dbo.CustomerProfiles when they use
an application. → No
The masking rules will apply even when row-level security (RLS) filters out rows. → No
The first statement is Yes because the design combines two relevant SQL security controls for
personally identifiable information: Dynamic Data Masking (DDM) on sensitive columns such as
FullName, EmailAddress, and PhoneNumber, and Row-Level Security (RLS) to restrict which rows a
user can access based on RegionCode. Microsoft documents that DDM limits sensitive data exposure
for nonprivileged users, while RLS restricts row access according to the user executing the query.
Together, these are valid and appropriate controls for protecting PII in Azure SQL Database.
The second statement is No. Administrative users can view unmasked data because administrative
roles effectively have CONTROL, which includes UNMASK. However, that does not mean they
automatically see all rows through the application query path defined by the RLS policy. The security
policy filters rows based on SUSER_SNAME() and matching RegionCode, so row visibility is governed
by the predicate unless the policy is altered or bypassed administratively. DDM and RLS solve
different problems: DDM affects how returned values are shown, while RLS affects which rows are
returned at all.
The third statement is No because masking only applies to data that is actually returned in the query
result set. Microsoft describes DDM as hiding sensitive data in the result set of a query. If RLS filters a
row out, that row is not returned, so there is nothing left for masking to act on. In other words, RLS
eliminates inaccessible rows first from the user’s perspective, and DDM masks sensitive column
values only on rows the user is allowed to see.
Quiz
Question 8/108/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
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?
relevant health summary reports. The solution must minimize latency.
What should you include in the solution?
Select the answer:Select the answer
1 correct answerA.
a computed column that manually compares vector values
B.
a standard nonclustered index on the Fmbeddings (vector (1536)) column
C.
a full-text index on the Fmbeddings (vector (1536)) column
D.
a vector index on the Embedding* (vector (1536)) column
Quiz
Question 9/109/10
Topic 1, Contoso Case Study
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
Existing 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.
Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following;
• Al 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.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary
where fleetld - gFleetld ORDER BV LastUpdatedUtc DESC;
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.
Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over
incident reports.
Security 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 LastModif reduce column to the time of the change.
• Avoid recursive updates.
AI Search, Embedding’s, and Vector indexing
The development learn 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 customer-Feedback 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.
You need to create a table in the database to store the telemetry data. You have the following
Transact-SQL code.
Transact-SQL code.
Check the image below to see the right answer:Select the answer
1 correct answer
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.
Quiz
Question 10/1010/10
DRAG DROP
You have an Azure SQL database named SalesDB that contains tables named Sales.Orders and
Sales.OrderLines. Both tables contain sales data
You have a Retrieval Augmented Generation (RAG) service that queries SalesDB to retrieve order
details and passes the results to a large language model (ILM) as JSON text. The following is a sample
of the JSON.
You need to return one 1SON document per order that includes the order header fields and an array
of related order lines. The LIM must receive a single JSON array of orders, where each order contains
a lines property that is a JSON array of line Items.
Which transact-SQL commands should you use to produce the required JSON shape from the
relational tables? To answer, drag the appropriate commands to the correct operations. Each
command may be used once, more than once, or not at all. Vou may need to drag the split bar
between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
You have an Azure SQL database named SalesDB that contains tables named Sales.Orders and
Sales.OrderLines. Both tables contain sales data
You have a Retrieval Augmented Generation (RAG) service that queries SalesDB to retrieve order
details and passes the results to a large language model (ILM) as JSON text. The following is a sample
of the JSON.
You need to return one 1SON document per order that includes the order header fields and an array
of related order lines. The LIM must receive a single JSON array of orders, where each order contains
a lines property that is a JSON array of line Items.
Which transact-SQL commands should you use to produce the required JSON shape from the
relational tables? To answer, drag the appropriate commands to the correct operations. Each
command may be used once, more than once, or not at all. Vou may need to drag the split bar
between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Check the image below to see the right answer:Select the answer
1 correct answerSerialize the order-level JSON: FOR JSON PATH
Generate a nested lines array: JSON_QUERY
Extract a single scalar value from the JSON text: JSON_VALUE
The correct mapping is based on how SQL Server and Azure SQL JSON functions are designed to
shape relational data into JSON for AI and RAG scenarios.
To serialize the order-level JSON, use FOR JSON PATH. Microsoft documents that FOR JSON PATH
gives you full control over the JSON output shape and formats the result as an array of JSON objects.
It is the standard way to turn relational query results into the JSON structure needed by downstream
consumers such as APIs and LLM-based RAG services. It also supports nested output through
subqueries and aliases.
To generate a nested lines array, use JSON_QUERY. Microsoft explains that JSON_QUERY returns a
JSON object or array from JSON text, and it is used when you want to preserve a JSON fragment
instead of treating it as plain text. In this scenario, the nested lines property must be emitted as a
proper JSON array inside each order document, so JSON_QUERY is the correct command to embed
that array in the final JSON shape.
To extract a single scalar value from the JSON text, use JSON_VALUE. Microsoft explicitly states that
JSON_VALUE extracts a scalar value from a JSON string, while JSON_QUERY is for objects or arrays. So
whenever the requirement is to pull out one property such as an order number, currency code, or
customer ID from JSON text, JSON_VALUE is the correct function.
The unused commands are not the best fit here:
OPENJSON is primarily for parsing JSON into rows and columns, not for shaping relational tables into
nested output.
JSON_MODIFY is for updating JSON text, not generating the required output structure.
So the drag-and-drop answers are:
Serialize the order-level JSON → FOR JSON PATH
Generate a nested lines array → JSON_QUERY
Extract a single scalar value from the JSON text → JSON_VALUE
Generate a nested lines array: JSON_QUERY
Extract a single scalar value from the JSON text: JSON_VALUE
The correct mapping is based on how SQL Server and Azure SQL JSON functions are designed to
shape relational data into JSON for AI and RAG scenarios.
To serialize the order-level JSON, use FOR JSON PATH. Microsoft documents that FOR JSON PATH
gives you full control over the JSON output shape and formats the result as an array of JSON objects.
It is the standard way to turn relational query results into the JSON structure needed by downstream
consumers such as APIs and LLM-based RAG services. It also supports nested output through
subqueries and aliases.
To generate a nested lines array, use JSON_QUERY. Microsoft explains that JSON_QUERY returns a
JSON object or array from JSON text, and it is used when you want to preserve a JSON fragment
instead of treating it as plain text. In this scenario, the nested lines property must be emitted as a
proper JSON array inside each order document, so JSON_QUERY is the correct command to embed
that array in the final JSON shape.
To extract a single scalar value from the JSON text, use JSON_VALUE. Microsoft explicitly states that
JSON_VALUE extracts a scalar value from a JSON string, while JSON_QUERY is for objects or arrays. So
whenever the requirement is to pull out one property such as an order number, currency code, or
customer ID from JSON text, JSON_VALUE is the correct function.
The unused commands are not the best fit here:
OPENJSON is primarily for parsing JSON into rows and columns, not for shaping relational tables into
nested output.
JSON_MODIFY is for updating JSON text, not generating the required output structure.
So the drag-and-drop answers are:
Serialize the order-level JSON → FOR JSON PATH
Generate a nested lines array → JSON_QUERY
Extract a single scalar value from the JSON text → JSON_VALUE
Looking for more questions?Buy now
DP-800: Microsoft Developing AI-Enabled Database Solutions Practice test unlocks all online simulator questions
Thank you for choosing the free version of the DP-800: Microsoft Developing AI-Enabled Database Solutions practice test! Further deepen your knowledge on Microsoft Simulator; by unlocking the full version of our DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator you will be able to take tests with over 61 constantly updated questions and easily pass your exam. 98% of people pass the exam in the first attempt after preparing with our 61 questions.
BUY NOWWhat to expect from our DP-800: Microsoft Developing AI-Enabled Database Solutions practice tests and how to prepare for any exam?
The DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator Practice Tests are part of the Microsoft Database and are the best way to prepare for any DP-800: Microsoft Developing AI-Enabled Database Solutions exam. The DP-800: Microsoft Developing AI-Enabled Database Solutions practice tests consist of 61 questions and are written by experts to help you and prepare you to pass the exam on the first attempt. The DP-800: Microsoft Developing AI-Enabled Database Solutions database includes questions from previous and other exams, which means you will be able to practice simulating past and future questions. Preparation with DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator will also give you an idea of the time it will take to complete each section of the DP-800: Microsoft Developing AI-Enabled Database Solutions practice test . It is important to note that the DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator does not replace the classic DP-800: Microsoft Developing AI-Enabled Database Solutions study guides; however, the Simulator provides valuable insights into what to expect and how much work needs to be done to prepare for the DP-800: Microsoft Developing AI-Enabled Database Solutions exam.
BUY NOWDP-800: Microsoft Developing AI-Enabled Database Solutions Practice test therefore represents an excellent tool to prepare for the actual exam together with our Microsoft practice test . Our DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator will help you assess your level of preparation and understand your strengths and weaknesses. Below you can read all the quizzes you will find in our DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator and how our unique DP-800: Microsoft Developing AI-Enabled Database Solutions Database made up of real questions:
Info quiz:
- Quiz name:DP-800: Microsoft Developing AI-Enabled Database Solutions
- Total number of questions:61
- Number of questions for the test:50
- Pass score:80%
You can prepare for the DP-800: Microsoft Developing AI-Enabled Database Solutions exams with our mobile app. It is very easy to use and even works offline in case of network failure, with all the functions you need to study and practice with our DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator.
Use our Mobile App, available for both Android and iOS devices, with our DP-800: Microsoft Developing AI-Enabled Database Solutions Simulator . You can use it anywhere and always remember that our mobile app is free and available on all stores.
Our Mobile App contains all DP-800: Microsoft Developing AI-Enabled Database Solutions practice tests which consist of 61 questions and also provide study material to pass the final DP-800: Microsoft Developing AI-Enabled Database Solutions exam with guaranteed success. Our DP-800: Microsoft Developing AI-Enabled Database Solutions database contain hundreds of questions and Microsoft Tests related to DP-800: Microsoft Developing AI-Enabled Database Solutions Exam. This way you can practice anywhere you want, even offline without the internet.
BUY NOW