Developing SQL Databases

This five-day instructor-led course provides students with the knowledge and skills to design and develop Microsoft SQL Server databases, database objects, indexes and use product features and tools related to developing a database. It also deals with performance, transaction processing, error handling, special data types, including work with binary data.

Course length: 5 days (8:30 - 16:00)

Dates

PDF to download Expand allCollapse all

• Students will be able to

  • 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.

• Course requirements

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

• This course is intended for

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

• Literature

  All participants will get internal OKsystem student materials.

• Hardware

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

• Syllabus

  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