DP-800: Designing SQL Databases for Intelligent Applications

The first design mistake in an AI-enabled database project often happens before anyone chooses a model. A team stores a customer’s entire transaction history in a flexible document column to speed up the prototype. Six months later, it cannot enforce basic relationships, update one line item reliably, or explain why account-specific searches scan too much data. The database’s fundamental shape determines how well the application can handle both ordinary transactions and advanced retrieval. The DP-800 exam tests this foundation through SQL objects, programmability, specialized storage, indexing, and design decisions across supported Microsoft SQL platforms.

DP-800’s database-design domain includes tables, data types, keys, constraints, indexes, columnstore, JSON columns, temporal and ledger tables, graph structures, partitioning, sequences, views, functions, stored procedures, triggers, and advanced T-SQL. These are not interchangeable features. The relevant skill is deciding which structure protects a business invariant or query requirement and what cost it adds to writes, maintenance, and recovery. The following design problem—an equipment-service platform that will later support natural-language search—provides a useful way to make those choices concrete.

Identify the system of record and its invariants

Start with a service order that references a customer, a machine, assigned technicians, replacement parts, and a sequence of status changes. Some fields are authoritative: every service order belongs to an existing customer, a replacement part has a defined identifier, and the number of units billed cannot be negative. Other fields may be descriptive or flexible: fault notes, photographs, and manufacturer-specific attributes differ widely between equipment types. Before designing tables, mark which facts demand relational integrity, which need historical versions, and which will be searched conversationally.

A primary key guarantees unique identification; a foreign key helps preserve relationships; UNIQUE, CHECK, and DEFAULT constraints enforce other supported rules. These should not be deferred to an AI application’s prompt. A model cannot reliably prevent two conflicting writes or ensure a deleted customer record never leaves orphaned service orders. Choose precise data types and lengths, consider null semantics, and explain how identifiers are generated. A sequence can support certain numbering requirements, but not every business number should double as a security-sensitive token or encode an ordering guarantee the underlying process cannot maintain.

Balance normalization, JSON flexibility, and access patterns

Relational normalization improves consistency when the same fact appears in many transactions. But applications sometimes need to store semi-structured attributes whose shape varies by supplier or device model. JSON columns may be a sensible adjunct for bounded optional properties. They are a poor excuse to abandon schema design for fields that drive referential integrity, frequent filtering, or critical financial calculations. Decide where the boundary lies by examining actual update and query patterns. If technicians regularly filter by a machine’s warranty category, model or index that access appropriately instead of extracting it from large documents repeatedly without a plan.

DP-800 includes JSON constructors, readers, aggregators, and indexes available in supported engines. A candidate should know the difference between retrieving one JSON property, projecting a nested array into rows, and returning structured JSON from a query. Those operations can simplify application integration, but their usefulness depends on schema, version, and execution plans. Test with realistic payload sizes and malformed or missing attributes. The aim is not to convert an SQL database into an unstructured blob store; it is to combine strong relational business rules with flexible representation where the workload justifies it.

Pick specialized table features for specific requirements

Temporal tables can help preserve queryable data history under supported patterns, which is valuable when a service-order status changes and auditors need to reconstruct earlier values. Ledger capabilities address tamper-evidence requirements rather than merely retaining old values. In-memory tables, external tables, and graph constructs have their own prerequisites and tradeoffs; a graph relationship might suit certain traversals but is not inherently superior to a conventional join. Columnstore structures can benefit analytical scan patterns, while ordinary row-oriented indexes often serve selective transactional lookups well. These distinctions are more important than remembering a product-feature checklist.

Suppose the service platform needs to prove which contract rate applied when a job was accepted. Historical data preservation may be relevant, but a temporal feature should not substitute for an explicit business rule about legally effective rates or an appropriate audit trail. If the same database also supports nightly reporting across millions of service lines, evaluate whether a specialized index or separate analytical workload is justified. That is different from the broader orchestration and analytics decisions found in DP-700 data engineering. DP-800’s concern is how the application database keeps its data trustworthy while supporting its own operational queries.

