Developing SQL Databases (M20762)

1. Introduction to Database Development

• Introduction to the SQL Server Platform
• SQL Server Database Development Tasks

2. Designing and Implementing Tables

• Designing Tables
• Data Types
• Working with Schemas
• Creating and Altering Tables

3. Advanced Table Designs

• Partitioning Data
• Compressing Data
• Temporal Tables

4. Ensuring Data Integrity through Constraints

• Enforcing Data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity

5. Introduction to Indexes

• Core Indexing Concepts
• Data Types and Indexes
• Heaps, Clustered, and Nonclustered Indexes
• Single Column and Composite Indexes

6. Designing Optimized Index Strategies

• Index Strategies
• Managing Indexes
• Execution Plans
• The Database Engine Tuning Advisor
• Query Store

7. Columnstore Indexes

• Introduction to Columnstore Indexes
• Creating Columnstore Indexes
• Working Columnstore Indexes

8. Designing and Implementing Views

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views

9. Designing and Implementing Stored Procedures

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context

10. Designing and Implementing User-Defined Functions

• Overview of Functions
  Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Alternatives to Functions

11. Responding to Data Manipulation via Triggers

• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts

12. Using In-Memory Tables

• In-Memory Tables
• Native Stored Procedures

13. Implementing Managed Code in SQL Server

• Introduction to CLR Integration in SQL Server
• Implementing and Publishing CLR Assemblies

14. Storing and Querying XML Data in SQL Server

• XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the T-SQL FOR XML Statement
• Getting Started with xQuery
• Shredding XML

15. Storing and Querying Spatial Data in SQL Server

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

16. Storing and Querying BLOBs and Text Documents in SQL Server

• Considerations for BLOB Data
• Working with FileStream
• Using Full-Text Search

17. Performance and Monitoring

• Concurrency and Transactions
• Locking Internals

18. Performance and Monitoring

• Extended Events
• Working with extended Events
• Live Query Statistics
• Optimize Database File Configuration 
• Metrics

Lab 1: Designing and Implementing Tables
Lab 2: Using Advanced Table Designs
Lab 3: Using Data Integrity Through Constraints
Lab 4: Implementing Indexes
Lab 5: Optimizing Indexes
Lab 6: Using Columnstore Indexes
Lab 7: Designing and Implementing Views
Lab 8: Designing and Implementing Stored Procedures
Lab 9: Designing and Implementing User-Defined Functions
Lab 10: Responding to Data Manipulation by Using Triggers
Lab 11: Using In-Memory Database Capabilities
Lab 12: Implementing Managed Code in SQL Server
Lab 13: Storing and Querying XML Data in SQL Server
Lab 14: Working with SQL Server Spatial Data
Lab 15: Storing and Querying BLOBs and Text Documents in SQL Server
Lab 16: SQL Server Concurrency
Lab 17: Monitoring, Tracing, and Baselining