Memory hook: Constraints protect truth; queries express intent.
Must remember
- Choose data types and sizes from actual values and access patterns. Primary/foreign/unique/check constraints protect integrity; defaults supply omitted values but do not validate every business rule.
- Rowstore indexes support selective access; columnstore favors analytical scans; partitioning improves manageability and can enable elimination when predicates align. It is not automatic faster execution for every query.
- Temporal tables preserve system-versioned history; ledger adds tamper-evidence; graph models relationships; external tables reference external data; in-memory tables target supported memory-optimized workloads. Check platform/version support.
- Views encapsulate queries; stored procedures package operations; scalar/table-valued functions compute reusable results; triggers run from specified events and can introduce hidden side effects. Sequences generate values independently of one table’s identity column.
- Use CTEs for readable query composition and window functions for calculations without collapsing every row. Correlated subqueries depend on outer rows; inspect plans rather than assuming a fixed performance rule.
- JSON functions parse/build structured payloads; supported native JSON/index features vary by platform. Regex, fuzzy matching and graph MATCH solve different search problems. Use TRY/CATCH and appropriate transaction handling for failures.
- Function recall: JSON_VALUE extracts a scalar; JSON_QUERY returns an object/array fragment; OPENJSON exposes a rowset for relational processing. JSON_OBJECT/JSON_ARRAY construct values, while supported JSON aggregate functions combine values across rows. REGEXP_LIKE tests a pattern; EDIT_DISTANCE-style functions compare string similarity; graph MATCH follows a graph pattern. Match the function to the data shape and check engine/version support before using newer functions.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Track historical row versions automatically | A supported temporal-table design. |
| Aggregate over rows while retaining row detail | Window functions with an explicit partition/order/frame. |
Traps
- A CTE is not automatically a materialized cache.
- Ledger provides tamper evidence, not permission-free access or a backup.
Active recall
1. What does a foreign key enforce?
Referential integrity between related keys according to its configured rules.
2. When use a columnstore index?
For suitable scan/aggregation-heavy analytical workloads.
3. Why specify a window frame?
Defaults may produce different running or peer-group calculations than intended.
4. What can a trigger complicate?
Transaction duration, hidden writes, recursion and troubleshooting.
5. Why validate platform support?
SQL Server, Azure SQL and Fabric do not expose every feature/version identically.