Memory hook: SQL truth first; AI output is still input.
Reviewed 10 October 2026. Read this once, then answer the last-pass checks without looking.
Scope/version: The published skills update starts 19 October 2026, after this review date. Check the outline for your booking date. This cross-platform SQL certification includes SQL Server, Azure SQL and SQL databases in Fabric; it is grouped with Azure for discovery.
Must remember by domain
| Domain | Rapid revision |
|---|---|
| Objects/modeling | Keys/constraints enforce integrity; defaults fill omitted values. Rowstore favors selective access; columnstore favors analytical scans; partitioning enables suitable management/elimination. Temporal tables track versions; ledger adds tamper evidence; graph models relationships; external and memory-optimized tables have separate support. Sequence values are not confined to one table. |
| Programmability/T-SQL | Views expose query definitions; procedures package operations; scalar/table functions compute reusable results; triggers introduce event-driven side effects. CTEs compose queries; window functions retain detail while calculating across ordered partitions. OPENJSON returns rowsets; JSON_VALUE extracts scalars. Regex, fuzzy distance and graph MATCH solve different matching needs. Use TRY/CATCH and explicit transaction recovery. |
| Security | Database roles/object permissions, RLS, audit, TLS and encryption address different layers. Always Encrypted protects selected columns with client-side key control; masking is not encryption. Secure REST/GraphQL/MCP/model endpoints separately. Passwordless managed identity still needs permissions. |
| Concurrency/performance | Read uncommitted allows dirty reads; read committed prevents dirty reads but can see later changes; snapshot uses row versions; serializable adds stronger range consistency with concurrency costs. Platform configuration controls row-versioning behavior. Inspect Query Store, plans, DMVs and waits to distinguish estimates, scans, blocking and deadlocks. |
| Delivery/assistance | SQL Database Projects model schema; validate build, deployment difference, drift, reference data and tests. Use reviewed branches/approvals and scoped pipeline identities. Copilot instructions/MCP can supply context and tools; generated SQL requires correctness, permission and performance review. Model output must never silently authorize mutation. |
| Integration | Data API builder maps entities/relationships/roles to supported REST/GraphQL. Configure paging/filter/cache and protect runtime credentials. CDC records supported changes; Change Tracking identifies changed rows; event streaming publishes supported events. SQL-triggered Functions/Logic Apps need checkpoints, idempotency and retry controls. |
| AI/search | Track source columns/chunks, embedding model and update path. Exact KNN computes nearest neighbors; ANN trades recall for scale. VECTOR_DISTANCE measures distance; VECTOR_SEARCH invokes supported search behavior. Full-text is lexical, vector is semantic, hybrid combines ranks (for example RRF). Authorized retrieved rows become context, not executable instructions. |
Release/retrieval order and traps
Define source/model/schema → generate compatible embeddings → create supported index → filter by access → retrieve/rank → format context → call model → validate/cite. Version syntax differs across SQL Server, Azure SQL and Fabric; inspect the documented engine support before using newer JSON/vector functions. A successful schema build does not establish that production publication is nondestructive.
Last-pass self-check
1. Need history versus tamper evidence?
Temporal history and ledger address different requirements; neither replaces authorization or backup.
2. What does RRF avoid assuming?
That lexical and vector raw scores share a directly comparable scale.
3. Why not put an LLM-generated predicate straight into SQL?
Validate and parameterize it; untrusted output can be wrong or malicious.
4. Do Change Tracking and CDC provide identical information?
No. Choose from the needed keys, change details, history and supported retention.
5. Why preserve compatible old/new schema during rollout?
Application rollback and concurrent clients may still depend on the old contract.
Sources
- Official exam scope and version
- SQL JSON function purposes and support
- Product documentation
- SQL isolation guarantees and row-versioning behavior
- Product documentation
- Product documentation
- Product documentation
- Product documentation
Every topic at a glance
Open any topic to revisit its essential facts, decisions and exam traps. Use the full topic for active recall and supporting references.
01 · Relational design and advanced T-SQL
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.
02 · Security, concurrency and query performance
Memory hook: Protect rows, protect keys, inspect waits.
Must remember
- Use supported passwordless identity and least-privileged database permissions. Object grants, roles and row-level security control access; Dynamic Data Masking limits displayed values for selected users but is not encryption.
- Always Encrypted protects supported columns with client-side key handling; column encryption and TDE address different layers. Key custody and application compatibility determine the right design.
- Secure model, REST, GraphQL and MCP endpoints separately from the database. Managed identities reduce secrets but still need narrowly granted permissions; audit sensitive operations without leaking data.
- Isolation levels trade blocking, consistency and version-store behavior. Use transactions only as long as necessary and consistent update ordering; deadlocks require analysis of competing resources, not endless retries.
- Isolation recall: READ UNCOMMITTED permits dirty reads; READ COMMITTED prevents them but can allow nonrepeatable reads/phantoms; REPEATABLE READ retains protection for read rows but can allow new matching rows; SERIALIZABLE also protects key ranges. SNAPSHOT uses a transaction-level versioned view and can raise update conflicts. READ COMMITTED with READ_COMMITTED_SNAPSHOT enabled uses statement-level row-versioning, which differs from SNAPSHOT. Platform defaults vary; inspect database settings before diagnosing lock behavior.
- Query Store records query/plan/runtime history; execution plans and DMVs expose scans, estimates, waits and resource pressure. Query Performance Insight and Azure monitoring provide supported views.
- Tune query shape, indexes, statistics and configuration from measured evidence. Parameterization improves safety but parameter-sensitive plans still need diagnosis.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Users may see only their tenant’s rows | Enforced row-level security plus appropriate identity and permissions. |
| Sensitive columns must remain hidden from the server in supported operations | Evaluate Always Encrypted and client key requirements. |
Traps
- Masking is not a substitute for controlling SELECT and privileged access.
- Raising compute may not solve a deadlock caused by inconsistent update order.
03 · SQL projects and AI-assisted delivery
Memory hook: Review the model; inspect the deployment plan.
Must remember
- SQL Database Projects represent database schema in source control; supported SDK-style projects build a deployable model. A deployment tool compares desired schema with the target, so review destructive changes explicitly.
- Version reference/static data with a deliberate deployment strategy. Migration order, data movement and backward compatibility matter beyond whether a schema build succeeds.
- Use branches, pull requests, code owners, policy checks, tests and controlled approvals. Keep pipeline identities/secrets separate from developer credentials and restrict production deployment rights.
- Unit tests validate SQL logic; integration tests validate real engine behavior; deployment tests expose drift and data-loss risks. Detect unexpected target changes before publishing.
- Copilot can draft queries, schema or project changes. Repository instruction files and scoped context improve consistency, but generated SQL still needs correctness, security and performance review.
- MCP-connected SQL/Fabric tools can expose live data or mutation capabilities. Choose model/tool settings deliberately, start with read-only access for analysis and validate every proposed write against the user’s authorization.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Production schema differs from the project | Investigate drift and inspect the deployment diff before publishing. |
| AI proposes dropping a column to fix a build | Review data/dependency impact and use a safe migration plan. |
Traps
- A successful DACPAC/project build does not prove deployment is non-destructive.
- An AI assistant’s database connection can expose sensitive data even without changing it.
04 · Expose APIs and process changes
Memory hook: Database permission and API permission both matter.
Must remember
- Data API builder (DAB) maps supported database objects to REST/GraphQL endpoints through configuration. Define entities, relationships, authentication and role permissions deliberately.
- Pagination, filtering, searching and caching affect API correctness and performance. Avoid exposing unrestricted tables or overly broad stored procedures merely because endpoint generation is easy.
- Deploy DAB with protected configuration, network access and managed identity where supported. Correlate application telemetry with database metrics using Application Insights, Log Analytics and Azure Monitor.
- Change Data Capture records supported change details; Change Tracking identifies changed rows with lighter semantics; change event streaming emits supported events. Choose by required history, payload and downstream processing.
- Azure Functions SQL triggers and Logic Apps can react to supported changes. Design idempotent consumers, checkpoints, retries and dead-letter/recovery behavior.
- Treat schema changes as API contract changes. Coordinate clients, functions, models and retrieval indexes when fields or event shapes evolve.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need GraphQL over selected relational entities | DAB with explicit relationships and scoped permissions. |
| Need to know which rows changed without full historical values | Evaluate Change Tracking rather than assuming CDC is required. |
Traps
- An API gateway does not automatically enforce every database row-level rule correctly.
- An event arriving twice must not create two business transactions.
05 · Embeddings, search and SQL RAG
Memory hook: Keep vectors aligned with the source.
Must remember
- Select external models by modality, language, quality, output format, dimensions, security and cost. Configure supported model endpoints and credentials/managed identities without embedding secrets in SQL text.
- Choose source columns and chunk boundaries according to meaning and update patterns. Maintain embeddings through supported triggers, Change Tracking/CDC, Functions, Logic Apps or Foundry workflows; track model/version alongside each vector.
- Full-text search matches terms; vector search finds semantic neighbors; hybrid search combines both. KNN can provide exact nearest-neighbor results; ANN trades some recall for speed/scale.
- Use compatible vector types, dimensions, indexes and distance metrics. Supported VECTOR_DISTANCE, VECTOR_SEARCH, normalization and property functions have distinct purposes; check the platform/version syntax.
- Reciprocal rank fusion combines ranked lists without assuming raw lexical and vector scores share a scale. Evaluate recall, relevance, latency and filtering together.
- RAG retrieves authorized context, serializes suitable structured data as JSON, calls the model through supported mechanisms such as sp_invoke_external_rest_endpoint, then validates/cites the answer. Retrieved text must not gain permission to execute arbitrary SQL.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need exact terminology plus semantic similarity | Hybrid search with a measured ranking/fusion strategy. |
| Source documents changed after embedding | Update the affected chunks/vectors and remove stale entries. |
Traps
- Vectors from incompatible embedding models should not be compared as if they share one space.
- An ANN index does not guarantee the exact nearest result.