Microsoft SQL AI Developer Associate (DP-800) Practice Questions and Study Guide

Auditors ask what a customer's credit limit was on any past date. Every change to the Customers table must be kept automatically, with no extra code in the application. What should you implement?

• AAn AFTER UPDATE trigger that copies old rows into a separate log table
• BA system-versioned temporal table, queried with FOR SYSTEM_TIME AS OF
• CA nightly job that takes a full copy of the table into a history schema
• DA rowversion column that the application reads before every update

A software company keeps all customers in one database. Each row has a TenantId column. The app sets the tenant with sp_set_session_context after it connects. Users must never see other tenants rows, even with ad hoc queries. What should you implement?

• ADynamic Data Masking on the TenantId column, with UNMASK for admins
• BA separate view for each tenant, plus a DENY on the base table
• CAlways Encrypted, with a different column encryption key per tenant
• DRow-Level Security with a filter predicate that checks SESSION_CONTEXT

Shoppers search a catalog with queries such as "SKU 44-1093 waterproof hiking boots". Some terms are exact codes and some describe meaning. Which search approach gives the best results?

• AHybrid search that merges full-text and vector results with RRF
• BFull-text search only, with results ranked by the CONTAINSTABLE rank
• CVector search only, on embeddings of the full product description
• DA LIKE filter on the product name with wildcards on both sides of the text

Product attributes are stored as JSON documents in a json column. The busiest query filters with JSON_VALUE(Attributes, '$.color') = 'red' and is slow. What is the most direct way to speed it up?

• AConvert the column to nvarchar(max) and add a full-text index on it
• BAdd a nonclustered columnstore index on the json column
• CCreate a JSON index on the json column that covers the $.color path
• DMove the attributes into an XML column and add a primary XML index

A report must show each order with a running total of order amounts per customer, ordered by order date. Which approach gives this in one pass without a self-join?

• AGROUP BY CustomerID with SUM(Amount) and a HAVING clause
• BSUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS UNBOUNDED PRECEDING)
• CA correlated subquery in the SELECT list that sums all orders for the customer
• DROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate)