Review: designing and developing database solutions

The design and develop group is the one Hana knows best, because she has just built the Finch Rewards database. That is also its danger. A developer who has made one set of choices for one app can forget that the exam asks about every option on the list, including ones she rejected. This review goes area by area through the March 12, 2026 outline and, for each skill, gives the decision the exam is likely to test and the detail that most often separates the right answer from a near miss.

The group has four areas: database objects, programmability objects, advanced T-SQL, and SQL solutions built with AI-assisted tools. Together they are worth 35 to 40 parts in 100 of the exam.

Tables, data types and indexes

Questions about tables usually turn on fit: the smallest data type that holds the values, the right key and the right index for the workload. Use int or bigint for surrogate keys depending on growth; Finch Rewards uses bigint for PointsTransaction because 30 million rows growing by 1.1 million a month will pass the int limit of about 2.1 billion only after many decades, but a busier table could pass it much sooner. Use nvarchar for text that can contain any language, and size it to the data: an email address is limited to 254 characters by the standards, so nvarchar(254) is enough. Avoid nvarchar(max) for columns that need to be indexed, because max columns cannot be index keys.

For indexes, the exam expects the usual rules. A clustered index defines the table's storage order and there can be only one. Nonclustered indexes support lookups and can include extra columns to cover a query. A columnstore index stores data by column and compresses it heavily; it suits large scans and aggregations, such as monthly points totals across 30 million rows, but not single-row lookups. A table can have a clustered columnstore index as its main storage, or a nonclustered columnstore index alongside a rowstore table to support analytics on operational data.

Specialized tables

The outline names five specialized table types. Each solves a specific problem, and questions often describe the problem without naming the table.

A typical question: a finance team must be able to prove that no historical points transaction has been altered, and transactions are never updated after insert. The answer is an append-only ledger table, not a temporal table. Temporal tables record history but do not prove it was not tampered with. Ledger tables use cryptographic hashes to make tampering detectable, and an append-only ledger table blocks updates and deletes. In MSAZ-300 Hana built rewards.PointsJournal this way for exactly that reason, because Malcolm Reyes in finance wanted evidence, not just history.

JSON columns and indexes

SQL Server 2025, Azure SQL Database, SQL database in Fabric and Managed Instance with the right update policy support a native json data type. It stores JSON in a binary format, checks that the value is valid JSON, and supports values up to 2 GB. Before the type existed, JSON was stored in nvarchar columns with an ISJSON check constraint, and that pattern still appears in older systems and in some answer options.

A JSON index can speed up queries that filter on paths inside a json column, and a computed column over JSON_VALUE with an ordinary index is the older technique that still works. A json column cannot itself be an index key. The exam's design question is usually about when to use JSON at all. Values that are queried, joined or constrained often belong in real columns; flexible, rarely queried attributes such as notification preferences are a good fit for JSON. Hana moved members' preferred stores out of JSON into a child table for exactly this reason: they were joined to the Store table in every store-finder query.

CREATE TABLE rewards.MemberProfile (
    MemberId    bigint NOT NULL PRIMARY KEY,
    Preferences json NULL,
    PrefLanguage AS JSON_VALUE(Preferences, '$.language')
);
CREATE INDEX IX_MemberProfile_PrefLanguage ON rewards.MemberProfile (PrefLanguage);

Constraints, sequences and partitioning

Constraints are a frequent source of easy marks and careless losses. PRIMARY KEY and UNIQUE both enforce uniqueness, but a table has only one primary key, and a UNIQUE constraint allows one NULL in SQL Server. FOREIGN KEY constraints can cascade deletes or updates; a question that says 'when a member is deleted, their preferences must also be removed' points to ON DELETE CASCADE. CHECK constraints test a condition on each row. DEFAULT supplies a value when none is given.

A SEQUENCE is a separate object that hands out numbers, unlike an IDENTITY property, which belongs to one column of one table. Use a sequence when numbers must be shared across tables, obtained before an insert, or restarted or cycled. Finch Rewards uses a sequence for card numbers starting at 600000001 because the app needs the number before writing the row. Sequences can have gaps, for example after a rollback or a restart with caching, so they are not suitable when an auditor needs every number to be used.

