Learn how to design and implement database objects in SQL Server, Azure SQL, and SQL databases in Microsoft Fabric, including tables, indexes, constraints, and specialized table types.
After completing this module, you'll be able to:
- Design and implement tables with appropriate data types, sizes, and columns
- Implement specialized tables including in-memory, temporal, external, LEDGER, and GRAPH tables
- Design and implement JSON columns and indexes
- Design and implement database constraints including primary key, foreign key, unique key, CHECK, and DEFAULT
- Design and implement SEQUENCES
- Design and implement partitioning strategies for tables and indexes
Learn how to implement views, stored procedures, functions, and triggers in SQL Server to encapsulate logic, improve code reusability, and enforce business rules.
In this module, you'll:
- Create views to simplify data access and provide security boundaries.
- Build stored procedures to encapsulate complex business logic and data modifications.
- Develop scalar functions to return single calculated values.
- Implement table-valued functions to return result sets for use in queries.
- Configure triggers to automatically respond to data modifications or database events.
- Choose the right programmability object based on specific requirements.
Learn how to write advanced T-SQL code including CTEs, window functions, JSON functions, regular expressions, fuzzy string matching, graph queries, correlated queries, and error handling.
By the end of this module, you'll be able to:
- Write Common Table Expressions (CTEs) including recursive CTEs for hierarchical data
- Use window functions for ranking, aggregation, and analytical calculations
- Parse, construct, and transform JSON data using JSON functions
- Apply regular expressions for pattern matching and text manipulation
- Find approximate matches using fuzzy string matching functions
- Create and query graph data using the MATCH operator
- Write correlated subqueries for row-by-row comparisons
- Implement structured error handling with TRY...CATCH
Learn how to use GitHub Copilot and Fabric Copilot for AI-assisted database development on Microsoft SQL platforms, including SQL Server, Azure SQL, and Microsoft Fabric.
By the end of this module, you'll be able to:
- 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

Syllabus

Design and implement database objects with SQL
- Introduction
- 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
- Summary
Implement programmability objects with SQL
- Introduction
- 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
- Summary
Write advanced T-SQL code
- Introduction
- 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
- Summary
Implement SQL solutions by using AI-assisted tools
- Introduction
- 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
- Summary