Reading T-SQL in questions

Walt writes T-SQL every day, but he writes it with SQL Server Management Studio's autocomplete, his binder of scripts and the error messages that tell him when he is wrong. On the exam, T-SQL appears as a printed listing with no help: a statement to complete with drop-down lists, a script to put in order, or a query whose result must be predicted. Elena's view is that reading T-SQL under time pressure is a separate skill from writing it, and that it can be practiced. This lesson collects the statement families that appear most often in DP-300 items and a method for reading them.

A method for reading a listing

Read the whole listing once without stopping, to see what it is for: creating something, granting something, changing a setting, or querying a view. Then identify the statement's verb and its object: CREATE USER, GRANT on a schema, ALTER DATABASE SCOPED CONFIGURATION, CREATE INDEX. Then read the options and clauses, the parts after WITH or inside parentheses, because the drop-down lists usually sit there. Finally, check the scope: does the statement run in master or in the user database, on the server or in one database, on Azure SQL Database or on a managed instance? A statement that is correct on one platform may fail on another.

Identity and permissions

Security statements are the most common T-SQL in the exam. The ones to recognize on sight are shown below. The question is usually which word belongs in a gap, or which statement grants the least privilege.

-- In the user database: a user for a Microsoft Entra group or managed identity
CREATE USER [sg-stockline-readers] FROM EXTERNAL PROVIDER;
CREATE USER [app-finchrewards] FROM EXTERNAL PROVIDER;

-- A role with exactly the rights the API needs
CREATE ROLE api_caller;
GRANT EXECUTE ON SCHEMA::api TO api_caller;
ALTER ROLE api_caller ADD MEMBER [app-finchrewards];

-- Read access to one schema, and an explicit block on one table
GRANT SELECT ON SCHEMA::Sales TO [sg-stockline-readers];
DENY SELECT ON OBJECT::Sales.CardTokens TO [sg-stockline-readers];

Three details carry marks. FROM EXTERNAL PROVIDER means the identity comes from Microsoft Entra ID; WITH PASSWORD would make a contained SQL user instead, and WITHOUT LOGIN makes a user that cannot connect at all, used for impersonation and signing. ON SCHEMA:: with two colons is the class qualifier; leaving it out would make SQL Server look for an object called Sales. And DENY on a single table beats the schema-level GRANT, so the readers can see every Sales table except CardTokens.

Checking effective permissions is another frequent item. SELECT * FROM fn_my_permissions(NULL, 'DATABASE') shows the current user's permissions in the database, and EXECUTE AS USER followed by REVERT lets an administrator test as another user. sys.database_role_members joined to sys.database_principals lists role memberships.

Encryption, masking and row-level security

Column definitions with Always Encrypted and dynamic data masking appear as hot areas, with the encryption type or the mask function hidden.

CREATE TABLE Rewards.Members (
    MemberId     int IDENTITY PRIMARY KEY,
    Email        nvarchar(256) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    Phone        varchar(20)   MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)') NULL,
    CardToken    char(16) COLLATE Latin1_General_BIN2
                 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Rewards,
                                 ENCRYPTION_TYPE = DETERMINISTIC,
                                 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    StoreId      int NOT NULL
);

The details to notice: deterministic encryption on a character column requires a BIN2 collation, which is a common trap in hot areas; the algorithm name is fixed and appears exactly as shown; the partial mask takes the number of characters to show at the start, the padding string and the number to show at the end, so partial(0, "XXX-XXX-", 4) shows only the last four digits. Masks are bypassed for users with the UNMASK permission.

CREATE FUNCTION Security.fn_store_filter(@StoreId int)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok
   WHERE @StoreId = CAST(SESSION_CONTEXT(N'StoreId') AS int)
      OR IS_MEMBER('head_office') = 1;

CREATE SECURITY POLICY Security.StorePolicy
    ADD FILTER PREDICATE Security.fn_store_filter(StoreId) ON Rewards.Members,
    ADD BLOCK PREDICATE Security.fn_store_filter(StoreId) ON Rewards.Members AFTER INSERT
    WITH (STATE = ON);

A row-level security predicate is an inline table-valued function, created WITH SCHEMABINDING, that returns a row when access is allowed. The filter predicate hides rows from reads; the block predicate, here AFTER INSERT, stops a store user inserting a row for another store. SESSION_CONTEXT is a common way for the application to tell the database which store the session belongs to.

Performance settings and Query Store

Settings that apply to one database use ALTER DATABASE SCOPED CONFIGURATION, which is the way to set MAXDOP, turn on or off parameter sniffing, or use the legacy cardinality estimator in Azure SQL Database. Instance settings use sp_configure on Managed Instance and VMs, and are not available for most options in Azure SQL Database. Query Store options use ALTER DATABASE ... SET QUERY_STORE. Automatic tuning uses ALTER DATABASE ... SET AUTOMATIC_TUNING. Compatibility level uses ALTER DATABASE ... SET COMPATIBILITY_LEVEL.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
ALTER DATABASE CURRENT SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;
EXEC sys.sp_query_store_force_plan @query_id = 412, @plan_id = 7;

