Developing SQL Databases

This course is intended for end users, Database Administrators, Database Developers, and BI professionals.

Module 1: Designing and Implementing Databases and Tables

• Lesson 1: Designing Databases
• Lesson 2: Designing Tables
• Lesson 3: Data Types
• Lesson 4: Working with Schemas
• Lesson 5: Creating and Altering Tables
• Lesson 6: Partitioning Data
• Lesson 7: Compressing Data
• Lesson 8: Change Tracking
• Lesson 9: Memory-Optimized Tables
• Lesson 10: Natively Compiled Stored Procedures

Module 2: Ensuring Data Integrity through Constraints

• Lesson 1: Enforcing Data Integrity
• Lesson 2: Implementing Data Domain Integrity
• Lesson 3: Implementing Entity and Referential Integrity

Module 3: Indexes

• Lesson 1: Core Indexing Concepts
• Lesson 2: Data Types and Indexes
• Lesson 3: Heaps, Clustered, and Nonclustered Indexes
• Lesson 4: Single Column and Composite Indexes
• Lesson 5: Index Strategies
• Lesson 6: Managing Indexes
• Lesson 7: Execution Plans
• Lesson 8: The Database Engine Tuning Advisor
• Lesson 9: Query Store
• Lesson 10: Creating Columnstore Indexes
• Lesson 11: Working with Columnstore Indexes

Module 4: Views

• Lesson 1: Introduction to Views
• Lesson 2: Creating and Managing Views
• Lesson 3: Performance Considerations for Views

Module 5: Stored Procedures

• Lesson 1: Introduction to Stored Procedures
• Lesson 2: Working with Stored Procedures
• Lesson 3: Implementing Parameterized Stored Procedures
• Lesson 4: Controlling Execution Context

Module 6: User-Defined Functions

• Lesson 1: Overview of Functions
• Lesson 2: Designing and Implementing Scalar Functions
• Lesson 3: Designing and Implementing Table-Valued Functions
• Lesson 4: Considerations for Implementing Functions
• Lesson 5: Alternatives to Functions

Module 7: Triggers

• Lesson 1: Designing DML and DDL Triggers
• Lesson 2: Implementing DML and DDL Triggers
• Lesson 3: Advanced Trigger Concepts

Module 8: Implementing Managed Code

• Lesson 1: CLR Integration in SQL Server
• Lesson 2: CLR Assemblies

Module 9: Storing and Querying XML Data

• Lesson 1: Introduction to XML and XML Schemas
• Lesson 2: Storing XML Data and Schemas
• Lesson 3: Implementing the XML Data Type
• Lesson 4: Using the Transact-SQL FOR XML Statement
• Lesson 5: Getting Started with XQuery
• Lesson 6: Shredding XML

Module 10: Spatial Data

• Lesson 1: Introduction to Spatial Data
• Lesson 2: Working with SQL Server Spatial Data Types
• Lesson 3: Using Spatial Data in Applications

Module 11: Storing and Querying BLOBs and Text Documents

• Lesson 1: Considerations for BLOB Data
• Lesson 2: Working with FILESTREAM
• Lesson 3: Using Full-Text Search

Module 12: SQL Server Concurrency

• Lesson 1: Concurrency and Transactions
• Lesson 2: Locking Internals

Module 13: Performance and Monitoring   

• Lesson 1: Extended Events
• Lesson 2: Working with extended Events
• Lesson 3: Live Query Statistics
• Lesson 4: Optimize Database File Configuration
• Lesson 5: Metrics

• Design and create disk tables and in-memory tables.
• Create and design constraints to ensure integrity.
• Create and use triggers and functions.
• Design and implement stored procedures.
• Implement Managed Code in SQL Server.
• Create indexes, including Columnstore indexes.
• Save and query XML Data.
• Design and implement views.
• Work with geometric and geographical data.
• Work with binary data and documents.
• Performance tunning, handling errors and transactions.

• Basic knowledge of T-SQL.
• Basic knowledge of the Microsoft Windows operating system..

Classrooms are equipped with high-performance computers with Internet access and the possibility of wireless connection.

All participants will get internal OKsystem student materials.