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.
Accredited course for Continuing Education of Pedagogical Staff
Course length: 5 days (8:30 - 16:00)
Dates
-
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
Prices do not include VAT. OKsystem a.s. reserves the right to change dates and prices of courses.