Design indexes as write-and-read tradeoffs

Indexing is a response to observed access patterns, not a contest to create an index on every column. Each additional index can improve selected reads while increasing storage and write maintenance. A query that locates all open orders for one customer might benefit from an index reflecting its predicates and ordering. A query that scans the entire historical fleet for annual metrics may call for a different strategy. Use representative cardinality and data skew rather than validating only a tiny seed dataset. Test whether a convenient index also helps the critical production query, rather than assuming a familiar design is universally correct.

Partitioning can support manageability and certain performance patterns when data volume, access locality, and retention make it worthwhile. It also complicates design and does not automatically accelerate queries that ignore the partition key. Columnstore can be useful for analytics-oriented scans, but its fit depends on the engine, workload, and update profile. An interview-quality explanation states the bottleneck, the chosen structure, the expected benefit, and the effect on inserts, updates, and maintenance. DP-800 asks for that reasoning in scenarios rather than for blind endorsement of one storage technology.

Use views, functions, and procedures to express boundaries

Programmability objects can centralize stable operations and limit what consumers need to know about raw tables. A carefully designed view might expose the service fields required by a customer portal while hiding internal technician notes. A stored procedure might apply a multi-step update under a transaction, enforcing application rules in a predictable way. Table-valued and scalar functions can serve distinct expression needs, with performance implications depending on their form and usage. Triggers can enforce or react to particular operations, but hidden side effects may complicate debugging and high-volume writes if they are used carelessly.

Do not assume a database object is secure just because it is not a table. Permissions on views, procedures, and underlying objects must be evaluated in the actual execution context. A procedure that concatenates untrusted input into dynamic SQL creates a risk that encapsulation alone does not solve. Define parameter types, expected error conditions, and how transactions roll back on failure. The application should receive clear outcomes without learning secrets from database error messages. Understanding this layer also helps when exposing selected objects through REST or GraphQL endpoints later in the application’s lifecycle.

Build query logic that keeps historical and current facts straight

Window functions and common table expressions can answer questions such as ‘What was the most recent inspection for each machine?’ or ‘How did the repair duration compare with the preceding visit?’ Correlated queries may be appropriate in some designs, but their repeated work can become expensive depending on plan and indexing. A window expression can produce a concise ranked result without manually looping through every machine. JSON and regular-expression functions can help normalize messy incoming descriptions; fuzzy matching can identify candidate similar product names, though false matches must not be allowed to corrupt authoritative identifiers.

The difference between explanatory search and transactional truth matters when AI is added. A language model may infer that two part descriptions mean the same thing. That is not sufficient justification for merging their identifiers or modifying invoice lines. Keep fuzzy text matching in a review or retrieval workflow until a trusted business process validates the relationship. Structure data so that the assistant can provide useful suggestions without becoming an unreviewed author of critical records. The boundaries between AI application engineering and database integrity are especially important when model output is translated into SQL actions.

Plan for concurrency and changing requirements

Two technicians may edit the same work order from different sites. A correct single-user demo does not prove that concurrent updates will preserve intent. Think about transaction boundaries, isolation levels, locking, optimistic conflict detection where applicable, and the consequences of retries. A long-running transaction that holds locks while a model generates a paragraph would be an avoidable design flaw. AI inference should generally not force unrelated database work to wait inside unnecessary transactional scope. Protect the smallest coherent state change and allow slow external operations to follow an appropriate asynchronous pattern when the workflow permits.

Requirements will evolve. A database design must accept new equipment categories, new report fields, and a future similarity-search feature without breaking everyday service entry. Version schema changes; test migrations with representative data; define rollback or forward-recovery options; and avoid basing business invariants on features whose availability differs between SQL Server, Azure SQL, and Fabric SQL database. DP-800’s emphasis is precisely this engineering judgment: a database built for intelligent applications remains a database first. Robust schema, predictable queries, and controlled changes give AI capabilities a foundation worth trusting.