Develop AI-enabled database solutions

Outline

• Design and implement database objects with SQL
  • Understand your SQL Server-based platform choices
  • Build effective tables
  • Optimize with indexes
  • Use specialized table types
  • Enforce data integrity with constraints
  • Manage JSON columns and indexes
  • Partition tables for scale
  • Exercise - Create and maintain database objects
  • Module assessment
• Implement programmability objects with SQL
  • Create views
  • Create stored procedures
  • Create scalar functions
  • Create table-valued functions
  • Create triggers
  • Choose when to use each option
  • Exercise: Implement programmability objects in SQL Server
  • Knowledge check
• Write advanced T-SQL code
  • Organize queries with Common Table Expressions
  • Apply window functions for analytics
  • Process JSON data with built-in functions
  • Match patterns with regular expressions
  • Find approximate matches with fuzzy string functions
  • Traverse relationships with graph queries
  • Compare rows with correlated subqueries
  • Handle errors with TRY...CATCH
  • Exercise - Work with JSON functions
  • Module assessment
• Implement SQL solutions by using AI-assisted tools
  • Describe AI-assisted development tools available for Microsoft SQL platforms
  • Interpret security impact of using AI-assisted tools
  • Enable GitHub Copilot and Fabric Copilot
  • Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Fabric Copilot chat session
  • Create and configure GitHub Copilot instruction files
  • Connect to MCP server endpoints, including Microsoft SQL Server and Fabric Lakehouse
  • Exercise - Configure AI-assisted tools for database development
  • Module assessment
• Implement data security and compliance with SQL
  • Protect data with encryption
  • Configure dynamic data masking
  • Implement row-level security
  • Manage permissions and secure access
  • Implement auditing
  • Configure secure access to AI services
  • Secure data API endpoints
  • Exercise: Implement security features
  • Module assessment
• Optimize database performance
  • Recommend database configurations
  • Preserve data integrity with transaction isolation levels and concurrency controls
  • Evaluate query performance with execution plans and DMVs
  • Monitor and tune queries with Query Store and Query Performance Insight
  • Identify and resolve blocking and deadlocks
  • Exercise: Optimize query performance
  • Knowledge check
• Implement CI/CD by using SQL Database Projects
  • Create, build, and validate SQL Database Projects
  • Configure source control and manage reference data
  • Manage branching, pull requests, and conflict resolution
  • Detect and resolve schema drift
  • Implement CI/CD pipelines
  • Design and implement a testing strategy
  • Exercise: Implement CI/CD by using SQL Database Projects
  • Knowledge check
• Integrate SQL solutions with Azure services
  • Create configuration files for Data API Builder
  • Define entities for REST and GraphQL
  • Expose database objects, stored procedures, and views
  • Explore deployment options for Data API Builder
  • Recommend Azure Monitor configurations
  • Handle changes with event-driven patterns
  • Exercise - Configure Data API Builder for a product catalog
  • Module assessment
• Design and implement models and embeddings with SQL
  • Understand and evaluate models for SQL database workloads
  • Create and manage external models in SQL
  • Design embeddings for SQL database workloads
  • Generate and maintain embeddings for SQL database workloads
  • Exercise - Generate and update embeddings in Azure SQL Database
  • Knowledge check
• Design and implement intelligent search with SQL
  • Choose an intelligent search approach
  • Implement full-text search
  • Prepare SQL for vector search
  • Implement vector search query patterns
  • Implement hybrid search and ranking
  • Exercise - Implement intelligent search with full-text, vector, and hybrid queries
  • Knowledge check
• Design and implement RAG with SQL
  • Identify RAG use cases and architecture
  • Prepare retrieval context for augmentation
  • Augment prompts with database context
  • Generate and process RAG responses
  • Exercise: Implement a RAG solution
  • Knowledge check