Audience
The primary audience for this course is IT Professionals who want to become skilled on SQL Server product features and technologies for implementing a database.
The secondary audiences for this course are individuals who are developers from other product platforms looking to become skilled in the implementation of a SQL Server database.
Prerequisites
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of Transact-SQL.
- Working knowledge of relational databases.
Duration
5 days. Hands on.
Course Objectives
This five-day instructor-led course provides students with the knowledge and skills to develop a Microsoft SQL Server database. The course focuses on teaching individuals how to use SQL Server product features and tools related to developing a database.
After completing this course, students will be able to:
- Design and Implement Tables.
- Describe advanced table designs.
- Ensure Data Integrity through Constraints.
- Describe indexes, including Optimized and Columnstore indexes.
- Design and Implement Views.
- Design and Implement Stored Procedures.
- Design and Implement User Defined Functions.
- Respond to data manipulation using triggers.
- Design and Implement In-Memory Tables.
- Implement Managed Code in SQL Server.
- Store and Query XML Data.
- Work with Spatial Data.
- Store and Query Blobs and Text Documents.
Course Content
Module 1: Introduction to Database Development
Introduction to the SQL Server Platform
SQL Server Database Development Tasks
After completing this module, you will be able to:
• Describe the SQL Server platform.
• Use SQL Server administration tools.
Module 2: Designing and Implementing Tables
Designing Tables
Data Types
Working with Schemas
Creating and Altering Tables
Lab : Designing and Implementing Tables
After completing this module, you will be able to:
• Design tables using normalization, primary and foreign keys.
• Work with identity columns.
• Understand built-in and user data types.
• Use schemas in your database designs to organize data, and manage object security.
• Work with computed columns and temporary tables.
Module 3: Advanced Table Designs
Partitioning Data
Compressing Data
Temporal Tables
Lab : Using Advanced Table Designs
After completing this module, you will be able to:
• Describe the considerations for using partitioned tables in a SQL Server database.
• Plan for using data compression in a SQL Server database.
• Use temporal tables to store and query changes to your data.
Module 4: Ensuring Data Integrity through Constraints
Enforcing Data Integrity
Implementing Data Domain Integrity
Implementing Entity and Referential Integrity
Lab : Using Data Integrity Through Constraints
After completing this module, you will be able to:
• Describe the options for enforcing data integrity, and the levels at which they should be applied.
• Implement domain integrity through options such as check, unique, and default constraints.
• Implement referential integrity through primary and foreign key constraints.
Module 5: Introduction to Indexes
Core Indexing Concepts
Data Types and Indexes
Heaps, Clustered, and Nonclustered Indexes
Single Column and Composite Indexes
Lab : Implementing Indexes
After completing this module, you will be able to:
• Explain core indexing concepts.
• Evaluate which index to use for different data types.
• Describe the difference between single and composite column indexes.
Module 6: Designing Optimized Index Strategies
Index Strategies
Managing Indexes
Execution Plans
The Database Engine Tuning Advisor
Query Store
Lab : Optimizing Indexes
After completing this module, you will be able to:
• What a covering index is, and when to use one.
• The issues involved in managing indexes.
• Actual and estimated execution plans.
• How to use Database Tuning Advisor to improve the performance of queries.
• How to use Query Store to improve query performance.
Module 7: Columnstore Indexes
Introduction to Columnstore Indexes
Creating Columnstore Indexes
Working with Columnstore Indexes
Lab : Using Columnstore Indexes
After completing this module, you will be able to:
• Describe columnstore indexes and identify suitable scenarios for their use.
• Create clustered and nonclustered columnstore indexes.
• Describe considerations for using columnstore indexes.
Module 8: Designing and Implementing Views
Introduction to Views
Creating and Managing Views
Performance Considerations for Views
Lab : Designing and Implementing Views
After completing this module, you will be able to:
• Understand the role of views in database design.
• Create and manage views.
• Understand the performance considerations with views.
Module 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
After completing this module, you will be able to:
• Understand what stored procedures are, and what benefits they have.
• Design, create, and alter stored procedures.
• Control the execution context of stored procedures.
• Implement stored procedures that use parameters.
Module 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
After completing this module, you will be able to:
• Describe different types of functions.
• Design and implement scalar functions.
• Design and implement table-valued functions (TVFs).
• Describe considerations for implementing functions.
• Describe alternatives to functions.
Module 11: Responding to Data Manipulation via Triggers
Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts
Lab : Responding to Data Manipulation by Using Triggers
After completing this module, you will be able to:
• Design DML triggers.
• Implement DML triggers.
• Explain advanced DML trigger concepts, such as nesting and recursion.
Module 12: Using In-Memory Tables
Memory-Optimized Tables
Natively Compiled Stored Procedures
Lab : Using In-Memory Database Capabilities
After completing this module, you will be able to:
• Use memory-optimized tables to improve performance for latch-bound workloads.
• Use natively compiled stored procedures.
Module 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
After completing this module, you will be able to:
• Explain the importance of CLR integration in SQL Server.
• Implement and publish CLR assemblies using SQL Server Data Tools (SSDT).
Module 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
After completing this module, you will be able to:
• Describe XML and XML schemas.
• Store XML data and associated XML schemas in SQL Server.
• Implement XML indexes within SQL Server.
• Use the Transact-SQL FOR XML statement.
• Work with basic XQuery queries.
Module 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
After completing this module, you will be able to:
• Describe how spatial data can be stored in SQL Server.
• Use basic methods of the GEOMETRY and GEOGRAPHY data types.
• Query databases containing spatial data.
Module 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
After completing this module, you will be able to:
• Describe the considerations for designing databases that incorporate BLOB data.
• Describe the benefits and design considerations for using FILESTREAM to store BLOB data on a Windows file system.
• Describe the benefits of using full-text indexing and Semantic Search, and explain how to use these features to search SQL Server data, including unstructured data.
Module 17: SQL Server Concurrency
Concurrency and Transactions
Locking Internals
Lab : SQL Server Concurrency
After completing this module, you will be able to:
• Describe concurrency and transactions in SQL Server.
• Describe SQL Server locking.
Module 18: Performance and Monitoring
Extended Events
Working with extended Events
Live Query Statistics
Optimize Database File Configuration
Metrics
Lab : Monitoring, Tracing, and Baselining
After completing this module, you will be able to:
• Understand Extended Events and how to use them.
• Work with Extended Events.
• Understand Live Query Statistics.
• Optimize the file configuration of your databases.
• Use DMVs and Performance Monitor to create baselines and gather performance metrics.