Bad database design and inefficient queries lead to applications that are hard to maintain and upgrade and that don’t perform well. Why don’t you start with a good design? This seminar provides developers and administrators with essential knowledge needed for a good database logical and physical design for well-performing applications in a single day. Of course, the knowledge gained by attending this seminar helps improving design and performance of existing databases as well.
This seminar explains the relational model for transactional databases and the dimensional model for data warehouses. However, even the best logical model can’t help when the physical implementation is bad. Therefore, the seminar shows in details how SQL Server stores and accesses data, and how to optimize the relational and the dimensional model.
This seminar is intended for application, database and business intelligence developers, and in addition for database administrators and solution architects.
Before attending this course, it is recommended that students have the following skills:
- Basic familiarity with the Transact-SQL language.
The following course: Microsoft MOC #20761 - Querying Data with Transact-SQL is recommended if you are not familiar with the T-SQL language.
Upon completion of this seminar, the student will be able to:
- Understand the relational model
- Design a relational database
- Use normalization and generalization / specialization
- Set up constraints to support business rules
- Understand SQL Server physical storage
- Explain SQL Server access methods
- Support queries with indexes
- Maintain statistics
- Parameterize queries
- Use optimizer hints and plan guides
- Create a dimensional model for a data warehouse
- Design dimensions and fact tables
- Optimize a dimensional database
- Write efficient data warehouse queries
- Understand and use Columnstore indexes and batch processing.
Seminar Summary Outline:
Module 01: Relational Model
- The relational model
- Normalization and denormalization
- Specialization and generalization
Module 02: SQL Server Storage and Access
- SQL Server data structures
- Access methods
- Understanding statistics
Module 03: Optimizing a Relational Database
- Supporting queries with indexes
- Parameterized queries
- Optimizer hints and plan guides
Module 04: Dimensional Model
- Star and Snowflake schemas
- Designing dimensions
- Designing fact tables
Module 05: Optimizing a Dimensional Database
- Data warehousing problems
- Optimizing analytical queries
- Columnar storage
- 22., 23., 24., 26. junij
- 9:00 - 11:00