Microsoft Developing AI-Enabled Database Solutions (DP-800)

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

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

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

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

Audit of the Correct Answer

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

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

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

Why other options are incorrect:

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

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

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

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

Follow the first directed edge k1 to p2,

Then follow the second directed edge k2 to p3.

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

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

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

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

Explanation of Choices

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

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

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

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

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

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

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

Why the other choices are incorrect:

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

✅

Option B (Correct)

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

❌ Why the other choices are incorrect:

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

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

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