Microsoft SQL AI Developer

Not all data is flat. You store and query JSON in SQL, and use temporal, columnstore and other specialized table types where they fit, choosing each for a reason rather than for novelty.

• Storing JSON in columns and indexing paths
• Querying JSON with OPENJSON and JSON functions
• Temporal tables for history
• Columnstore for analytics
• Ledger and graph tables in brief
• Choosing a specialized object

You build A table design that mixes relational columns, a JSON document column and a temporal history table.

Logic placed in the database must be easy to test and safe to change. You write views, stored procedures, functions and triggers, handle errors and transactions, and decide what belongs in the database at all.

• Views and what they can hide
• Stored procedures, parameters and result sets
• Scalar and table-valued functions
• Triggers and their costs
• Transactions and TRY…CATCH error handling
• Deciding what logic belongs in the database

You build A set of procedures with transactional error handling and tests that exercise them.

Set-based thinking is the core T-SQL skill. You write window functions, common table expressions, recursive queries and APPLY, and learn to replace row-by-row code with queries the engine can optimize.

• Window functions for ranking and running totals
• CTEs and recursive queries
• CROSS APPLY and OUTER APPLY
• PIVOT, UNPIVOT and grouping sets
• MERGE and its pitfalls
• Replacing cursors with set-based queries

You build A set of reporting queries that replace cursor loops with set-based T-SQL.

AI tools now write and explain much SQL. You use GitHub Copilot to draft and review T-SQL, connect to databases through MCP, and keep control of what an assistant may read and change.

• Prompting Copilot for T-SQL
• Reviewing generated SQL for correctness and cost
• Explaining and refactoring existing code
• Connecting an assistant to a database through MCP
• Read-only access and guardrails for assistants
• Keeping schema context in the prompt

You build A Copilot workflow for generating and reviewing queries, with an MCP connection limited to read-only access.

Database security is layered. You encrypt sensitive columns with Always Encrypted, mask output with dynamic data masking, filter rows with row-level security, and grant permissions no wider than needed.

• Permissions, roles and least privilege
• Always Encrypted and column encryption keys
• Dynamic data masking and its limits
• Row-level security predicates
• Auditing access to sensitive data
• Testing protections as different users

You build A database with encrypted columns, masking and row-level security, tested as three different users.

Slow queries are usually plan problems. You read execution plans, design indexes that help real workloads, and learn what statistics, cardinality and parameter sensitivity do to a plan.

• Reading estimated and actual plans
• Clustered, nonclustered and covering indexes
• Statistics and cardinality estimation
• Parameter sensitivity
• Index maintenance and fragmentation
• Rewriting a query to change its plan

You build A tuned query, with plans before and after and the index that made the difference.

Performance regressions need history, and concurrency problems need patience. You use Query Store to find and fix regressions, and diagnose blocking and deadlocks through isolation levels and locking behavior.

• Query Store: capture and analysis
• Finding regressed queries
• Forcing plans and the risks of doing so
• Isolation levels and row versioning
• Diagnosing blocking
• Reading deadlock graphs

You build A Query Store investigation of a regression, and a fix for a reproduced deadlock.

Schema changes are code changes. You keep the schema in a SQL Database Project, build and test it in a pipeline, deploy it with a repeatable process, and detect when a live database drifts from source.

• SQL Database Projects and source control
• Building a project into a DACPAC
• Deploying through a GitHub pipeline
• Pre- and post-deployment scripts
• Drift detection and reconciliation
• Testing database changes

You build A GitHub pipeline that builds a database project, deploys it and reports drift.

Applications need an interface to the data. You publish tables and procedures through Data API builder as REST and GraphQL, secure them, and connect the database to other Azure services.

• Data API builder configuration
• REST endpoints over tables and procedures
• GraphQL schemas generated from the database
• Authentication and permissions on the API
• Calling Azure services from SQL and with Functions
• Hosting and scaling the API

You build A REST and GraphQL API over a database, with permissions set per role.

Vectors turn meaning into data you can store. You choose embedding models, generate embeddings from T-SQL, store them in vector columns, and decide how to keep them current as rows change.

• What embeddings are and how models differ
• Connecting the database to an embedding model
• Generating embeddings from T-SQL
• Vector data types and storage
• Keeping embeddings in step with source rows
• Chunking text before embedding

You build A table whose text rows are embedded automatically and kept current.

Keyword search finds words and vector search finds meaning, and you often need both. You build full-text and vector indexes, query them, and fuse the results into one ranking.

• Full-text indexes and queries
• Vector similarity queries and distance measures
• Approximate and exact nearest neighbor search
• Combining filters with vector search
• Hybrid search and rank fusion
• Measuring relevance

You build A search procedure that fuses full-text and vector results and is scored against test queries.

RAG can run next to the data it needs. You retrieve relevant rows, build a grounded prompt, call a language model from SQL, and handle the cost, latency and safety that come with it.

• The RAG flow inside the database
• Assembling a grounded prompt from retrieved rows
• Calling a language model from T-SQL
• Respecting row-level security during retrieval
• Latency, cost and caching
• Evaluating grounded answers

You build A stored procedure that answers a question from retrieved rows and returns its sources.

Combine design, security, delivery and AI in one database. You model the data, protect and tune it, ship it through CI/CD, and add search and retrieval that a language model can use safely.

• Designing the schema and its API
• Securing and tuning the workload
• Delivering through CI/CD
• Adding vector and hybrid search
• Adding grounded answers and testing them

You build A deployed database with an API, CI/CD, security, hybrid search and a grounded question-answering procedure.