Design choices - JSON, graph tables and partitioning
AI features sit on top of normal schema design. The exam checks whether you pick the right structure for the data shape and the query pattern.
JSON in the database
SQL Server 2025 and Azure SQL support a native json data type, in addition to JSON stored in nvarchar. Useful functions:
| Function | Purpose |
|---|---|
| JSON_VALUE | Extract a scalar value from a path |
| JSON_QUERY | Extract an object or array |
| OPENJSON | Turn JSON into rows and columns |
| JSON_OBJECT / JSON_ARRAY | Build JSON from columns |
| ISJSON / JSON_MODIFY | Validate or change JSON |
To filter on a property quickly, add a computed column on JSON_VALUE(Payload, '$.customer_id') and index it. Newer versions also support a JSON index on the column.
Use JSON for variable or nested data. Use normal columns for data you join, constrain or filter all the time.
Graph tables
Graph features store many-to-many relationships as node and edge tables.
CREATE TABLE Person (PersonID int, DisplayName nvarchar(100)) AS NODE;
CREATE TABLE Knows AS EDGE;
SELECT p3.PersonID, p3.DisplayName
FROM Person p1, Knows k1, Person p2, Knows k2, Person p3
WHERE MATCH(p1-(k1)->p2-(k2)->p3)
AND p1.PersonID = @StartPersonId;The arrow direction in MATCH matters. SHORTEST_PATH finds shortest routes with repeated patterns. Choose graph over recursive CTEs when relationship depth varies and traversal is the main workload.
Partitioning
A partition function defines boundaries and a partition scheme maps them to filegroups. Partition large tables by a date column that queries filter on.
Benefits:
- Switch a partition in or out almost instantly (
ALTER TABLE ... SWITCH). - Rebuild or compress one partition (
ALTER INDEX ... REBUILD PARTITION = n) instead of the whole table. - Partition elimination reduces reads when the query filters on the partition key.
The partition column must be part of a unique key or clustered index key on a partitioned table.
Choosing index types
| Need | Index |
|---|---|
| Point lookups, ranges | Rowstore B-tree (clustered or nonclustered) |
| Large scans and aggregates | Clustered or nonclustered columnstore |
| Nearest-neighbour on embeddings | Vector index |
| Word and phrase search | Full-text index |
Exam traps
- Partitioning is a maintenance and manageability tool first. It does not always speed up queries.
- JSON path queries are not indexed automatically. Add a computed column or JSON index.
- A vector index is not a replacement for full-text. Hybrid search uses both.
- Columnstore is poor for single-row lookups.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. Semi-structured event payloads vary by source and you want to index one property for fast filtering. What is a good design?
Show answer
Answer: B.Keep the payload in a JSON column and expose hot properties through a computed column (JSON_VALUE) or a JSON index so filters can use an index.
2. You must find people reachable through exactly two Knows relationships. Which feature models this most naturally?
Show answer
Answer: A.Graph tables (AS NODE and AS EDGE) with MATCH express multi-hop relationships, including fixed or variable length paths.
3. A table of orders keeps growing by year, and old years must be switched out quickly with little rebuild time. What do you use?
Show answer
Answer: A.Partitioning lets you switch, rebuild or compress one partition at a time, which keeps maintenance windows short.
4. Which index type suits analytic scans and aggregations over a large fact table?
Show answer
Answer: A.Columnstore indexes compress data by column and are built for scans and aggregates. Rowstore B-tree indexes suit point lookups.