Partitioning splits a table or index into ranges by a partition function and maps them to storage with a partition scheme. It helps manage very large tables, for example by switching out an old month in a single metadata operation. It does not automatically make queries faster; it helps only when queries filter on the partition column so that partitions can be eliminated.

Programmability objects

Views, scalar functions, table-valued functions, stored procedures and triggers are classic T-SQL, and the questions are about choosing between them. A view is a saved SELECT. An inline table-valued function is like a view with parameters and is usually the best-performing kind of function, because the optimizer expands it into the calling query. A multi-statement table-valued function fills a table variable and is harder for the optimizer to estimate. A scalar function returns one value; since SQL Server 2019, many simple scalar functions can be inlined, which removes much of their old performance cost.

A stored procedure can change data, return results, use transactions and handle errors; a function cannot change data in tables. A trigger runs automatically on an insert, update or delete. Triggers suit audit trails, such as Finch Rewards' MemberEmailHistory, but they add work to every change and are easy to forget about. The exam often prefers a constraint over a trigger when a constraint can do the job, and prefers asynchronous change handling over a trigger when the follow-up work is slow, such as calling an AI model.

Advanced T-SQL

This area lists the most functions by name. Expect questions that show a short query and ask what it returns, or that give a requirement and ask which function meets it. Common table expressions and window functions are long-standing features: ROW_NUMBER, RANK and DENSE_RANK differ in how they treat ties, LAG and LEAD read neighboring rows, and SUM with an OVER clause can produce a running total.

The JSON functions named are JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, JSON_CONTAINS, OPENJSON and JSON_VALUE. JSON_VALUE extracts a scalar value from a path; JSON_QUERY, not on the list but often in options, extracts an object or array. OPENJSON turns JSON into rows. JSON_OBJECT and JSON_ARRAY build JSON from values, and JSON_ARRAYAGG builds an array by aggregating rows, which is useful when sending results to a language model. JSON_CONTAINS tests whether a JSON value contains a given value at a path.

The regular expression functions are REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE. REGEXP_LIKE returns true or false and is used in WHERE clauses and CHECK constraints; the last two return tables. The implementation uses the RE2 pattern syntax, ignores collation, and has a 2 MB limit on the input string. The fuzzy matching functions, EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY and JARO_WINKLER_DISTANCE, measure how alike two strings are, for example to find likely duplicate member names. In SQL Server 2025 they are a preview and require PREVIEW_FEATURES.

Graph queries use MATCH in the WHERE clause to follow edges, for example MATCH(p1-(r)->p2) for 'p1 referred p2', and SHORTEST_PATH to follow chains of any length. Correlated subqueries refer to the outer query and run once per outer row in concept; EXISTS with a correlated subquery is often the clearest way to express 'members who have redeemed at least one offer'. Error handling uses TRY...CATCH, THROW to raise or re-raise an error, and XACT_STATE() to decide whether to commit or roll back after an error. A procedure that might be called inside another transaction should check @@TRANCOUNT before beginning its own.

AI-assisted tools

This area is new for most database developers. It covers enabling GitHub Copilot and Copilot in Fabric, choosing the model and the Model Context Protocol (MCP) tools in a chat session, writing Copilot instruction files, connecting to MCP server endpoints for SQL Server and Fabric lakehouses, and understanding the security impact of all of this. An MCP server exposes tools, such as 'run a query' or 'list tables', that an AI assistant can call. An instruction file, such as .github/copilot-instructions.md or a path-specific file under .github/instructions, tells Copilot the conventions of a repository: naming rules, which schemas to use, and which patterns to avoid.

The security questions are the most important. An MCP tool that runs queries does so with the permissions of the identity it connects as. If that identity can read production data, the assistant can too, and anything it reads can end up in a prompt. The safe pattern, which Hana used in MSAZ-300, is to connect MCP tools only to a development database with synthetic data, using a least-privileged identity, and to review every change the assistant proposes before it runs. A question that offers 'connect the MCP server to production with an administrator account so Copilot has full context' is offering a wrong answer.