You Can Pass This Exam For Free
Choose Your Study Path
Limited SQL or database development experience. You need to build foundational T-SQL skills and database design knowledge before tackling AI integration topics.
Week 1-2Learn T-SQL fundamentals: SELECT, JOIN, subqueries, CTEs, window functions. Practice writing queries against sample databases like AdventureWorks or WideWorldImporters
Week 3Study database design: tables, data types, constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT), indexes (clustered, nonclustered, columnstore), and partitioning
Week 4Learn programmability objects: views, scalar functions, table-valued functions, stored procedures, and triggers. Write working examples of each
Week 5Study advanced T-SQL: JSON functions (JSON_OBJECT, JSON_ARRAY, OPENJSON, JSON_VALUE), regular expressions (REGEXP_LIKE, REGEXP_REPLACE), fuzzy matching (EDIT_DISTANCE), and graph queries with MATCH
Week 6Cover security and compliance: Always Encrypted, column-level encryption, Dynamic Data Masking, Row-Level Security, object-level permissions, passwordless access, and auditing
Week 7Study performance optimization: execution plans, DMVs, Query Store, Query Performance Insight, transaction isolation levels, blocking, and deadlocks
Week 8Learn CI/CD with SQL Database Projects: SDK-style projects, source control, branching, schema drift detection, deployment pipelines, secrets management, and testing strategies
Week 9Study AI capabilities: external models, embeddings, vector data type, VECTOR_DISTANCE, VECTOR_SEARCH, semantic search vs full-text search vs hybrid search, and RAG patterns with sp_invoke_external_rest_endpoint
Week 10Learn Data API builder (DAB): configuration files, REST and GraphQL endpoints, entity configuration, caching, pagination. Also cover change event streaming, CDC, and Azure Functions with SQL trigger binding
Week 11Practice questions across all domains, review explanations carefully. Focus on Domains 1 and 2 which together are 70-80% of the exam
Week 12Take full mock exams under timed conditions (120 minutes). Review weak areas, re-study any domains where you score below 70%
Exam Overview
Format
Approximately 50 questions, 120 minutes. Multiple choice, multiple select, case studies, and interactive lab-style questions.
Scoring
Scaled score 100-1000. Passing: 700. No penalty for wrong answers — always answer every question.
Domains & Weights
- Design and Develop Database Solutions37%
- Secure, Optimize, and Deploy Database Solutions37%
- Implement AI Capabilities in Database Solutions26%
Registration
$165 USD. Available at Pearson VUE testing centers or online proctored from home. Exam fee is $165 USD.
Topic Priority Table
Not all topics are tested equally. Focus your study time on Tier 1 first, then Tier 2. Tier 3 topics rarely appear — just recognize what they do.
Domain 137% of exam
Design and Develop Database Solutions
This domain covers the full breadth of database design and T-SQL development. You need to design tables with appropriate data types, indexes, and constraints, implement programmability objects like views and stored procedures, write advanced T-SQL including JSON functions, regular expressions, and graph queries, and use AI-assisted development tools like GitHub Copilot and MCP server connections.
Key Topics
Tables and IndexesStored ProceduresViews and FunctionsJSON FunctionsRegular ExpressionsGraph QueriesCTEs and Window FunctionsGitHub CopilotMCP Server
Must-Know Concepts
- Table design: choosing appropriate data types, sizes, columns, clustered and nonclustered indexes, and columnstore indexes for the workload type
- Specialized table types: in-memory (MEMORY_OPTIMIZED), temporal (system-versioned with history table), external (referencing remote data), ledger (tamper-evident with cryptographic hashing), and graph (node and edge tables)
- JSON column design: creating JSON columns, JSON indexes, and using JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, JSON_CONTAINS, OPENJSON, and JSON_VALUE for querying semi-structured data
- Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT — know when to use each and how they enforce data integrity
- SEQUENCES: how to create and use sequences for generating ordered numeric values independently of tables
- Table and index partitioning: partition functions, partition schemes, and how partitioning improves manageability and query performance for large tables
- Programmability objects: creating views, scalar functions, table-valued functions, stored procedures, and triggers — know the use cases and limitations of each
- CTEs (Common Table Expressions): syntax, recursive CTEs for hierarchical data, and when CTEs are better than subqueries or temp tables
- Window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, SUM/AVG/COUNT with OVER and PARTITION BY
- JSON functions: constructing JSON (JSON_OBJECT, JSON_ARRAY), parsing JSON (OPENJSON, JSON_VALUE), aggregating JSON (JSON_ARRAYAGG), and testing containment (JSON_CONTAINS)
- Regular expressions: REGEXP_LIKE (matching), REGEXP_REPLACE (replacement), REGEXP_SUBSTR (extraction), REGEXP_INSTR (position), REGEXP_COUNT (counting), REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE
- Fuzzy string matching: EDIT_DISTANCE (Levenshtein distance), EDIT_DISTANCE_SIMILARITY (percentage match), and JARO_WINKLER_DISTANCE (positional similarity) for approximate matching
- Graph queries: creating node tables and edge tables, writing MATCH operator queries to traverse graph relationships
- Error handling: TRY/CATCH blocks, THROW, RAISERROR, @@ERROR, and XACT_ABORT for transactional error management
- AI-assisted tools: enabling GitHub Copilot, configuring instruction files, connecting to MCP server endpoints for SQL Server and Fabric lakehouse, and understanding the security implications of AI-assisted code generation
Common Exam Traps
Temporal tables require TWO tables: the current table and a history table. The history table is system-managed — you cannot directly modify it
Ledger tables come in TWO types: updatable (tracks changes) and append-only (no updates or deletes allowed). Know when to use each
JSON_ARRAYAGG aggregates multiple rows into a JSON array, while JSON_ARRAY constructs an array from individual values. They serve different purposes
Graph MATCH queries use arrow syntax (->) to traverse edges. You must understand the direction matters in graph traversal
GitHub Copilot instruction files (.github/copilot-instructions.md) customize Copilot behavior for your project. They do NOT replace code review — AI-generated code still needs security review
Domain 237% of exam
Secure, Optimize, and Deploy Database Solutions
This domain covers data security features, performance optimization, CI/CD pipeline implementation with SQL Database Projects, and integration with Azure services including Data API builder. Together with Domain 1, these two domains represent about 74% of the exam. You need both conceptual knowledge and practical ability to implement these features.
Key Topics
Always EncryptedDynamic Data MaskingRow-Level SecurityQuery StoreExecution PlansSQL Database ProjectsData API BuilderAzure MonitorCDC
Must-Know Concepts
- Always Encrypted: deterministic vs randomized encryption, enclave-enabled Always Encrypted, column master keys vs column encryption keys, and that the database engine NEVER sees plaintext
- Column-level encryption: encrypting specific columns using symmetric keys and certificates, different from Always Encrypted which is client-side
- Dynamic Data Masking: default, email, random, and custom string masking functions. Users with UNMASK permission bypass masking
- Row-Level Security: filter predicates (control which rows are returned by SELECT), block predicates (control which rows can be modified by INSERT/UPDATE/DELETE), and security policy objects
- Object-level permissions: GRANT, DENY, REVOKE on tables, views, stored procedures, schemas. Understand permission inheritance and the principle of least privilege
- Passwordless database access: Azure AD authentication, Managed Identity, eliminating connection strings with passwords
- Auditing: server-level and database-level auditing, audit action groups, writing to Azure Blob Storage or Log Analytics
- Securing endpoints: Managed Identity for model endpoints, authentication/authorization for GraphQL, REST, and MCP endpoints
- Query execution plans: reading actual vs estimated plans, identifying expensive operators (Table Scan, Key Lookup, Sort), and using plans to diagnose performance issues
- Dynamic Management Views (DMVs): sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_os_wait_stats for monitoring query and server performance
- Query Store: enabling, configuring retention, identifying regressed queries, forcing plans, and comparing performance across time periods
- Query Performance Insight: Azure SQL dashboard for identifying top resource-consuming queries
- Blocking and deadlocks: identifying blocking chains, using sys.dm_exec_requests and sys.dm_tran_locks, implementing deadlock retry logic, and designing to minimize lock contention
- Transaction isolation levels: READ UNCOMMITTED (dirty reads), READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE, and SNAPSHOT — know which phenomena each prevents
- SQL Database Projects: SDK-style .sqlproj files, build with dotnet build, schema comparison, deployment with SqlPackage or dacpac, and schema drift detection
- CI/CD pipeline design: source control integration, branching policies, pull request workflows, deployment triggers, approval gates, code owners, secrets management, and unit/integration testing strategies
- Data API builder (DAB): JSON configuration files, entity definitions for tables/views/stored procedures, REST and GraphQL endpoint configuration, pagination, filtering, searching, caching, and GraphQL relationships
- Azure Monitor: Application Insights for application-level telemetry, Log Analytics for centralized log queries, and diagnostic settings
- Change patterns: Change Data Capture (CDC), Change Tracking, Change Event Streaming (CES), Azure Functions with SQL trigger binding, and Azure Logic Apps for event-driven architectures
Common Exam Traps
Always Encrypted deterministic encryption allows equality comparisons but NOT range queries. Randomized encryption does not allow any comparisons at all
Dynamic Data Masking is NOT a security boundary — it only hides data in query results. A determined user might infer masked values through crafted queries. Use it for convenience, not for true data protection
Query Store captures plans and statistics automatically but you must ENABLE it first. It is not enabled by default on all platforms
Schema drift means the live database has diverged from the source-controlled project. SQL Database Projects can DETECT drift but you must decide how to resolve it — the tool does not automatically fix it
Data API builder generates REST AND GraphQL endpoints simultaneously from the same config. Do not assume you must choose one or the other
CDC captures full row data (before and after images). Change Tracking only captures primary keys of changed rows. The exam tests this distinction in the context of embedding maintenance
Domain 326% of exam
Implement AI Capabilities in Database Solutions
This domain covers integrating AI directly into SQL databases: evaluating and managing external AI models, generating and maintaining embeddings, implementing intelligent search (full-text, vector, and hybrid), and building RAG workflows using sp_invoke_external_rest_endpoint. While the smallest domain by weight, this is the most novel material and a key differentiator for the certification.
Key Topics
External ModelsEmbeddingsAI_GENERATE_EMBEDDINGSVector SearchHybrid SearchRAGVECTOR_DISTANCEVECTOR_NORMALIZEVECTORPROPERTYVECTOR_SEARCHDiskANN
Must-Know Concepts
- Evaluating external models: multimodal capabilities, multilanguage support, model sizes, structured output support, and cost considerations when choosing an AI model for your database solution
- Creating and managing external models: registering models with CREATE EXTERNAL MODEL, configuring credentials, and managing model lifecycle
- Embedding maintenance methods: table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, Change Event Streaming (CES), and Microsoft Foundry — know which to use for different freshness requirements
- Choosing which columns to include in embeddings: identifying the most semantically meaningful columns, combining multiple columns for richer embeddings, and excluding irrelevant data
- Designing and implementing chunks for embeddings: splitting large text into appropriately sized chunks with overlap to maintain context across chunk boundaries
- Generating embeddings: using AI_GENERATE_EMBEDDINGS (the native T-SQL function that calls a registered external model) or sp_invoke_external_rest_endpoint (direct REST call to an AI API). AI_GENERATE_EMBEDDINGS requires a CREATE EXTERNAL MODEL registration and is the more concise, exam-preferred method
- Full-text search: creating full-text indexes, full-text catalogs, and using CONTAINS, FREETEXT, CONTAINSTABLE, and FREETEXTTABLE for keyword search
- Vector data type: declaring vector columns with dimensions, understanding the vector data type limitations, and storing embeddings
- VECTOR_DISTANCE: syntax with three distance metrics (cosine, Euclidean, dot product), when to use each metric, and the performance characteristics of exact search
- VECTOR_NORMALIZE: normalizes a vector to unit length using a specified norm type (norm1, norm2, norminf). Use before storing or comparing embeddings when magnitude should not affect similarity scores
- VECTORPROPERTY: returns metadata about a vector column — Dimensions (the count of dimensions as an integer) or BaseType (the underlying data type name). Used for inspecting vector schema programmatically
- VECTOR_SEARCH: using DiskANN indexes for approximate nearest neighbor search, understanding recall vs speed tradeoffs, and configuring vector index parameters
- ANN vs ENN: Approximate Nearest Neighbor (VECTOR_SEARCH with indexes, fast but approximate) vs Exact Nearest Neighbor (VECTOR_DISTANCE scanning all vectors, slow but precise)
- Hybrid search: combining full-text and vector search results for improved relevance using Reciprocal Rank Fusion (RRF) to merge ranked lists
- Evaluating search performance: measuring recall, precision, latency, and relevance of vector and hybrid search implementations
- RAG workflow: (1) receive user query, (2) generate query embedding, (3) search for relevant context using vector/hybrid search, (4) construct prompt with retrieved context, (5) send to language model via sp_invoke_external_rest_endpoint, (6) extract and return the response
- Converting structured data to JSON for language model processing: using FOR JSON, JSON_OBJECT, and JSON_ARRAY to format query results as model input
Common Exam Traps
VECTOR_DISTANCE does NOT use indexes — it scans all vectors for exact results. VECTOR_SEARCH uses DiskANN indexes for approximate results. Mixing these up is a common exam mistake
Cosine distance is the standard metric for text embeddings and NLP. Do not default to Euclidean unless the scenario specifically involves numeric/spatial data
Embedding maintenance is ongoing, not one-time. When source data changes, embeddings must be regenerated. The exam tests which maintenance method (triggers, CDC, CES, etc.) fits the update frequency and latency requirements
RAG does NOT retrain or fine-tune the model. It augments the prompt with retrieved context at query time. The model weights are unchanged
Chunking strategy matters for embedding quality. Chunks that are too small lose context; chunks that are too large dilute relevance. Overlap between chunks preserves continuity at boundaries
sp_invoke_external_rest_endpoint is a SYSTEM stored procedure. You cannot create your own version of it. Know its exact syntax for calling REST APIs from T-SQL
AI_GENERATE_EMBEDDINGS requires a CREATE EXTERNAL MODEL registration first. It is NOT the same as calling sp_invoke_external_rest_endpoint manually — AI_GENERATE_EMBEDDINGS is the higher-level, single-function approach that the exam emphasizes for embedding generation
VECTOR_NORMALIZE does not change what is stored in the database — it returns a new normalized vector as output. You must explicitly store the result if you want the normalized form. Also, dot product distance assumes pre-normalized vectors; using unnormalized vectors with dot product gives inaccurate similarity scores
Technologies and Concepts You Must Not Confuse
These pairs appear on nearly every exam. Learn the difference and you'll avoid the most common traps.
Top Mistakes to Avoid
Confusing VECTOR_DISTANCE (exact nearest neighbor, no index, scans all vectors) with VECTOR_SEARCH (approximate nearest neighbor, uses DiskANN index, faster but approximate)
Using Euclidean distance for text embeddings when cosine distance is the standard metric for NLP and RAG workloads
Thinking Always Encrypted and Dynamic Data Masking provide the same level of protection — Always Encrypted prevents even DBAs from seeing data, while masking only hides data in query results
Confusing Row-Level Security (hides entire rows) with Dynamic Data Masking (hides column values) — they operate on different axes of access control
Mixing up CDC (captures full before/after row data) with Change Tracking (captures only which rows changed by primary key) when choosing an embedding maintenance method
Confusing AI_GENERATE_EMBEDDINGS (the native T-SQL function paired with CREATE EXTERNAL MODEL) with sp_invoke_external_rest_endpoint (a lower-level raw REST call) — the exam expects you to know both and when to use each
Assuming RAG fine-tunes or retrains the AI model — RAG only augments the prompt with retrieved context at query time without changing model weights
Confusing JSON_ARRAY (constructs an array from scalar values) with JSON_ARRAYAGG (aggregates multiple rows into a JSON array) — different purposes, different syntax
Not understanding that SQL Database Projects detect schema drift but do not automatically resolve it — the developer must decide how to handle the divergence
Forgetting that Data API builder generates both REST and GraphQL endpoints simultaneously from one configuration file — you do not have to choose one
Thinking temporal tables provide tamper detection — they track history but do not cryptographically verify integrity like ledger tables do
Exam-Ready Checklist
Can explain all 3 exam domains and their relative weights (37%, 37%, 26%)
Know how to design tables with appropriate data types, indexes (clustered, nonclustered, columnstore), constraints, and partitioning strategies
Can write advanced T-SQL including CTEs, window functions, JSON functions, regular expressions, fuzzy matching, and graph queries
Understand all specialized table types: temporal, ledger, in-memory, external, and graph — and when to use each
Can implement and differentiate Always Encrypted, Dynamic Data Masking, and Row-Level Security
Know how to read query execution plans, use DMVs, Query Store, and Query Performance Insight to diagnose performance issues
Understand CI/CD with SQL Database Projects: SDK-style projects, source control, branching, schema drift detection, and deployment pipelines
Can configure Data API builder for REST and GraphQL endpoints including entity exposure, pagination, caching, and GraphQL relationships
Know how to generate embeddings using sp_invoke_external_rest_endpoint and store them in vector columns
Can distinguish VECTOR_DISTANCE (ENN) from VECTOR_SEARCH (ANN) and choose the right distance metric (cosine, Euclidean, dot product)
Understand hybrid search implementation and Reciprocal Rank Fusion (RRF) for merging keyword and semantic results
Can implement a complete RAG workflow: query embedding, context retrieval, prompt construction, model invocation, and response extraction
Know embedding maintenance methods (triggers, CDC, CES, Change Tracking, Azure Functions, Logic Apps) and when to use each
Scored 70%+ on at least two full mock exams (700/1000 passing score)
Recommended Resources
Free & Official Resources
Paid Courses & Practice Exams
These are recommended if you prefer a structured learning path. They can save time but are not required to pass.