Index statements test the INCLUDE and WHERE clauses. A covering index puts the columns used for seeking in the key and the columns only returned in INCLUDE; a filtered index adds a WHERE clause; an online rebuild on Enterprise-level platforms uses WITH (ONLINE = ON), and a resumable rebuild adds RESUMABLE = ON, which can be paused and resumed.

CREATE INDEX IX_Sales_Store_Date ON Sales.SaleLine (StoreId, SaleTime)
    INCLUDE (Sku, Quantity, Amount)
    WITH (ONLINE = ON);

CREATE INDEX IX_Orders_Open ON Sales.CustomerOrder (DueDate)
    WHERE Status = 'Open';

ALTER INDEX IX_Sales_Store_Date ON Sales.SaleLine REBUILD
    WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

Backup, restore and HA statements

On SQL Server VMs and for copy-only backups on Managed Instance, backup and restore statements appear with URL targets. Credentials for URL access use either a shared access signature, with the credential named after the container URL, or a managed identity on recent versions. On Managed Instance, user backups must be COPY_ONLY, because the service manages the backup chain. A STOPAT clause on the last RESTORE LOG gives a point in time; NORECOVERY on all but the last restore keeps the database ready for more.

CREATE CREDENTIAL [https://hfbackups.blob.core.windows.net/sql]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<SAS token>';

BACKUP DATABASE StockLine TO URL = 'https://hfbackups.blob.core.windows.net/sql/StockLine_copy.bak'
    WITH COPY_ONLY, COMPRESSION, CHECKSUM;

Predicting what a statement does

Some items show a short script and ask what happens, or what a user can see afterwards. These test precise knowledge of precedence and scope. Three rules settle most of them. First, DENY beats GRANT, whether the grant is on the object, the schema or the database, and whether it comes directly or through a role, except for members of db_owner and sysadmin, who bypass permission checks. Second, a permission granted on a higher scope covers objects created later inside it, so a schema grant includes tables added next month, while an object grant does not. Third, ownership chaining lets a procedure or view read the tables it uses without the caller having permission on them, as long as the procedure and the tables have the same owner, which is usually the schema's owner.

Walt practices with a set of ten small scripts that Elena wrote, each followed by the question 'Can user X now do Y?'. Example: a user is a member of a role granted SELECT on schema Sales; the user is then denied SELECT on Sales.CardTokens directly; the role is later granted SELECT on the whole database. Can the user read Sales.CardTokens? No: the direct DENY on the table still wins over both grants. The scripts take a minute each and build exactly the kind of quick, certain reasoning that hot areas need.

Ordering scripts

Build-a-list questions sometimes give the statements of a task out of order. The order is usually set by dependencies: an object must exist before it is granted on, a key must exist before a column is encrypted with it, a function must exist before a security policy uses it, and a credential must exist before a backup to URL. For Always Encrypted, the order is the column master key in Key Vault, then CREATE COLUMN MASTER KEY in the database to record where it is, then CREATE COLUMN ENCRYPTION KEY protected by it, then the column definition that uses the encryption key. For a customer-managed TDE protector, the order is the key in Key Vault with soft delete and purge protection, then access for the server identity, then setting the key as the server's TDE protector.

Reading DMV queries

Monitoring questions show a query against dynamic management views and ask what it returns or which column answers a question. The habit that helps is to read the FROM clause first, because the view's name says what is being measured, then the WHERE clause, which says what is being filtered, then the ORDER BY, which says what the query is looking for. sys.dm_exec_requests is about what is running now; sys.dm_exec_query_stats is about cached plans since they were compiled; sys.dm_db_index_usage_stats is about how indexes were used since the last restart; sys.dm_db_resource_stats is about resource use in the last hour. A query ordered by total_worker_time descending is looking for CPU-heavy queries, one ordered by total_logical_reads descending for IO-heavy ones.

Platform checks

A statement can be perfect T-SQL and still be the wrong answer because the platform does not support it. On Azure SQL Database, USE to switch databases does not work across databases, so scripts connect to each database; cross-database queries need elastic query or are not available; SQL Server Agent stored procedures in msdb do not exist; and many sp_configure options are not available. On Managed Instance, BACKUP needs COPY_ONLY and TO URL. On a VM, everything works as on premises, including Windows authentication. When a hot area offers a statement that is correct in general but impossible on the stated platform, it is a distractor.

Elena gives Walt a last tip for this lesson: when two drop-down values both look right, read the statement aloud with each in place. The wrong one usually sounds wrong to someone who writes T-SQL every day, which is a strength Walt should use rather than second-guess.