You have an Azure SQL database that contains a table named Rooms. Rooms was created by using the
following transact-SQL statement.  You discover that some records in the Rooms table contain NULL values for the Owner field. You need to ensure that all future records have a value for the Owner field. What should you add?
-
A
-
B
-
C
-
D
Reveal answer details
Close answer details
Correct answerB
ExplanationThe table definition allows Owner to be nullable because it was created as Owner nvarchar(100) without NOT NULL. Since the question asks what to add so that future rows must have a value, a check constraint such as CHECK (Owner IS NOT NULL) is the appropriate choice. Microsoft documents that check constraints validate future INSERT and UPDATE operations against the constraint condition.
The other options do not solve the requirement: A foreign key enforces referential integrity, not non-null entry by itself. A nonclustered index does not require values to be present. A unique constraint prevents duplicate values but still does not serve as the right mechanism here for enforcing presence across future writes. Microsoft's constraint documentation also notes that primary-key columns are implicitly NOT NULL, which helps distinguish nullability enforcement from other constraint types.
Case study
Case Study 1
Existing Environment Contoso has an Azure subscription in North Europe that contains the corporate infrastructure. The current infrastructure contains a Microsoft SQL Server 2017 database. The database contains the following tables.  The FeedbackJson column has a full-text index and stores JSON documents in the following format.  The support staff at Contoso never has the unmask permission. Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following; 1. Al workloads
2. Vector search
3. Modernized API access
4. Retrieval Augmented Generation (RAG) pipelines Sometimes the ingestion pipeline fails due to malformed JSON and duplicate payloads.
The engineers at Contoso report that the following dashboard query runs slowly.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary where
fleetld - gFleetld ORDER BV LastUpdatedUtc DESC; You review the execution plan and discover that the plan shows a clustered index scan. vehicleincidentReports often contains details about the weather, traffic conditions, and location. Analysts report that it is difficult to find similar incidents based on these details. Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over incident reports. Security Requirements
Contoso identifies the following telemetry requirements:
1. Telemetry data must be stored in a partitioned table.
2. Telemetry data must provide predictable performance for ingestion and retention operations.
3. latitude, longitude, and accuracy JSON properties must be filtered by using an index seek. Contoso identifies the following maintenance data requirements:
1. Ensure that any changes to a row in the MaintenanceEvents table updates the corresponding value in the LastModif reduce column to the time of the change.
2. Avoid recursive updates. AI Search, Embedding's, and Vector indexing
The development learn at Contoso will use Microsoft Visual Studio Code and GitHub Copilot and will retrieve live metadata from the databases. Contoso identifies the following requirements for querying data in the FeedbackJson column of the customer-Feedback table:
1. Extract the customer feedback text from the JSON document.
2. Filter rows where the JSON text contains a keyword.
3. Calculate a fuzzy similarity score between the feedback text and a known issue description.
4. Order the results by similarity score, with the highest score first.
Testlet 1
Hotspot
HOTSPOT You need to meet the development requirements for the FeedbackJson column How should you complete the Transact SQL query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. 
Reveal answer details
Close answer details
You deploy Data API builder (DAB) to Azure Container Apps. A secret named MSSQL_CONNECTION_STRING is exposed to the container as an environment variable.
You need DAB to read that value when initializing the configuration. Which command should you use?
-
A
dab init --database-type mssql --connection-string "secretref:MSSQL_CONNECTION_STRING"
-
B
dab init --database-type mssql --connection-string "@env('MSSQL_CONNECTION_STRING')"
-
C
dab init --database-type mssql --connection-string "@env('DAB_CONFIG_BASE64')"
-
D
dab init --database-type mssql --connection-string "@akv('MSSQL_CONNECTION_STRING')"
Reveal answer details
Close answer details
Correct answerB
ExplanationOption B is correct because DAB supports reading the connection string from an environment variable by using the @env(...) syntax during initialization and runtime configuration. Options A, C, and D are incorrect because secretref is a container-apps secret mapping concept rather than the DAB CLI connection-string syntax, DAB_CONFIG_BASE64 refers to the configuration payload rather than the SQL connection string, and @akv(...) is for Key Vault references instead of the environment variable provided in this scenario.
Case study
Case Study 1
Existing Environment Contoso has an Azure subscription in North Europe that contains the corporate infrastructure. The current infrastructure contains a Microsoft SQL Server 2017 database. The database contains the following tables. The FeedbackJson column has a full-text index and stores JSON documents in the following format. The support staff at Contoso never has the unmask permission. Requirements
Contoso is deploying a new Azure SQL database that will become the authoritative data store for the
following; 1. Al workloads
2. Vector search
3. Modernized API access
4. Retrieval Augmented Generation (RAG) pipelines Sometimes the ingestion pipeline fails due to malformed JSON and duplicate payloads.
The engineers at Contoso report that the following dashboard query runs slowly.
SELECT VehicleTd, Lastupdatedutc, EngineStatus, BatteryHealth FROM dbo.VehicleHealthSumary where
fleetld - gFleetld ORDER BV LastUpdatedUtc DESC; You review the execution plan and discover that the plan shows a clustered index scan. vehicleincidentReports often contains details about the weather, traffic conditions, and location. Analysts report that it is difficult to find similar incidents based on these details. Planned Changes
Contoso wants to modernize Fleet Intelligence Platform to support Al-powered semantic search over incident reports. Security Requirements
Contoso identifies the following telemetry requirements:
1. Telemetry data must be stored in a partitioned table.
2. Telemetry data must provide predictable performance for ingestion and retention operations.
3. latitude, longitude, and accuracy JSON properties must be filtered by using an index seek. Contoso identifies the following maintenance data requirements:
1. Ensure that any changes to a row in the MaintenanceEvents table updates the corresponding value in the LastModif reduce column to the time of the change.
2. Avoid recursive updates. AI Search, Embedding's, and Vector indexing
The development learn at Contoso will use Microsoft Visual Studio Code and GitHub Copilot and will retrieve live metadata from the databases. Contoso identifies the following requirements for querying data in the FeedbackJson column of the customer-Feedback table:
1. Extract the customer feedback text from the JSON document.
2. Filter rows where the JSON text contains a keyword.
3. Calculate a fuzzy similarity score between the feedback text and a known issue description.
4. Order the results by similarity score, with the highest score first.
Testlet 1
Single choice
You need to recommend a solution to lesolve the slow dashboard query issue. What should you recommend?
-
A
Create a clustered index on Lastupdatedutc.
-
B
On Fleetid, create a nonclustered index that includes Lastupdatedutc. inginestatus, and BatteryHealth.
-
C
On Lastupdatedutc. create a nonclustered index that includes Fleetid.
-
D
On Fleetid, create a filtered index where lastupdatedutc > DATEADD(DAV, -7,SYSuTCOATETIME()).
Reveal answer details
Close answer details
You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders, dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month. You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.orders. Solution:
Identify the partition number for the oldest month, and then run the following Transact-SQL statement.
TRUNCATE TABIE dbo.Orders WITH (PARTITIONS (partition number)); Does this meet the goal?
-
A
-
B
Reveal answer details
Close answer details
Correct answerA
ExplanationYes, this meets the goal. Microsoft documents that on a partitioned table, you can use TRUNCATE TABLE... WITH (PARTITIONS (...)) to remove data from a specific partition, and that this is an efficient maintenance operation that targets only that data subset rather than the whole table. Microsoft's partitioning guidance explicitly lists truncating a single partition as an example of a fast partition-level maintenance or retention operation. That matches the requirement to remove the oldest month while minimizing impact on other queries. Because the table is already partitioned by month on OrderDate, identifying the partition number for that oldest month and truncating only that partition is the correct low-impact approach, assuming the table and indexes are aligned as required for partition truncation.
You have an Azure SQL table that contains a column named NationalId with highly sensitive values. You must protect the stored values and query parameters so that repeated plaintext values do not produce repeated ciphertext values. Users must not be able to infer value patterns from the encrypted output. Which feature should you use?
-
A
Always Encrypted with deterministic encryption
-
B
-
C
Always Encrypted with randomized encryption
-
D
Reveal answer details
Close answer details
Correct answerC
ExplanationOption C is correct because randomized encryption produces different ciphertext for the same plaintext value, which prevents pattern analysis based on repeated encrypted values. Always Encrypted also keeps encryption and decryption on the client side rather than revealing plaintext to the database engine. Options
A, B, and D are incorrect because deterministic encryption always generates the same ciphertext for the same plaintext, Dynamic Data Masking is a presentation feature rather than encryption, and Row-Level Security controls row access instead of value confidentiality.
DRAG DROP You have a SQL database in Microsoft Fabric that contains a table named WebSite. Logs. WebSite.Logs
stores application telemetry data. Website.Logs contains a nvarehar(iMx) column named log that stores JSON documents. You have a daily report that filters by the $.severity JSON property and returns Logld. LogDateTime, and log. The report frequently causes full table scans. You need to modify Website. Logs to support efficient filtering by $. severity and avoid key lookups for the columns returned by the report. How should you complete the Transact-SQL code to avoid full table scans? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. 
Reveal answer details
Close answer details
You have a SQL database in Microsoft Fabric that contains a table named dbo.Orders, dbo.Orders has a clustered index, contains three years of data, and is partitioned by a column named OrderDate by month.
You need to remove all the rows for the oldest month. The solution must minimize the impact on other queries that access the data in dbo.orders. Solution:
Run the following Transact-SQL statement.
DELETE FROM dbo.Orders
WHERE OrderDate < DATEADD(nonth, -36, SYSUTCDATETIME()); Does this meet the goal?
-
A
-
B
Reveal answer details
Close answer details
Correct answerB
ExplanationThis does not meet the goal. A row-by-row DELETE against the oldest month is not the lowest-impact way to purge data from a monthly partitioned table. Microsoft's partitioning guidance specifically says partitioning lets you perform maintenance and retention operations more efficiently by targeting just the relevant partition, including the ability to truncate data in a single partition.
The proposed statement:
DELETE FROM dbo.Orders
WHERE OrderDate < DATEADD(month, -36, SYSUTCDATETIME());
would log row deletions and can hold locks longer, creating more overhead for other queries than a partition-level maintenance operation. Since the table is already partitioned by month, the expected low-impact approach is to operate on the oldest partition directly, not issue a broad delete predicate over rows.
Microsoft explicitly highlights partition-targeted truncation as a faster, more efficient retention operation than working against the whole table or rowset.
You have a GitHub Actions workflow that builds and deploys an Azure SQL database. The schema is stored in a GitHub repository as an SDK-style SQL database project. Following a code review, you discover that you need to generate a report that shows whether the production schema has diverged from the model in source control. Which action should you add to the pipeline?
-
A
SqlPackage.exe /Action:DriftReport
-
B
SqlPackage.exe /Action:DeployReport
-
C
SqlPackage.exe /Action:Extract
-
D
SqlPackage.exe /Action:Script
Reveal answer details
Close answer details
Correct answerA
ExplanationMicrosoft documents that DriftReport creates an XML report showing changes that have been made to the registered database since it was last registered. That is the action intended to detect whether the production schema has diverged from the expected model baseline in your deployment workflow. This is different from DeployReport, which shows the changes that would be made by a publish action. In other words: DriftReport answers: Has the deployed database drifted from the registered state/model?
DeployReport answers: What changes would be applied if I published now? The other options are not the right fit: Extract creates a DACPAC from an existing database, not a drift analysis report. Script generates a deployment script, not a schema-drift report. So to generate a report that shows whether production has diverged from the model in source control, add: SqlPackage.exe /Action:DriftReport
DRAG DROP You have an Azure SQL database that contains a table named dbo.orders, dbo.orders contains a column named createDate that stores order creation dates. You need to create a stored procedure that filters Orders by CreateDate for a single calendar day. The solution must be SARGable. How should you complete the Transact-SQL code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. 
Reveal answer details
Close answer details
Question 11
Single choice
You have an Azure SQL database.
That contains database-level Data Definition Language (DDL) triggers, including a trigger named ddl_Audit.
You need to prevent ddl_Audit from firing during the next deployment. The trigger object must remain in place. Which Transact-SQL statement should you use?
-
A
-
B
-
C
ALTER SERVER AUDIT SPECIFICATION
-
D
-
E
ALTER DATABASE AUDIT SPECIFICATION
Reveal answer details
Close answer details
Correct answerD
ExplanationThe requirement is very specific: prevent ddl_Audit from firing during the next deployment, but leave the trigger object in place. Microsoft documents DISABLE TRIGGER as the statement used to disable a trigger without dropping it. That is exactly the right operation for a temporary suspension of a DDL trigger. For a database-scoped DDL trigger, the syntax is on the database scope, for example DISABLE TRIGGER
ddl_Audit ON DATABASE;.
The other options do not meet the requirement as directly: ALTER TRIGGER changes the trigger definition, not simply disables execution. ALTER DATABASE is not the direct statement for disabling a specific DDL trigger. ALTER SERVER AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION are audit-feature statements, not trigger-control statements.
So the correct Transact-SQL statement is DISABLE TRIGGER.
DRAG DROP You have a Microsoft SQL Servei 2025 database that contains a table named dbo.Customer-Messages, dbo. Customer-Messages contains two columns named HessagelD (int) and MessageRaw (nvarchar(iux)).
MessageRaw can contain a phone number in multiple formats. and some rows do NOT contain a phone number. You need to write a single SELECT query that meets the following requirements: - The query must return Message ID, RawNumber. DigitsOnly, and PhoneStatus.
- RawNumber must contain the first substring that matches a phone-number pattern, or NULL if no match
exists.
- DigitsOnly must remove all non-digit characters from RawNumber. or return NULL.
- PhoneStatus must return Valid when a phone number exists in MessageRaw. otherwise return Missing. How should you complete the Transact-SQL query? To answer, drag the appropriate values To the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point. 
Reveal answer details
Close answer details
HOTSPOT You have a SQL database in Microsoft Fabric named Sales BD that contains a table named dbo.Products. You need to modify SalesBD to meet the following requirements: - Create a vector index on the appropriate column.
- Use a supplied natural language query vector. How should you complete the Transact-SQL code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. 
Reveal answer details
Close answer details
DRAG DROP You have an Azure SQL database that supports an OLTP application. You need to write Transact-SQL code that returns blocking chain details. The output must return only sessions that ate blocked or are blocking other sessions. How should you complete the code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content NOTE: Each correct selection is worth one point. 
Reveal answer details
Close answer details
|
