Reading T-SQL in questions

DP-800 is a developer exam, and many of its questions contain T-SQL. Some show a complete statement and ask what it returns or whether it meets a goal. Some show a statement with gaps and ask the candidate to fill them from drop-down lists. Some show four statements that look alike and ask which one is correct. Hana writes T-SQL every day, but reading someone else's code under time pressure is a different skill. In the diagnostic she lost most of her time on the three longest code questions, and got one of them wrong because she missed a single keyword near the end.

This lesson teaches a reading method for code in questions, then applies it to the kinds of statements DP-800 is likely to show: JSON, regular expressions, window functions, vector search, permissions and REST calls. None of the listings run in this course; they are for reading, exactly as they would appear on the exam.

A reading method

Elena's method has five steps. First, read the goal sentence in the question and say in plain words what the result should be. Second, read the FROM clause and joins: which tables or functions supply the rows? Third, read the WHERE clause: which rows survive? Fourth, jump to the end and read ORDER BY, TOP, OFFSET and any WITH options, because these often decide the answer. Only then, fifth, read the SELECT list and the functions in it. This order follows the way the engine logically processes a query, and it puts the decisive details in front of the reader before the eye gets lost in long expressions.

For code with gaps, the same method applies with one addition: before choosing a value for any gap, read the whole statement. The first gap might be a function name, and the clue to it is in the last line. When comparing four similar statements, Hana should find the line where they differ and read only that line closely, rather than reading all four from top to bottom.

Small words that change everything

Many code questions turn on a single small word. A table of the most common ones is worth memorizing, because the exam setters know these are where readers slip.

Reading JSON queries

JSON questions often show OPENJSON with a WITH clause that maps paths to columns, or JSON_VALUE in a WHERE clause. The details to check are the path, whether the path is lax or strict, the data type in the WITH clause, and whether a value is a scalar (JSON_VALUE) or an object or array (JSON_QUERY, or AS JSON in OPENJSON). Building JSON is the other direction: FOR JSON PATH turns rows into JSON, and JSON_OBJECT, JSON_ARRAY and JSON_ARRAYAGG build JSON from values.

SELECT m.MemberId,
       JSON_OBJECT('tier': m.TierCode,
                   'offers': JSON_ARRAYAGG(o.Title)) AS context
FROM rewards.Member AS m
JOIN rewards.Redemption AS r ON r.MemberId = m.MemberId
JOIN rewards.Offer AS o ON o.OfferId = r.OfferId
WHERE m.MemberId = @MemberId
GROUP BY m.MemberId, m.TierCode;

Reading it with the method: the goal is one JSON document per member. FROM joins members to their redemptions and the offers. WHERE keeps one member. GROUP BY gives one row per member, which is what lets JSON_ARRAYAGG collect the offer titles into an array. The SELECT builds an object with the tier and that array. A wrong option might drop GROUP BY, which would make JSON_ARRAYAGG invalid alongside the non-aggregated columns, or use JSON_ARRAY, which builds an array from listed values rather than from rows.

Reading regular expressions

Regular expression questions usually ask whether a pattern matches given strings, or which function returns what is wanted. Keep three facts in mind: REGEXP_LIKE returns a boolean and is used in WHERE or CHECK; REGEXP_SUBSTR returns the matching text; REGEXP_COUNT returns the number of matches. An anchor at the start (^) and one at the end (written with the dollar sign) together mean the whole string must match, not just part of it. In a pattern, the dollar sign is an anchor, not currency. In the constraint below, 'SKU-48213' matches the pattern and 'Old SKU-48213' does not, because of the start anchor.

ALTER TABLE catalog.Product
ADD CONSTRAINT CK_Product_Sku CHECK (REGEXP_LIKE(Sku, '^SKU-[0-9]{5}$'));

Reading window functions

Window function questions ask what a column will contain. Read the OVER clause carefully: PARTITION BY sets the groups, ORDER BY sets the order within each group, and the frame (ROWS or RANGE BETWEEN...) sets which rows are included. Without an explicit frame, an aggregate with ORDER BY uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal ORDER BY values as one step. That is why a running total can jump by two rows at once when two transactions have the same timestamp. ROW_NUMBER gives 1, 2, 3 even for ties; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2.

Reading error-handling code

