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
- Lab : Designing and Implementing Tables
- Designing Tables
- Creating Schemas
- Creating Tables
3 – Advanced Table Designs
- Partitioning Data
- Compressing Data
- Temporal Tables
- Lab : Using Advanced Table Designs
- Partitioning Data
- Compressing Data
4 – Ensuring Data Integrity through Constraints
- Enforcing Data Integrity
- Implementing Data Domain Integrity
- Implementing Entity and Referential Integrity
- Lab : Using Data Integrity Through Constraints
- Add Constraints
- Test the Constraints
5 – Introduction to Indexes
- Core Indexing Concepts
- Data Types and Indexes
- Heaps, Clustered, and Nonclustered Indexes
- Single Column and Composite Indexes
- Lab : Implementing Indexes
- Creating a Heap
- Creating a Clustered Index
- Creating a Covered Index
6 – Designing Optimized Index Strategies
- Index Strategies
- Managing Indexes
- Execution Plans
- The Database Engine Tuning Advisor
- Query Store
- Lab : Optimizing Indexes
- Using Query Store
- Heaps and Clustered Indexes
- Creating a Covered Index
7 – Columnstore Indexes
- Introduction to Columnstore Indexes
- Creating Columnstore Indexes
- Working with Columnstore Indexes
- Lab : Using Columnstore Indexes
- Creating a Columnstore Index
- Create a Memory Optimized Columnstore Table
8 – Designing and Implementing Views
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
- Lab : Designing and Implementing Views
- Creating Standard Views
- Creating an Updateable view
9 – Designing and Implementing Stored Procedures
- Introduction to Stored Procedures
- Working with Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
- Lab : Designing and Implementing Stored Procedures
- Create Stored procedures
- Create Parameterized Stored procedures
- Changes Stored Procedure Execution Context
10 – Designing and Implementing User-Defined Functions
- Overview of Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-Valued Functions
- Considerations for Implementing Functions
- Alternatives to Functions
- Lab : Designing and Implementing User-Defined Functions
- Format Phone numbers
- Modify an Existing Function
11 – Responding to Data Manipulation via Triggers
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- Lab : Responding to Data Manipulation by Using Triggers
- Create and Test the Audit Trigger
- Improve the Audit Trigger
12 – Using In-Memory Tables
- Memory-Optimized Tables
- Natively Compiled Stored Procedures
- Lab : Using In-Memory Database Capabilities
- Using Memory-Optimized Tables
- Using Natively Compiled Stored procedures
13 – Implementing Managed Code in SQL Server
- Introduction to CLR Integration in SQL Server
- Implementing and Publishing CLR Assemblies
- Lab : Implementing Managed Code in SQL Server
- Assessing Proposed CLR Code
- Creating a Scalar-Valued CLR Function
- Creating a Table Valued CLR Function
14 – Storing and Querying XML Data in SQL Server
- Introduction to XML and XML Schemas
- Storing XML Data and Schemas in SQL Server
- Implementing the XML Data Type
- Using the Transact-SQL FOR XML Statement
- Getting Started with XQuery
- Shredding XML
- Lab : Storing and Querying XML Data in SQL Server
- Determining when to use XML
- Testing XML Data Storage in Variables
- Using XML Schemas
- Using FOR XML Queries
- Creating a Stored Procedure to Return 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
- Lab : Working with SQL Server Spatial Data
- Become Familiar with the Geometry Data Type
- Add Spatial Data to an Existing Table
- Find Nearby Locations
16 – Storing and Querying Blobs and Text Documents in SQL Server
- Considerations for BLOB Data
- Working with FILESTREAM
- Using Full-Text Search
- Lab : Storing and Querying BLOBs and Text Documents in SQL Server
- Enabling and Using FILESTREAM Columns
- Enabling and Using File Tables
- Using a Full-Text Index
17 – SQL Server Concurrency
- Concurrency and Transactions
- Locking Internals
- Lab : SQL Server Concurrency
- Implement Snapshot Isolation
- Implement Partition Level Locking
18 – Performance and Monitoring
- Extended Events
- Working with extended Events
- Live Query Statistics
- Optimize Database File Configuration
- Metrics
- Lab : Monitoring, Tracing, and Baselining
- Collecting and Analyzing Data Using Extended Events
- Implementing Baseline Methodology
