Quiz DP-800: Microsoft Developing AI-Enabled Database Solutions
Free Test
Question: / 10
Quiz
Question 1/101/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youneedtogenerateembeddingstoresolvetheissuesidentifiedbytheanalysts.Whichcolumnshould
youuse?
A.vehicleLocation
B.incidentDescrlption
C.incidentType
D.SeverityScore
youuse?
A.vehicleLocation
B.incidentDescrlption
C.incidentType
D.SeverityScore
Check the image below to see the right answer:Select the answer
1 correct answerThecorrectcolumntouseforgeneratingembeddingsisincidentDescrlptionbecauseembeddingsare
intendedtorepresentthesemanticmeaningofrichtextualcontent,notsimplecategorical,numeric,or
location-onlyvalues.Microsoft’sDP-800studyguideexplicitlyincludesskillssuchasidentifyingwhich
columnstoincludeinembeddings,generatingembeddings,andimplementingsemanticvectorsearch
forscenarioswhereusersneedtofindsimilarrecordsbasedonmeaningratherthanexactmatches.
Inthisscenario,analystsreportthatitisdifficulttofindsimilarincidentsbasedondetailssuchas
weather,trafficconditions,andlocation.Thosearedescriptivecontextelementsthataretypically
capturedinafree-textincidentdescriptionfield.AnembeddinggeneratedfromincidentDescrlptioncan
encodethesemanticrelationshipsamongthesenarrativedetails,makingitsuitableforsimilaritysearch,
semanticsearch,andRAGretrieval.Microsoftdocumentationonvectorsandembeddingsexplainsthat
embeddingsaregeneratedfromtextdataandthenstoredforvectorsearchtofindsemanticallyrelated
items.
[http://www.justcerts.com](http://www.justcerts.com)
vehicleLocationistoonarrowandusuallybetterhandledwithgeospatialfiltering,notembeddings.
incidentTypeislikelycategoricalandtoolowinsemanticrichness.
SeverityScoreisnumericandnotappropriateastheprimarysourceforsemanticembeddings.
Microsoftalsonotesthatwhenmultipleusefulattributesexist,youcaneitherembedeachtextcolumn
separatelyorconcatenaterelevanttextfieldsintoonetextualrepresentationbeforegeneratingthe
embedding.Butamongtheoptionsgiven,thebestandmostexam-alignedansweristhetextual
narrativecolumn:incidentDescrlption.
intendedtorepresentthesemanticmeaningofrichtextualcontent,notsimplecategorical,numeric,or
location-onlyvalues.Microsoft’sDP-800studyguideexplicitlyincludesskillssuchasidentifyingwhich
columnstoincludeinembeddings,generatingembeddings,andimplementingsemanticvectorsearch
forscenarioswhereusersneedtofindsimilarrecordsbasedonmeaningratherthanexactmatches.
Inthisscenario,analystsreportthatitisdifficulttofindsimilarincidentsbasedondetailssuchas
weather,trafficconditions,andlocation.Thosearedescriptivecontextelementsthataretypically
capturedinafree-textincidentdescriptionfield.AnembeddinggeneratedfromincidentDescrlptioncan
encodethesemanticrelationshipsamongthesenarrativedetails,makingitsuitableforsimilaritysearch,
semanticsearch,andRAGretrieval.Microsoftdocumentationonvectorsandembeddingsexplainsthat
embeddingsaregeneratedfromtextdataandthenstoredforvectorsearchtofindsemanticallyrelated
items.
[http://www.justcerts.com](http://www.justcerts.com)
vehicleLocationistoonarrowandusuallybetterhandledwithgeospatialfiltering,notembeddings.
incidentTypeislikelycategoricalandtoolowinsemanticrichness.
SeverityScoreisnumericandnotappropriateastheprimarysourceforsemanticembeddings.
Microsoftalsonotesthatwhenmultipleusefulattributesexist,youcaneitherembedeachtextcolumn
separatelyorconcatenaterelevanttextfieldsintoonetextualrepresentationbeforegeneratingthe
embedding.Butamongtheoptionsgiven,thebestandmostexam-alignedansweristhetextual
narrativecolumn:incidentDescrlption.
Quiz
Question 2/102/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youneedtorecommendasolutionforthedevelopmentteamtoretrievethelivemetadata.Thesolution
mustmeetthedevelopmentrequirements.
Whatshouldyouincludeintherecommendation?
A.Exportthedatabaseschemaasa.dacpacfileandloadtheschemaintoaGitHubCopilotcontext
window.
B.AddtheschematoaGitHubCopilotinstructionfile.
C.UseanMCPserver
D.Includethedatabaseprojectinthecoderepository.
mustmeetthedevelopmentrequirements.
Whatshouldyouincludeintherecommendation?
A.Exportthedatabaseschemaasa.dacpacfileandloadtheschemaintoaGitHubCopilotcontext
window.
B.AddtheschematoaGitHubCopilotinstructionfile.
C.UseanMCPserver
D.Includethedatabaseprojectinthecoderepository.
Check the image below to see the right answer:Select the answer
1 correct answerThebestrecommendationistouseanMCPserver.IntheofficialDP-800studyguide,Microsoftexplicitly
listsskillssuchasconfiguringModelContextProtocol(MCP)tooloptionsinaGitHubCopilotsessionand
connectingtoMCPserverendpoints,includingMicrosoftSQLServerandFabricLakehouse.Thatmakes
MCPtheexam-alignedmechanismforenablingAI-assistedtoolstoworkwithlivedatabasecontext
ratherthanstaticsnapshots.
Thisalsomatchesthestateddevelopmentrequirement:theteamwilluseVisualStudioCodeandGitHub
Copilotandneedstoretrievelivemetadatafromthedatabases.Microsoft’sdocumentationforGitHub
CopilotwiththeMSSQLextensionexplainsthatCopilotworkswithanactivedatabaseconnection,
providesschema-awaresuggestions,supportschattingwithaconnecteddatabase,andadaptsresponses
basedonthecurrentdatabasecontext.MicrosoftalsodocumentsMCPasthestandardwayforAItools
toconnecttoexternalsystemsanddatasourcesthroughdiscoverabletoolsandendpoints.
Theotheroptionsdonotsatisfythe“livemetadata”requirementaswell:
[http://www.justcerts.com](http://www.justcerts.com)
ACopilotinstructionfileprovidesguidance,notlivedatabasediscovery.
Includingthedatabaseprojectintherepositoryhelpssourcecontrolanddeployment,butitstilldoesnot
providelivedatabasemetadatabyitself.
listsskillssuchasconfiguringModelContextProtocol(MCP)tooloptionsinaGitHubCopilotsessionand
connectingtoMCPserverendpoints,includingMicrosoftSQLServerandFabricLakehouse.Thatmakes
MCPtheexam-alignedmechanismforenablingAI-assistedtoolstoworkwithlivedatabasecontext
ratherthanstaticsnapshots.
Thisalsomatchesthestateddevelopmentrequirement:theteamwilluseVisualStudioCodeandGitHub
Copilotandneedstoretrievelivemetadatafromthedatabases.Microsoft’sdocumentationforGitHub
CopilotwiththeMSSQLextensionexplainsthatCopilotworkswithanactivedatabaseconnection,
providesschema-awaresuggestions,supportschattingwithaconnecteddatabase,andadaptsresponses
basedonthecurrentdatabasecontext.MicrosoftalsodocumentsMCPasthestandardwayforAItools
toconnecttoexternalsystemsanddatasourcesthroughdiscoverabletoolsandendpoints.
Theotheroptionsdonotsatisfythe“livemetadata”requirementaswell:
[http://www.justcerts.com](http://www.justcerts.com)
ACopilotinstructionfileprovidesguidance,notlivedatabasediscovery.
Includingthedatabaseprojectintherepositoryhelpssourcecontrolanddeployment,butitstilldoesnot
providelivedatabasemetadatabyitself.
Quiz
Question 3/103/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youneedtorecommendasolutionthatwillresolvetheingestionpipelinefailureissues.Whichtwo
actionsshouldyourecommend?Eachcorrectanswerpresentspartofthesolution.NOTE:Eachcorrect
selectionisworthonepoint.
A.Enablesnapshotisolationonthedatabase.
B.UseatriggertoautomaticallyrewritemalformedJSON.
C.Addforeignkeyconstraintsonthetable.
D.Createauniqueindexonahashofthepayload.
E.AddacheckconstraintthatvalidatestheJSONstructure.
actionsshouldyourecommend?Eachcorrectanswerpresentspartofthesolution.NOTE:Eachcorrect
selectionisworthonepoint.
A.Enablesnapshotisolationonthedatabase.
B.UseatriggertoautomaticallyrewritemalformedJSON.
C.Addforeignkeyconstraintsonthetable.
D.Createauniqueindexonahashofthepayload.
E.AddacheckconstraintthatvalidatestheJSONstructure.
Check the image below to see the right answer:Select the answer
2 correct answersThetwocorrectactionsareDandEbecausetheingestionfailuresarecausedbymalformedJSONand
duplicatepayloads,andthesetwocontrolsaddressthosetwoproblemsdirectly.Microsoft’sJSON
documentationstatesthatSQLServerandAzureSQLsupportvalidatingJSONwithISJSON,andMicrosoft
specificallyrecommendsusingaCHECKconstrainttoensureJSONtextstoredinacolumnisproperly
formatted.
Fortheduplicate-payloadissue,creatingauniqueindexonahashofthepayloadistheappropriate
design.MicrosoftdocumentsusinghashingfunctionssuchasHASHBYTEStohashcolumnvalues,and
SQLServerallowsadeterministiccomputedcolumntobeusedasakeycolumninaUNIQUEconstraint
oruniqueindex.Thatmakesapersistedhash-basedcomputedcolumnplusauniqueindexapractical
andexam-consistentwaytorejectduplicatepayloadsefficiently.
Theotheroptionsdonotsolvethestatedrootcauses:
Snapshotisolationaddressesconcurrencybehavior,notmalformedJSONorduplicatepayloaddetection.
AtriggertorewritemalformedJSONisnottherightintegritycontrolandisbrittle.
Foreignkeyconstraintsenforcereferentialintegrity,notJSONvalidityorduplicate-payloadprevention
[http://www.justcerts.com](http://www.justcerts.com)
YouneedtomeetthedevelopmentrequirementsfortheFeedbackJsoncolumn
HowshouldyoucompletetheTransactSQLquery?Toanswer,selecttheappropriateoptionsinthe
answerarea.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
JSON_VALUE(f.FeedbackJson,'$.text')ASFeedbackText
CONTAINS(FeedbackJson,@Keyword)
SimilarityScore
Thesethreeselectionsarethecorrectwaytocompletethequerybecausetheyalignexactlywiththe
statedrequirementsfortheFeedbackJsoncolumn.
First,toextractthecustomerfeedbacktextfromtheJSONdocument,thecorrectexpressionis
JSON_VALUE(f.FeedbackJson,'$.text')ASFeedbackText.MicrosoftdocumentsthatJSON_VALUEisused
toextractascalarvaluefromJSON,whileJSON_QUERYisusedforreturninganobjectorarray.Since
$.textisthetextualfeedbackstring,JSON_VALUEisthecorrectfunction.
Second,tofilterrowswheretheJSONtextcontainsakeyword,thebestchoiceis
CONTAINS(FeedbackJson,@Keyword).ThescenarioexplicitlystatesthatFeedbackJsonalreadyhasafull-
textindex,andMicrosoftdocumentsthatCONTAINSisthefull-textpredicateusedintheWHEREclause
tosearchfull-textindexedcharacterdata.ThatmakesitmoreappropriatethanusingEDIT_DISTANCEfor
keywordfiltering.
Third,toordertheresultsbysimilarityscore,highestfirst,thecorrectitemisSimilarityScoreinthe
ORDERBYclause,whichwouldbepairedwithDESCinthequery.Thismatchestherequirementtosort
bythecomputedfuzzysimilarityvalue.TheDP-800studyguidespecificallyincludeswritingqueriesthat
usefuzzystringmatchingfunctionssuchasEDIT_DISTANCE,whichsupportstheearliercomputed
SimilarityScoreexpressioninthequery.
duplicatepayloads,andthesetwocontrolsaddressthosetwoproblemsdirectly.Microsoft’sJSON
documentationstatesthatSQLServerandAzureSQLsupportvalidatingJSONwithISJSON,andMicrosoft
specificallyrecommendsusingaCHECKconstrainttoensureJSONtextstoredinacolumnisproperly
formatted.
Fortheduplicate-payloadissue,creatingauniqueindexonahashofthepayloadistheappropriate
design.MicrosoftdocumentsusinghashingfunctionssuchasHASHBYTEStohashcolumnvalues,and
SQLServerallowsadeterministiccomputedcolumntobeusedasakeycolumninaUNIQUEconstraint
oruniqueindex.Thatmakesapersistedhash-basedcomputedcolumnplusauniqueindexapractical
andexam-consistentwaytorejectduplicatepayloadsefficiently.
Theotheroptionsdonotsolvethestatedrootcauses:
Snapshotisolationaddressesconcurrencybehavior,notmalformedJSONorduplicatepayloaddetection.
AtriggertorewritemalformedJSONisnottherightintegritycontrolandisbrittle.
Foreignkeyconstraintsenforcereferentialintegrity,notJSONvalidityorduplicate-payloadprevention
[http://www.justcerts.com](http://www.justcerts.com)
YouneedtomeetthedevelopmentrequirementsfortheFeedbackJsoncolumn
HowshouldyoucompletetheTransactSQLquery?Toanswer,selecttheappropriateoptionsinthe
answerarea.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
JSON_VALUE(f.FeedbackJson,'$.text')ASFeedbackText
CONTAINS(FeedbackJson,@Keyword)
SimilarityScore
Thesethreeselectionsarethecorrectwaytocompletethequerybecausetheyalignexactlywiththe
statedrequirementsfortheFeedbackJsoncolumn.
First,toextractthecustomerfeedbacktextfromtheJSONdocument,thecorrectexpressionis
JSON_VALUE(f.FeedbackJson,'$.text')ASFeedbackText.MicrosoftdocumentsthatJSON_VALUEisused
toextractascalarvaluefromJSON,whileJSON_QUERYisusedforreturninganobjectorarray.Since
$.textisthetextualfeedbackstring,JSON_VALUEisthecorrectfunction.
Second,tofilterrowswheretheJSONtextcontainsakeyword,thebestchoiceis
CONTAINS(FeedbackJson,@Keyword).ThescenarioexplicitlystatesthatFeedbackJsonalreadyhasafull-
textindex,andMicrosoftdocumentsthatCONTAINSisthefull-textpredicateusedintheWHEREclause
tosearchfull-textindexedcharacterdata.ThatmakesitmoreappropriatethanusingEDIT_DISTANCEfor
keywordfiltering.
Third,toordertheresultsbysimilarityscore,highestfirst,thecorrectitemisSimilarityScoreinthe
ORDERBYclause,whichwouldbepairedwithDESCinthequery.Thismatchestherequirementtosort
bythecomputedfuzzysimilarityvalue.TheDP-800studyguidespecificallyincludeswritingqueriesthat
usefuzzystringmatchingfunctionssuchasEDIT_DISTANCE,whichsupportstheearliercomputed
SimilarityScoreexpressioninthequery.
Quiz
Question 4/104/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
DRAGDROP
Youneedtomeetthedatabaseperformancerequirementsformaintenancedata
HowshouldyoucompletetheTransact-SQLcode?Toanswer,dragtheappropriatevaluestothecorrect
targets.Eachvaluemaybeusedonce,morethanonce,ornotatall.Youmayneedtodragthesplitbar
betweenpanesorscrolltoviewcontent.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Youneedtomeetthedatabaseperformancerequirementsformaintenancedata
HowshouldyoucompletetheTransact-SQLcode?Toanswer,dragtheappropriatevaluestothecorrect
targets.Eachvaluemaybeusedonce,morethanonce,ornotatall.Youmayneedtodragthesplitbar
betweenpanesorscrolltoviewcontent.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Check the image below to see the right answer:Select the answer
1 correct answerON→m.maintenanceId=i.maintenanceId
WHERE→m.LastModifiedUtc<>i.LastModifiedUtc
Thecorrectdrag-and-dropcompletionis:
ONm.maintenanceId=i.maintenanceId
WHEREm.LastModifiedUtc<>i.LastModifiedUtc
ThissatisfiestherequirementtoensurethatwhenarowinMaintenanceEventschanges,the
correspondingLastModifiedUtcvalueisupdatedtothecurrentsystemtime,whilealsohelpingavoid
unnecessaryrepeatupdates.
Theinsertedpseudo-tableinaSQLServerAFTERUPDATEtriggercontainstherowsthatwerejust
updated.Toupdatethematchingrowinthebasetablecorrectly,thetriggermustjointhetargettable
rowtothecorrespondingrowininsertedbythetable’sprimarykey.Inthisschema,MaintenanceIdis
theprimarykeyforMaintenanceEvents,sothecorrectjoinism.maintenanceId=i.maintenanceId.
JoiningonVehicleIdwouldbeincorrectbecausemultiplemaintenancerowscouldexistforthesame
vehicle,whichcouldupdateunintendedrows.Microsoft’striggerdocumentationexplainsthatinserted
anddeletedareusedtoworkwiththeaffectedrowsandthatmulti-rowlogicshouldbebasedonproper
keymatching.
TheWHEREm.LastModifiedUtc<>i.LastModifiedUtcpredicateisusedtopreventthetriggerfromre-
updatingrowswherethetimestampalreadymatchesthevalueininserted.Thatreducesredundant
[http://www.justcerts.com](http://www.justcerts.com)
meansthetriggerupdatesonlyrowswhosecurrentstoredtimestampdiffersfromthejust-updated
version.Thisistheexam-appropriatepatternforaself-updatingtimestampcolumninanAFTERUPDATE
trigger.
WHERE→m.LastModifiedUtc<>i.LastModifiedUtc
Thecorrectdrag-and-dropcompletionis:
ONm.maintenanceId=i.maintenanceId
WHEREm.LastModifiedUtc<>i.LastModifiedUtc
ThissatisfiestherequirementtoensurethatwhenarowinMaintenanceEventschanges,the
correspondingLastModifiedUtcvalueisupdatedtothecurrentsystemtime,whilealsohelpingavoid
unnecessaryrepeatupdates.
Theinsertedpseudo-tableinaSQLServerAFTERUPDATEtriggercontainstherowsthatwerejust
updated.Toupdatethematchingrowinthebasetablecorrectly,thetriggermustjointhetargettable
rowtothecorrespondingrowininsertedbythetable’sprimarykey.Inthisschema,MaintenanceIdis
theprimarykeyforMaintenanceEvents,sothecorrectjoinism.maintenanceId=i.maintenanceId.
JoiningonVehicleIdwouldbeincorrectbecausemultiplemaintenancerowscouldexistforthesame
vehicle,whichcouldupdateunintendedrows.Microsoft’striggerdocumentationexplainsthatinserted
anddeletedareusedtoworkwiththeaffectedrowsandthatmulti-rowlogicshouldbebasedonproper
keymatching.
TheWHEREm.LastModifiedUtc<>i.LastModifiedUtcpredicateisusedtopreventthetriggerfromre-
updatingrowswherethetimestampalreadymatchesthevalueininserted.Thatreducesredundant
[http://www.justcerts.com](http://www.justcerts.com)
meansthetriggerupdatesonlyrowswhosecurrentstoredtimestampdiffersfromthejust-updated
version.Thisistheexam-appropriatepatternforaself-updatingtimestampcolumninanAFTERUPDATE
trigger.
Quiz
Question 5/105/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youneedtorecommendasolutiontolesolvetheslowdashboardqueryissue.Whatshouldyou
recommend?
A.CreateaclusteredindexonLastupdatedutc.
B.OnFleetid,createanonclusteredindexthatincludesLastupdatedutc.inginestatus,andBatteryHealth.
C.OnLastupdatedutc.createanonclusteredindexthatincludesFleetid.
D.OnFleetid,createafilteredindexwherelastupdatedutc >DATEADD(DAV, -7, SYSuTCOATETIME()).
recommend?
A.CreateaclusteredindexonLastupdatedutc.
B.OnFleetid,createanonclusteredindexthatincludesLastupdatedutc.inginestatus,andBatteryHealth.
C.OnLastupdatedutc.createanonclusteredindexthatincludesFleetid.
D.OnFleetid,createafilteredindexwherelastupdatedutc >DATEADD(DAV, -7, SYSuTCOATETIME()).
Check the image below to see the right answer:Select the answer
1 correct answerThebestrecommendationisBbecausetheslowqueryfiltersonFleetIdandreturnsLastUpdatedUtc,
EngineStatus,andBatteryHealth.AnonclusteredindexwithFleetIdasthekeycolumnallowsthe
optimizertoperformanindexseekinsteadofaclusteredindexscan,andincludingtheotherselected
columnsmakestheindexcovering,whichreducesextralookupsandI/O.Microsoft’sSQLServerindexing
guidancestatesthatanonclusteredindexwithincludedcolumnscansignificantlyimproveperformance
whenallquerycolumnsareavailableintheindex,becausetheoptimizercansatisfythequerydirectly
fromtheindex.
Thequeryis:
SELECTVehicleId,LastUpdatedUtc,EngineStatus,BatteryHealth
FROMdbo.VehicleHealthSummary
WHEREFleetId=@FleetId
ORDERBYLastUpdatedUtcDESC;
Amongthegivenchoices,FleetIdisthemostimportantsearchargumentbecauseitappearsinthe
WHEREpredicate.Microsoft’sindexdesignguidancerecommendsputtingcolumnsusedforsearchingin
thekeyandusingnonkeyincludedcolumnstocovertherestofthequeryefficiently.
Whytheotheroptionsareweaker:
AisnotappropriatebecausechangingtheclusteredindextoLastUpdatedUtcwouldnottargetthemain
filterpredicateonFleetId,andatablecanhaveonlyoneclusteredindex.
CmakesLastUpdatedUtcthekey,whichispoorforaquerywhoseprimaryfilterisFleetId.
[http://www.justcerts.com](http://www.justcerts.com)
filteredindexesaremeantforawell-definedsubset;thisoptionalsousesatime-basedexpressionthatis
notalignedtothestatedquerypattern.
Strictlyspeaking,themostoptimaldesignforbothfilteringandorderingwouldusuallybeacomposite
keylike(FleetId,LastUpdatedUtc),butsincethatisnotoneoftheavailableoptions,Bisthecorrectexam
answer.
EngineStatus,andBatteryHealth.AnonclusteredindexwithFleetIdasthekeycolumnallowsthe
optimizertoperformanindexseekinsteadofaclusteredindexscan,andincludingtheotherselected
columnsmakestheindexcovering,whichreducesextralookupsandI/O.Microsoft’sSQLServerindexing
guidancestatesthatanonclusteredindexwithincludedcolumnscansignificantlyimproveperformance
whenallquerycolumnsareavailableintheindex,becausetheoptimizercansatisfythequerydirectly
fromtheindex.
Thequeryis:
SELECTVehicleId,LastUpdatedUtc,EngineStatus,BatteryHealth
FROMdbo.VehicleHealthSummary
WHEREFleetId=@FleetId
ORDERBYLastUpdatedUtcDESC;
Amongthegivenchoices,FleetIdisthemostimportantsearchargumentbecauseitappearsinthe
WHEREpredicate.Microsoft’sindexdesignguidancerecommendsputtingcolumnsusedforsearchingin
thekeyandusingnonkeyincludedcolumnstocovertherestofthequeryefficiently.
Whytheotheroptionsareweaker:
AisnotappropriatebecausechangingtheclusteredindextoLastUpdatedUtcwouldnottargetthemain
filterpredicateonFleetId,andatablecanhaveonlyoneclusteredindex.
CmakesLastUpdatedUtcthekey,whichispoorforaquerywhoseprimaryfilterisFleetId.
[http://www.justcerts.com](http://www.justcerts.com)
filteredindexesaremeantforawell-definedsubset;thisoptionalsousesatime-basedexpressionthatis
notalignedtothestatedquerypattern.
Strictlyspeaking,themostoptimaldesignforbothfilteringandorderingwouldusuallybeacomposite
keylike(FleetId,LastUpdatedUtc),butsincethatisnotoneoftheavailableoptions,Bisthecorrectexam
answer.
Quiz
Question 6/106/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youarecreatingatablethatwillstorecustomerprofiles.
YouhavethefollowingTransact-SQLcode.
Foreachofthefollowingstatements,selectYesifthestatementistrue.Otherwise,selectNo.NOTE:
EachcorrectselectionIsworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
YouhavethefollowingTransact-SQLcode.
Foreachofthefollowingstatements,selectYesifthestatementistrue.Otherwise,selectNo.NOTE:
EachcorrectselectionIsworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Check the image below to see the right answer:Select the answer
1 correct answerTheschemameetsthesecurityrequirementsforPIIdata.→Yes
AdministratorsoftheAzureSQLservercanseealltherowsindbo.CustomerProfileswhentheyusean
application.→No
Themaskingruleswillapplyevenwhenrow-levelsecurity(RLS)filtersoutrows.→No
ThefirststatementisYesbecausethedesigncombinestworelevantSQLsecuritycontrolsforpersonally
identifiableinformation:DynamicDataMasking(DDM)onsensitivecolumnssuchasFullName,
EmailAddress,andPhoneNumber,andRow-LevelSecurity(RLS)torestrictwhichrowsausercanaccess
basedonRegionCode.MicrosoftdocumentsthatDDMlimitssensitivedataexposurefornonprivileged
users,whileRLSrestrictsrowaccessaccordingtotheuserexecutingthequery.Together,thesearevalid
andappropriatecontrolsforprotectingPIIinAzureSQLDatabase.
ThesecondstatementisNo.Administrativeuserscanviewunmaskeddatabecauseadministrativeroles
effectivelyhaveCONTROL,whichincludesUNMASK.However,thatdoesnotmeantheyautomatically
seeallrowsthroughtheapplicationquerypathdefinedbytheRLSpolicy.Thesecuritypolicyfiltersrows
basedonSUSER_SNAME()andmatchingRegionCode,sorowvisibilityisgovernedbythepredicate
unlessthepolicyisalteredorbypassedadministratively.DDMandRLSsolvedifferentproblems:DDM
affectshowreturnedvaluesareshown,whileRLSaffectswhichrowsarereturnedatall.
ThethirdstatementisNobecausemaskingonlyappliestodatathatisactuallyreturnedinthequery
resultset.MicrosoftdescribesDDMashidingsensitivedataintheresultsetofaquery.IfRLSfiltersa
rowout,thatrowisnotreturned,sothereisnothingleftformaskingtoacton.Inotherwords,RLS
eliminatesinaccessiblerowsfirstfromtheuser’sperspective,andDDMmaskssensitivecolumnvalues
onlyonrowstheuserisallowedtosee.
AdministratorsoftheAzureSQLservercanseealltherowsindbo.CustomerProfileswhentheyusean
application.→No
Themaskingruleswillapplyevenwhenrow-levelsecurity(RLS)filtersoutrows.→No
ThefirststatementisYesbecausethedesigncombinestworelevantSQLsecuritycontrolsforpersonally
identifiableinformation:DynamicDataMasking(DDM)onsensitivecolumnssuchasFullName,
EmailAddress,andPhoneNumber,andRow-LevelSecurity(RLS)torestrictwhichrowsausercanaccess
basedonRegionCode.MicrosoftdocumentsthatDDMlimitssensitivedataexposurefornonprivileged
users,whileRLSrestrictsrowaccessaccordingtotheuserexecutingthequery.Together,thesearevalid
andappropriatecontrolsforprotectingPIIinAzureSQLDatabase.
ThesecondstatementisNo.Administrativeuserscanviewunmaskeddatabecauseadministrativeroles
effectivelyhaveCONTROL,whichincludesUNMASK.However,thatdoesnotmeantheyautomatically
seeallrowsthroughtheapplicationquerypathdefinedbytheRLSpolicy.Thesecuritypolicyfiltersrows
basedonSUSER_SNAME()andmatchingRegionCode,sorowvisibilityisgovernedbythepredicate
unlessthepolicyisalteredorbypassedadministratively.DDMandRLSsolvedifferentproblems:DDM
affectshowreturnedvaluesareshown,whileRLSaffectswhichrowsarereturnedatall.
ThethirdstatementisNobecausemaskingonlyappliestodatathatisactuallyreturnedinthequery
resultset.MicrosoftdescribesDDMashidingsensitivedataintheresultsetofaquery.IfRLSfiltersa
rowout,thatrowisnotreturned,sothereisnothingleftformaskingtoacton.Inotherwords,RLS
eliminatesinaccessiblerowsfirstfromtheuser’sperspective,andDDMmaskssensitivecolumnvalues
onlyonrowstheuserisallowedtosee.
Quiz
Question 7/107/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youneedtoenablesimilaritysearchtoprovidetheanalystswiththeabilitytoretrievethemostrelevant
[http://www.justcerts.com](http://www.justcerts.com)
Whatshouldyouincludeinthesolution?
A.acomputedcolumnthatmanuallycomparesvectorvalues
B.astandardnonclusteredindexontheFmbeddings (vector(1536))column
C.afull-textindexontheFmbeddings (vector (1536))column
D.avectorindexontheEmbedding* (vector (1536))column
[http://www.justcerts.com](http://www.justcerts.com)
Whatshouldyouincludeinthesolution?
A.acomputedcolumnthatmanuallycomparesvectorvalues
B.astandardnonclusteredindexontheFmbeddings (vector(1536))column
C.afull-textindexontheFmbeddings (vector (1536))column
D.avectorindexontheEmbedding* (vector (1536))column
Check the image below to see the right answer:Select the answer
1 correct answerThecorrectanswerisDbecausetherequirementistoenablesimilaritysearchoverembeddingvectors
andtominimizelatency.MicrosoftdocumentsthatCREATEVECTORINDEXisspecificallyusedtocreate
anindexonvectordataforapproximatenearestneighbor(ANN)search,whichisdesignedtoaccelerate
vectorsimilarityqueriescomparedtoexactk-nearest-neighborscans.
Thismatchesthescenarioexactly.TheVehicleHealthSummarytablealreadyincludesanEmbeddings
(vector(1536))column.InMicrosoftSQLplatforms,embeddingsarestoredinvectorcolumnsand
queriedforsemanticsimilarity.Toimproveperformanceandreduceresponsetime,Microsoft
recommendsavectorindex,notaregularB-treenonclusteredindexandnotafull-textindex.Avector
indexispurpose-builtforfindingthemostsimilarvectorsefficiently.
Theotheroptionsarenotappropriate:
Awouldrequiremanualcomparisonlogicandwouldincreaselatencyratherthanminimizeit.
Bisincorrectbecauseastandardnonclusteredindexisnottheindextypeusedforvectorsimilarity
operations.
Cisincorrectbecausefull-textindexesarefortextualtoken-basedsearch,notnumericvector
embeddings.
Microsoft’scurrentdocumentationisexplicitthatvectorindexessupportapproximatenearestneighbor
search,andthattheoptimizercanusetheANNindexautomaticallyforvectorqueries.Thatistheexam-
aligneddesignchoicewhenthegoalisfastretrievalofthemostrelevanthealthsummaryreportsfrom
anembeddingscolumn.
andtominimizelatency.MicrosoftdocumentsthatCREATEVECTORINDEXisspecificallyusedtocreate
anindexonvectordataforapproximatenearestneighbor(ANN)search,whichisdesignedtoaccelerate
vectorsimilarityqueriescomparedtoexactk-nearest-neighborscans.
Thismatchesthescenarioexactly.TheVehicleHealthSummarytablealreadyincludesanEmbeddings
(vector(1536))column.InMicrosoftSQLplatforms,embeddingsarestoredinvectorcolumnsand
queriedforsemanticsimilarity.Toimproveperformanceandreduceresponsetime,Microsoft
recommendsavectorindex,notaregularB-treenonclusteredindexandnotafull-textindex.Avector
indexispurpose-builtforfindingthemostsimilarvectorsefficiently.
Theotheroptionsarenotappropriate:
Awouldrequiremanualcomparisonlogicandwouldincreaselatencyratherthanminimizeit.
Bisincorrectbecauseastandardnonclusteredindexisnottheindextypeusedforvectorsimilarity
operations.
Cisincorrectbecausefull-textindexesarefortextualtoken-basedsearch,notnumericvector
embeddings.
Microsoft’scurrentdocumentationisexplicitthatvectorindexessupportapproximatenearestneighbor
search,andthattheoptimizercanusetheANNindexautomaticallyforvectorqueries.Thatistheexam-
aligneddesignchoicewhenthegoalisfastretrievalofthemostrelevanthealthsummaryreportsfrom
anembeddingscolumn.
Quiz
Question 8/108/10
Topic1,ContosoCaseStudy
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
ExistingEnvironment
ContosohasanAzuresubscriptioninNorthEuropethatcontainsthecorporateinfrastructure.The
currentinfrastructurecontainsaMicrosoftSQLServer2017database.Thedatabasecontainsthe
followingtables.
[http://www.justcerts.com](http://www.justcerts.com)
TheFeedbackJsoncolumnhasafull-textindexandstoresJSONdocumentsinthefollowingformat.
ThesupportstaffatContosoneverhastheunmaskpermission.
[http://www.justcerts.com](http://www.justcerts.com)
ContosoisdeployinganewAzureSQLdatabasethatwillbecometheauthoritativedatastoreforthe
following;
• Alworkloads
• Vectorsearch
• ModernizedAPIaccess
• RetrievalAugmentedGeneration(RAG)pipelines
SometimestheingestionpipelinefailsduetomalformedJSONandduplicatepayloads.
TheengineersatContosoreportthatthefollowingdashboardqueryrunsslowly.
SELECTVehicleTd, Lastupdatedutc, EngineStatus, BatteryHealthFROMdbo.VehicleHealthSumarywhere
fleetld-gFleetldORDER BV LastUpdatedUtc DESC;
Youreviewtheexecutionplananddiscoverthattheplanshowsaclusteredindexscan.
vehicleincidentReportsoftencontainsdetailsabouttheweather,trafficconditions,andlocation.
Analystsreportthatitisdifficulttofindsimilarincidentsbasedonthesedetails.
PlannedChanges
ContosowantstomodernizeFleetIntelligencePlatformtosupportAl-poweredsemanticsearchover
incidentreports.
SecurityRequirements
Contosoidentifiesthefollowingtelemetryrequirements:
• Telemetrydatamustbestoredinapartitionedtable.
• Telemetrydatamustprovidepredictableperformanceforingestionandretentionoperations.
• latitude,longitude,andaccuracyJSONpropertiesmustbefilteredbyusinganindexseek.
Contosoidentifiesthefollowingmaintenancedatarequirements:
• EnsurethatanychangestoarowintheMaintenanceEventstableupdatesthecorrespondingvaluein
theLastModifreducecolumntothetimeofthechange.
• Avoidrecursiveupdates.
AISearch,Embedding’s,andVectorindexing
[http://www.justcerts.com](http://www.justcerts.com)
retrievelivemetadatafromthedatabases.Contosoidentifiesthefollowingrequirementsforquerying
dataintheFeedbackJsoncolumnofthecustomer-Feedbacktable:
• ExtractthecustomerfeedbacktextfromtheJSONdocument.
• FilterrowswheretheJSONtextcontainsakeyword.
• Calculateafuzzysimilarityscorebetweenthefeedbacktextandaknownissuedescription.
• Ordertheresultsbysimilarityscore,withthehighestscorefirst.
Youneedtocreateatableinthedatabasetostorethetelemetrydata.YouhavethefollowingTransact-
[http://www.justcerts.com](http://www.justcerts.com)
[http://www.justcerts.com](http://www.justcerts.com)
Check the image below to see the right answer:Select the answer
1 correct answer[http://www.justcerts.com](http://www.justcerts.com)
ThefirststatementisNo.Therequirementsaystelemetrydatamustbestoredinapartitionedtableto
providepredictableperformanceforingestionandretentionoperations.However,theshownCREATE
TABLEstatementdoesnotdefineapartitionfunctionorpartitionscheme,andthetableiscreatedwitha
regularclusteredprimarykeyonTelemetryId.Microsoft’spartitioningguidancestatesthatcreatinga
partitionedtablerequiresapartitionfunction,apartitionscheme,andcreatingthetableorindexonthat
partitionschemeusingapartitioningcolumn.Noneofthatappearsinthecode,sothetableisnot
partitioned.
ThesecondstatementisYes.ThecodecreatesaJSONindexnamedJI_VehicleTelemetry_Locationon
LocationJsonforthesespecificJSONpaths:$.location.latitude,$.location.longitude,and
$.location.accuracy.ThatmatchestherequirementthatthoseJSONpropertiesmustbefilterableby
usinganindexseek.MicrosoftdocumentsthatJSONindexingisusedtooptimizefilteringandsortingon
JSONproperties,andtheindexonlyhelpsforthepropertiesincludedintheindexdefinition.
ThethirdstatementisNo.TheJSONindexisdefinedonlyforlatitude,longitude,andaccuracy.Aquery
filteringon$.location.headingreferencesadifferentpaththatisnotincludedintheindexdefinition,so
thatquerywouldnotuseJI_VehicleTelemetry_Locationforthatpredicate.JSONindexesarepath-
specific;theydonotautomaticallycoverunrelatedpropertiesinthesameJSONdocument.
ThefirststatementisNo.Therequirementsaystelemetrydatamustbestoredinapartitionedtableto
providepredictableperformanceforingestionandretentionoperations.However,theshownCREATE
TABLEstatementdoesnotdefineapartitionfunctionorpartitionscheme,andthetableiscreatedwitha
regularclusteredprimarykeyonTelemetryId.Microsoft’spartitioningguidancestatesthatcreatinga
partitionedtablerequiresapartitionfunction,apartitionscheme,andcreatingthetableorindexonthat
partitionschemeusingapartitioningcolumn.Noneofthatappearsinthecode,sothetableisnot
partitioned.
ThesecondstatementisYes.ThecodecreatesaJSONindexnamedJI_VehicleTelemetry_Locationon
LocationJsonforthesespecificJSONpaths:$.location.latitude,$.location.longitude,and
$.location.accuracy.ThatmatchestherequirementthatthoseJSONpropertiesmustbefilterableby
usinganindexseek.MicrosoftdocumentsthatJSONindexingisusedtooptimizefilteringandsortingon
JSONproperties,andtheindexonlyhelpsforthepropertiesincludedintheindexdefinition.
ThethirdstatementisNo.TheJSONindexisdefinedonlyforlatitude,longitude,andaccuracy.Aquery
filteringon$.location.headingreferencesadifferentpaththatisnotincludedintheindexdefinition,so
thatquerywouldnotuseJI_VehicleTelemetry_Locationforthatpredicate.JSONindexesarepath-
specific;theydonotautomaticallycoverunrelatedpropertiesinthesameJSONdocument.
Quiz
Question 9/109/10
Topic2,FabrikamCase
Overview
Todisplaythefirstquestioninthiscasestudy,clicktheNextbutton.Usethebuttonsintheleftpaneto
explorethecontentofthecasestudybeforeyouanswerthequestions.Clickingthesebuttonsdisplays
informationsuchasbusinessrequirements,existingenvironmentandproblemstatements.Ifthecase
studyhasanAllInformationtab,notethattheinformationdisplayedisidenticaltotheinformation
displayedonthesubsequenttabs.Whenyouarereadytoansweraquestion,clicktheQuestionbutton
toreturntothequestion.
ExistingEnvironment
[http://www.justcerts.com](http://www.justcerts.com)
AzureEnvironment
-
FabrikamhasasingleAzuresubscriptionintheEastUS2Azureregion.Thesubscriptioncontainsan
AzureSQLdatabasenamedDB1.DB1containsthefollowingtables:
•Patients
•Employees
•Procedures
•Transactions
•UsefulPrompts
•ProcedureDocuments
YoustoreacolumnmasterkeyasasecretinAzureKeyVault.
Youhaveanon-premisesapplicationnamedTransactionProcessingthatusesahard-codedusername
andpasswordinaconnectionstringtoaccessDB1.
ProblemStatements
-
Usersreportthatafterexecutingalong-runningstoredprocedurenamed
sp_UpdateProcedureForPatient,updatestotheunderlyingdataaresometimesinconsistent.
Requirements
-
PlannedChanges
-
FabrikamplanstomanageallchangestoAzureSQLDatabaseobjectsbyusingsourcecontrolinGitHub.
Everypullrequestsubmittedtoproductionwillbevalidatedbeforeitcanbemerged.Deploymentsmust
usetheReleaseconfiguration.
SecurityRequirements
[http://www.justcerts.com](http://www.justcerts.com)
Fabrikamidentifiesthefollowingsecurityrequirements:
•TheTransactionProcessingapplicationmustuseapasswordlessconnectiontoDB1.
•TheEmployeestablecontainstwocolumnsnamedTaxIDandSalarythatmustbeencryptedatrest.
•Auditorsmusthaveatamper-evidenthistoryoftransactionswithcryptographicproofofchangestothe
employeedata.
DatabasePerformanceRequirements
Recordsaccessedbyusingsp_UpdateProcedureForPatientmustNOTbechangedbyothertransactions
whilethestoredprocedureruns.
AISearch,Embeddings,andVectorIndexing
FabrikamidentifiesthefollowingAI-relatedrequirements:
•QueriestotheProcedureDocumentstablemustuseReciprocalRankFusion(RRF).
•UsersmustbeabletoquerythedatainDB1byusingpromptsinCopilotinMicrosoftFabric.
•TheUsefulPromptstablewillstorepromptsthatdoctorscanusetohelpdiagnosepatientillnessby
connectingtoanAzureOpenAIendpoint.
DevelopmentRequirements
-
Fabrikamidentifiesthefollowingdevelopmentrequirements:
•Providethefunctionalitytoretrieveallthetransactionsofagivenpatientbetweentwodates,showing
arunningtotal.
•ExposeaDataAPIbuilder(DAB)configurationfiletoenableAzureservicestoperformthefollowing
operationsoveraRESTAPI:
oReaddatafromtheprocedurestablewithoutauthentication.
oReadandinsertdataintotheTransactionstableonceauthenticated.
oExecutethesp_UpdateProcedurePatientstoredprocedure.
•Providethefunctionalitytoretrievealistofthenamesofpatientswhounderwentmedicalprocedures
duringthelast30days.
•Informationforeachmedicalprocedurewillbestoredinatable.Thetablewillbeusedwithalarge
languagemodel(LLM)foruserqueryingandwillhavethefollowingstructure.
[http://www.justcerts.com](http://www.justcerts.com)
DAB
Youcreatea
DABconfigurationfilethatmeetsthedevelopmentrequirementsforDB1andincludesthe
followingentities.
[http://www.justcerts.com](http://www.justcerts.com)
Youneedtocreateasolutionthatmeetsthedevelopmentrequirementsforretrievingthepatientlists.
HowshouldyoucompletetheTransact-SQLcode?Toanswer,selecttheappropriateoptions
intheanswerarea.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Answer:
Explanation:
whereexists2.wherep.PatientID=pr.PatientIDOtherwisewefilterPatientIDtwice.Havinginthe
subquerydoesnotworkwithoutgroupbyandthedatefilterdoesnotworkwithoutwhere.
[http://www.justcerts.com](http://www.justcerts.com)
Overview
Todisplaythefirstquestioninthiscasestudy,clicktheNextbutton.Usethebuttonsintheleftpaneto
explorethecontentofthecasestudybeforeyouanswerthequestions.Clickingthesebuttonsdisplays
informationsuchasbusinessrequirements,existingenvironmentandproblemstatements.Ifthecase
studyhasanAllInformationtab,notethattheinformationdisplayedisidenticaltotheinformation
displayedonthesubsequenttabs.Whenyouarereadytoansweraquestion,clicktheQuestionbutton
toreturntothequestion.
ExistingEnvironment
[http://www.justcerts.com](http://www.justcerts.com)
AzureEnvironment
-
FabrikamhasasingleAzuresubscriptionintheEastUS2Azureregion.Thesubscriptioncontainsan
AzureSQLdatabasenamedDB1.DB1containsthefollowingtables:
•Patients
•Employees
•Procedures
•Transactions
•UsefulPrompts
•ProcedureDocuments
YoustoreacolumnmasterkeyasasecretinAzureKeyVault.
Youhaveanon-premisesapplicationnamedTransactionProcessingthatusesahard-codedusername
andpasswordinaconnectionstringtoaccessDB1.
ProblemStatements
-
Usersreportthatafterexecutingalong-runningstoredprocedurenamed
sp_UpdateProcedureForPatient,updatestotheunderlyingdataaresometimesinconsistent.
Requirements
-
PlannedChanges
-
FabrikamplanstomanageallchangestoAzureSQLDatabaseobjectsbyusingsourcecontrolinGitHub.
Everypullrequestsubmittedtoproductionwillbevalidatedbeforeitcanbemerged.Deploymentsmust
usetheReleaseconfiguration.
SecurityRequirements
[http://www.justcerts.com](http://www.justcerts.com)
Fabrikamidentifiesthefollowingsecurityrequirements:
•TheTransactionProcessingapplicationmustuseapasswordlessconnectiontoDB1.
•TheEmployeestablecontainstwocolumnsnamedTaxIDandSalarythatmustbeencryptedatrest.
•Auditorsmusthaveatamper-evidenthistoryoftransactionswithcryptographicproofofchangestothe
employeedata.
DatabasePerformanceRequirements
Recordsaccessedbyusingsp_UpdateProcedureForPatientmustNOTbechangedbyothertransactions
whilethestoredprocedureruns.
AISearch,Embeddings,andVectorIndexing
FabrikamidentifiesthefollowingAI-relatedrequirements:
•QueriestotheProcedureDocumentstablemustuseReciprocalRankFusion(RRF).
•UsersmustbeabletoquerythedatainDB1byusingpromptsinCopilotinMicrosoftFabric.
•TheUsefulPromptstablewillstorepromptsthatdoctorscanusetohelpdiagnosepatientillnessby
connectingtoanAzureOpenAIendpoint.
DevelopmentRequirements
-
Fabrikamidentifiesthefollowingdevelopmentrequirements:
•Providethefunctionalitytoretrieveallthetransactionsofagivenpatientbetweentwodates,showing
arunningtotal.
•ExposeaDataAPIbuilder(DAB)configurationfiletoenableAzureservicestoperformthefollowing
operationsoveraRESTAPI:
oReaddatafromtheprocedurestablewithoutauthentication.
oReadandinsertdataintotheTransactionstableonceauthenticated.
oExecutethesp_UpdateProcedurePatientstoredprocedure.
•Providethefunctionalitytoretrievealistofthenamesofpatientswhounderwentmedicalprocedures
duringthelast30days.
•Informationforeachmedicalprocedurewillbestoredinatable.Thetablewillbeusedwithalarge
languagemodel(LLM)foruserqueryingandwillhavethefollowingstructure.
[http://www.justcerts.com](http://www.justcerts.com)
DAB
Youcreatea
DABconfigurationfilethatmeetsthedevelopmentrequirementsforDB1andincludesthe
followingentities.
[http://www.justcerts.com](http://www.justcerts.com)
Youneedtocreateasolutionthatmeetsthedevelopmentrequirementsforretrievingthepatientlists.
HowshouldyoucompletetheTransact-SQLcode?Toanswer,selecttheappropriateoptions
intheanswerarea.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Answer:
Explanation:
whereexists2.wherep.PatientID=pr.PatientIDOtherwisewefilterPatientIDtwice.Havinginthe
subquerydoesnotworkwithoutgroupbyandthedatefilterdoesnotworkwithoutwhere.
[http://www.justcerts.com](http://www.justcerts.com)
DRAGDROP
YouhaveanAzureSQLdatabasenamedSalesDBthatcontainstablesnamedSales.Ordersand
Sales.OrderLines.Bothtablescontainsalesdata
YouhaveaRetrievalAugmentedGeneration(RAG)servicethatqueriesSalesDBtoretrieveorderdetails
andpassestheresultstoalargelanguagemodel(ILM)asJSONtext.Thefollowingisasampleofthe
JSON.
Youneedtoreturnone1SONdocumentperorderthatincludestheorderheaderfieldsandanarrayof
relatedorderlines.TheLIMmustreceiveasingleJSONarrayoforders,whereeachordercontainsalines
propertythatisaJSONarrayoflineItems.
Whichtransact-SQLcommandsshouldyouusetoproducetherequiredJSONshapefromtherelational
tables?Toanswer,dragtheappropriatecommandstothecorrectoperations.Eachcommandmaybe
[http://www.justcerts.com](http://www.justcerts.com)
viewcontent.
NOTE:Eachcorrectselectionisworthonepoint.
YouhaveanAzureSQLdatabasenamedSalesDBthatcontainstablesnamedSales.Ordersand
Sales.OrderLines.Bothtablescontainsalesdata
YouhaveaRetrievalAugmentedGeneration(RAG)servicethatqueriesSalesDBtoretrieveorderdetails
andpassestheresultstoalargelanguagemodel(ILM)asJSONtext.Thefollowingisasampleofthe
JSON.
Youneedtoreturnone1SONdocumentperorderthatincludestheorderheaderfieldsandanarrayof
relatedorderlines.TheLIMmustreceiveasingleJSONarrayoforders,whereeachordercontainsalines
propertythatisaJSONarrayoflineItems.
Whichtransact-SQLcommandsshouldyouusetoproducetherequiredJSONshapefromtherelational
tables?Toanswer,dragtheappropriatecommandstothecorrectoperations.Eachcommandmaybe
[http://www.justcerts.com](http://www.justcerts.com)
viewcontent.
NOTE:Eachcorrectselectionisworthonepoint.
Check the image below to see the right answer:Select the answer
1 correct answerSerializetheorder-levelJSON:FORJSONPATH
Generateanestedlinesarray:JSON_QUERY
ExtractasinglescalarvaluefromtheJSONtext:JSON_VALUE
ThecorrectmappingisbasedonhowSQLServerandAzureSQLJSONfunctionsaredesignedtoshape
relationaldataintoJSONforAIandRAGscenarios.
Toserializetheorder-levelJSON,useFORJSONPATH.MicrosoftdocumentsthatFORJSONPATHgives
youfullcontrolovertheJSONoutputshapeandformatstheresultasanarrayofJSONobjects.Itisthe
standardwaytoturnrelationalqueryresultsintotheJSONstructureneededbydownstreamconsumers
suchasAPIsandLLM-basedRAGservices.Italsosupportsnestedoutputthroughsubqueriesandaliases.
Togenerateanestedlinesarray,useJSON_QUERY.MicrosoftexplainsthatJSON_QUERYreturnsaJSON
objectorarrayfromJSONtext,anditisusedwhenyouwanttopreserveaJSONfragmentinsteadof
treatingitasplaintext.Inthisscenario,thenestedlinespropertymustbeemittedasaproperJSON
arrayinsideeachorderdocument,soJSON_QUERYisthecorrectcommandtoembedthatarrayinthe
finalJSONshape.
ToextractasinglescalarvaluefromtheJSONtext,useJSON_VALUE.Microsoftexplicitlystatesthat
JSON_VALUEextractsascalarvaluefromaJSONstring,whileJSON_QUERYisforobjectsorarrays.So
whenevertherequirementistopulloutonepropertysuchasanordernumber,currencycode,or
customerIDfromJSONtext,JSON_VALUEisthecorrectfunction.
Theunusedcommandsarenotthebestfithere:
[http://www.justcerts.com](http://www.justcerts.com)
nestedoutput.
JSON_MODIFYisforupdatingJSONtext,notgeneratingtherequiredoutputstructure.
Sothedrag-and-dropanswersare:
Serializetheorder-levelJSON→FORJSONPATH
Generateanestedlinesarray→JSON_QUERY
ExtractasinglescalarvaluefromtheJSONtext→JSON_VALUE
Generateanestedlinesarray:JSON_QUERY
ExtractasinglescalarvaluefromtheJSONtext:JSON_VALUE
ThecorrectmappingisbasedonhowSQLServerandAzureSQLJSONfunctionsaredesignedtoshape
relationaldataintoJSONforAIandRAGscenarios.
Toserializetheorder-levelJSON,useFORJSONPATH.MicrosoftdocumentsthatFORJSONPATHgives
youfullcontrolovertheJSONoutputshapeandformatstheresultasanarrayofJSONobjects.Itisthe
standardwaytoturnrelationalqueryresultsintotheJSONstructureneededbydownstreamconsumers
suchasAPIsandLLM-basedRAGservices.Italsosupportsnestedoutputthroughsubqueriesandaliases.
Togenerateanestedlinesarray,useJSON_QUERY.MicrosoftexplainsthatJSON_QUERYreturnsaJSON
objectorarrayfromJSONtext,anditisusedwhenyouwanttopreserveaJSONfragmentinsteadof
treatingitasplaintext.Inthisscenario,thenestedlinespropertymustbeemittedasaproperJSON
arrayinsideeachorderdocument,soJSON_QUERYisthecorrectcommandtoembedthatarrayinthe
finalJSONshape.
ToextractasinglescalarvaluefromtheJSONtext,useJSON_VALUE.Microsoftexplicitlystatesthat
JSON_VALUEextractsascalarvaluefromaJSONstring,whileJSON_QUERYisforobjectsorarrays.So
whenevertherequirementistopulloutonepropertysuchasanordernumber,currencycode,or
customerIDfromJSONtext,JSON_VALUEisthecorrectfunction.
Theunusedcommandsarenotthebestfithere:
[http://www.justcerts.com](http://www.justcerts.com)
nestedoutput.
JSON_MODIFYisforupdatingJSONtext,notgeneratingtherequiredoutputstructure.
Sothedrag-and-dropanswersare:
Serializetheorder-levelJSON→FORJSONPATH
Generateanestedlinesarray→JSON_QUERY
ExtractasinglescalarvaluefromtheJSONtext→JSON_VALUE
Quiz
Question 10/1010/10
Topic2,FabrikamCase
Overview
Todisplaythefirstquestioninthiscasestudy,clicktheNextbutton.Usethebuttonsintheleftpaneto
explorethecontentofthecasestudybeforeyouanswerthequestions.Clickingthesebuttonsdisplays
informationsuchasbusinessrequirements,existingenvironmentandproblemstatements.Ifthecase
studyhasanAllInformationtab,notethattheinformationdisplayedisidenticaltotheinformation
displayedonthesubsequenttabs.Whenyouarereadytoansweraquestion,clicktheQuestionbutton
toreturntothequestion.
ExistingEnvironment
[http://www.justcerts.com](http://www.justcerts.com)
AzureEnvironment
-
FabrikamhasasingleAzuresubscriptionintheEastUS2Azureregion.Thesubscriptioncontainsan
AzureSQLdatabasenamedDB1.DB1containsthefollowingtables:
•Patients
•Employees
•Procedures
•Transactions
•UsefulPrompts
•ProcedureDocuments
YoustoreacolumnmasterkeyasasecretinAzureKeyVault.
Youhaveanon-premisesapplicationnamedTransactionProcessingthatusesahard-codedusername
andpasswordinaconnectionstringtoaccessDB1.
ProblemStatements
-
Usersreportthatafterexecutingalong-runningstoredprocedurenamed
sp_UpdateProcedureForPatient,updatestotheunderlyingdataaresometimesinconsistent.
Requirements
-
PlannedChanges
-
FabrikamplanstomanageallchangestoAzureSQLDatabaseobjectsbyusingsourcecontrolinGitHub.
Everypullrequestsubmittedtoproductionwillbevalidatedbeforeitcanbemerged.Deploymentsmust
usetheReleaseconfiguration.
SecurityRequirements
[http://www.justcerts.com](http://www.justcerts.com)
Fabrikamidentifiesthefollowingsecurityrequirements:
•TheTransactionProcessingapplicationmustuseapasswordlessconnectiontoDB1.
•TheEmployeestablecontainstwocolumnsnamedTaxIDandSalarythatmustbeencryptedatrest.
•Auditorsmusthaveatamper-evidenthistoryoftransactionswithcryptographicproofofchangestothe
employeedata.
DatabasePerformanceRequirements
Recordsaccessedbyusingsp_UpdateProcedureForPatientmustNOTbechangedbyothertransactions
whilethestoredprocedureruns.
AISearch,Embeddings,andVectorIndexing
FabrikamidentifiesthefollowingAI-relatedrequirements:
•QueriestotheProcedureDocumentstablemustuseReciprocalRankFusion(RRF).
•UsersmustbeabletoquerythedatainDB1byusingpromptsinCopilotinMicrosoftFabric.
•TheUsefulPromptstablewillstorepromptsthatdoctorscanusetohelpdiagnosepatientillnessby
connectingtoanAzureOpenAIendpoint.
DevelopmentRequirements
-
Fabrikamidentifiesthefollowingdevelopmentrequirements:
•Providethefunctionalitytoretrieveallthetransactionsofagivenpatientbetweentwodates,showing
arunningtotal.
•ExposeaDataAPIbuilder(DAB)configurationfiletoenableAzureservicestoperformthefollowing
operationsoveraRESTAPI:
oReaddatafromtheprocedurestablewithoutauthentication.
oReadandinsertdataintotheTransactionstableonceauthenticated.
oExecutethesp_UpdateProcedurePatientstoredprocedure.
•Providethefunctionalitytoretrievealistofthenamesofpatientswhounderwentmedicalprocedures
duringthelast30days.
•Informationforeachmedicalprocedurewillbestoredinatable.Thetablewillbeusedwithalarge
languagemodel(LLM)foruserqueryingandwillhavethefollowingstructure.
[http://www.justcerts.com](http://www.justcerts.com)
DAB
Youcreatea
DABconfigurationfilethatmeetsthedevelopmentrequirementsforDB1andincludesthe
followingentities.
[http://www.justcerts.com](http://www.justcerts.com)
Youneedtocreateasolutionthatmeetsthedevelopmentrequirementsforretrievingthepatientlists.
HowshouldyoucompletetheTransact-SQLcode?Toanswer,selecttheappropriateoptions
intheanswerarea.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Answer:
Explanation:
whereexists2.wherep.PatientID=pr.PatientIDOtherwisewefilterPatientIDtwice.Havinginthe
subquerydoesnotworkwithoutgroupbyandthedatefilterdoesnotworkwithoutwhere.
[http://www.justcerts.com](http://www.justcerts.com)
Overview
Todisplaythefirstquestioninthiscasestudy,clicktheNextbutton.Usethebuttonsintheleftpaneto
explorethecontentofthecasestudybeforeyouanswerthequestions.Clickingthesebuttonsdisplays
informationsuchasbusinessrequirements,existingenvironmentandproblemstatements.Ifthecase
studyhasanAllInformationtab,notethattheinformationdisplayedisidenticaltotheinformation
displayedonthesubsequenttabs.Whenyouarereadytoansweraquestion,clicktheQuestionbutton
toreturntothequestion.
ExistingEnvironment
[http://www.justcerts.com](http://www.justcerts.com)
AzureEnvironment
-
FabrikamhasasingleAzuresubscriptionintheEastUS2Azureregion.Thesubscriptioncontainsan
AzureSQLdatabasenamedDB1.DB1containsthefollowingtables:
•Patients
•Employees
•Procedures
•Transactions
•UsefulPrompts
•ProcedureDocuments
YoustoreacolumnmasterkeyasasecretinAzureKeyVault.
Youhaveanon-premisesapplicationnamedTransactionProcessingthatusesahard-codedusername
andpasswordinaconnectionstringtoaccessDB1.
ProblemStatements
-
Usersreportthatafterexecutingalong-runningstoredprocedurenamed
sp_UpdateProcedureForPatient,updatestotheunderlyingdataaresometimesinconsistent.
Requirements
-
PlannedChanges
-
FabrikamplanstomanageallchangestoAzureSQLDatabaseobjectsbyusingsourcecontrolinGitHub.
Everypullrequestsubmittedtoproductionwillbevalidatedbeforeitcanbemerged.Deploymentsmust
usetheReleaseconfiguration.
SecurityRequirements
[http://www.justcerts.com](http://www.justcerts.com)
Fabrikamidentifiesthefollowingsecurityrequirements:
•TheTransactionProcessingapplicationmustuseapasswordlessconnectiontoDB1.
•TheEmployeestablecontainstwocolumnsnamedTaxIDandSalarythatmustbeencryptedatrest.
•Auditorsmusthaveatamper-evidenthistoryoftransactionswithcryptographicproofofchangestothe
employeedata.
DatabasePerformanceRequirements
Recordsaccessedbyusingsp_UpdateProcedureForPatientmustNOTbechangedbyothertransactions
whilethestoredprocedureruns.
AISearch,Embeddings,andVectorIndexing
FabrikamidentifiesthefollowingAI-relatedrequirements:
•QueriestotheProcedureDocumentstablemustuseReciprocalRankFusion(RRF).
•UsersmustbeabletoquerythedatainDB1byusingpromptsinCopilotinMicrosoftFabric.
•TheUsefulPromptstablewillstorepromptsthatdoctorscanusetohelpdiagnosepatientillnessby
connectingtoanAzureOpenAIendpoint.
DevelopmentRequirements
-
Fabrikamidentifiesthefollowingdevelopmentrequirements:
•Providethefunctionalitytoretrieveallthetransactionsofagivenpatientbetweentwodates,showing
arunningtotal.
•ExposeaDataAPIbuilder(DAB)configurationfiletoenableAzureservicestoperformthefollowing
operationsoveraRESTAPI:
oReaddatafromtheprocedurestablewithoutauthentication.
oReadandinsertdataintotheTransactionstableonceauthenticated.
oExecutethesp_UpdateProcedurePatientstoredprocedure.
•Providethefunctionalitytoretrievealistofthenamesofpatientswhounderwentmedicalprocedures
duringthelast30days.
•Informationforeachmedicalprocedurewillbestoredinatable.Thetablewillbeusedwithalarge
languagemodel(LLM)foruserqueryingandwillhavethefollowingstructure.
[http://www.justcerts.com](http://www.justcerts.com)
DAB
Youcreatea
DABconfigurationfilethatmeetsthedevelopmentrequirementsforDB1andincludesthe
followingentities.
[http://www.justcerts.com](http://www.justcerts.com)
Youneedtocreateasolutionthatmeetsthedevelopmentrequirementsforretrievingthepatientlists.
HowshouldyoucompletetheTransact-SQLcode?Toanswer,selecttheappropriateoptions
intheanswerarea.
NOTE:Eachcorrectselectionisworthonepoint.
[http://www.justcerts.com](http://www.justcerts.com)
Answer:
Explanation:
whereexists2.wherep.PatientID=pr.PatientIDOtherwisewefilterPatientIDtwice.Havinginthe
subquerydoesnotworkwithoutgroupbyandthedatefilterdoesnotworkwithoutwhere.
[http://www.justcerts.com](http://www.justcerts.com)
YouhaveanAzureSQLtablethatcontainsthefollowingdata.
Youneedtoretrievedatatobeusedascontextforalargelanguagemodel(LLM).Thesolutionmust
minimizetokenusage.
WhichformalshouldyouusetosendthedatatotheLLM?
A)
B)
[http://www.justcerts.com](http://www.justcerts.com)
C)
D)
Youneedtoretrievedatatobeusedascontextforalargelanguagemodel(LLM).Thesolutionmust
minimizetokenusage.
WhichformalshouldyouusetosendthedatatotheLLM?
A)
B)
[http://www.justcerts.com](http://www.justcerts.com)
C)
D)
Select the answer:Select the answer
1 correct answerA.
OptionA
B.
OptionB
C.
OptionC
D.
OptionD
Answr 1:
A. [http://www.justcerts.com](http://www.justcerts.com)
avoidinganunnecessaryfieldthatwouldaddtokenswithoutimprovinganswerquality.
ForLLMgroundingandRAG-stylecontext,Microsoftguidanceemphasizesmappingandsendingthe
fieldsthatcontaintextpertinenttotheusecase.InthisFAQscenario,theusefulcontextisthe
ProductName,theQuestion,andtheAnswer.Thosethreefieldshelpthemodelunderstandboththe
subjectdomainandtheactualQ&Apair.Bycontrast,FaqIdisjustatechnicalidentifierandgenerally
addsnosemanticvalueforresponsegeneration,soincludingitwastestokens.
ThatiswhyOptionAisbetterthantheothers:
OptionAkeepsthemeaningfultextfieldsandremovesthelow-valueidentifier.
OptionBistoominimalbecauseitincludesonlytheanswertextasPrompt,whichstripsawaythe
productandquestioncontexttheLLMmayneedforaccurategrounding.
OptionCkeepsFaqIdbutomitsProductName,whichcanbeimportantdisambiguatingcontext.
OptionDincludeseverything,butthatdoesnotminimizetokenusagebecauseitkeepstheunnecessary
FaqId.
Answr 1:
A. [http://www.justcerts.com](http://www.justcerts.com)
avoidinganunnecessaryfieldthatwouldaddtokenswithoutimprovinganswerquality.
ForLLMgroundingandRAG-stylecontext,Microsoftguidanceemphasizesmappingandsendingthe
fieldsthatcontaintextpertinenttotheusecase.InthisFAQscenario,theusefulcontextisthe
ProductName,theQuestion,andtheAnswer.Thosethreefieldshelpthemodelunderstandboththe
subjectdomainandtheactualQ&Apair.Bycontrast,FaqIdisjustatechnicalidentifierandgenerally
addsnosemanticvalueforresponsegeneration,soincludingitwastestokens.
ThatiswhyOptionAisbetterthantheothers:
OptionAkeepsthemeaningfultextfieldsandremovesthelow-valueidentifier.
OptionBistoominimalbecauseitincludesonlytheanswertextasPrompt,whichstripsawaythe
productandquestioncontexttheLLMmayneedforaccurategrounding.
OptionCkeepsFaqIdbutomitsProductName,whichcanbeimportantdisambiguatingcontext.
OptionDincludeseverything,butthatdoesnotminimizetokenusagebecauseitkeepstheunnecessary
FaqId.
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