Error-handling questions show a procedure with TRY and CATCH blocks and ask what happens when something fails. Four details decide most of them. First, whether the transaction is begun inside the TRY block, so that the CATCH block can roll it back. Second, whether the CATCH block checks XACT_STATE(): a value of -1 means the transaction cannot be committed and must be rolled back; 1 means it is still committable; 0 means there is no transaction. Third, whether the error is re-raised with THROW, so the caller knows something failed, or swallowed, which hides the problem. Fourth, whether XACT_ABORT is on, which makes most runtime errors roll back the whole transaction automatically.

CREATE OR ALTER PROCEDURE rewards.uspRedeemOffer @MemberId bigint, @OfferId int
AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
            INSERT rewards.Redemption (MemberId, OfferId) VALUES (@MemberId, @OfferId);
            UPDATE rewards.MemberBalance SET Points -= (SELECT Cost FROM rewards.Offer WHERE OfferId = @OfferId)
            WHERE MemberId = @MemberId;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Reading it with the method: the goal is that a redemption and its balance change happen together or not at all. The transaction begins inside TRY. If the unique constraint on MemberId and OfferId rejects a second redemption, control passes to CATCH, which rolls back if a transaction is open and re-raises the original error with THROW, so the app sees the duplicate. An option that replaces THROW with a PRINT statement would leave the app believing the redemption worked, and an option that commits in the CATCH block would keep a half-finished change. Both are typical distractors.

A last point about error handling in questions: errors raised inside a function cannot be caught there, because functions cannot contain TRY and CATCH blocks, and a function cannot change data. If an option proposes catching and logging an error inside a scalar function, it is wrong for that reason alone. Logging belongs in the calling procedure's CATCH block, ideally after the rollback, so that the log row is not rolled back with the failed work. That ordering detail, rollback first and then log, is exactly the kind of thing a build-a-list question can test.

Reading vector queries

Vector questions often show either an exact query with VECTOR_DISTANCE in ORDER BY, or an approximate query with VECTOR_SEARCH. For VECTOR_SEARCH, check four things: that the TABLE is a base table (not a view); that the METRIC matches the vector index; that the query uses SELECT TOP (n) WITH APPROXIMATE in current syntax rather than the older TOP_N parameter; and that ORDER BY is on the distance column alone, ascending. Each of those is a rule the engine enforces, so an option that breaks one of them is wrong even if everything else looks fine.

For VECTOR_DISTANCE, check the metric and the direction. Smaller distances mean more similar, so ORDER BY distance ASC is right; DESC returns the least similar rows first. A dot-product result returned by VECTOR_DISTANCE is negated so that smaller still means closer, which is a detail that can appear as a distractor.

Reading permission and security code

Permission statements are short, but each word counts. GRANT EXECUTE ON SCHEMA::api TO app_role gives execute on every procedure in the api schema, including future ones. DENY SELECT ON rewards.Member TO app_role blocks reading the table directly, but does not stop reading through a view with an unbroken ownership chain, because permissions on the table are not checked in that case. CREATE USER [fn-finch-notify] FROM EXTERNAL PROVIDER creates a database user for a managed identity or Entra principal. When the question asks for least privilege, prefer the narrowest scope that still works: a single procedure over a schema, a schema over the database.

Reading REST calls

Calls to sp_invoke_external_rest_endpoint show a URL, a method, headers, a payload, a credential and an output parameter. Check that the method is POST for a chat or embeddings call; that the credential name matches the endpoint URL, as database scoped credentials for this procedure are named after the URL they serve; that the payload is valid JSON built with FOR JSON or JSON_OBJECT rather than string concatenation; and that the response is read from the output parameter with JSON_VALUE at the right path, such as the content of the first choice in a chat response.

DECLARE @response nvarchar(max), @payload nvarchar(max) =
    JSON_OBJECT('messages': JSON_ARRAY(
        JSON_OBJECT('role': 'system', 'content': 'Answer using only the offers provided.'),
        JSON_OBJECT('role': 'user', 'content': @question + ' Offers: ' + @offersJson)));

EXEC sp_invoke_external_rest_endpoint
     @url = N'https://finch-ai.openai.azure.com/openai/deployments/chat/chat/completions?api-version=2024-10-21',
     @method = 'POST',
     @credential = [https://finch-ai.openai.azure.com],
     @payload = @payload,
     @response = @response OUTPUT;

SELECT JSON_VALUE(@response, '$.result.choices[0].message.content') AS answer;

Read with the method: the goal is one text answer. The payload is built with JSON functions. The call uses POST and a credential named for the endpoint. The response wraps the model's reply in a result object, so the path starts with result. An option that reads from the top level, without result, would return NULL.