Developing SQL Databases

Session Detail:    Classroom, Virtual 5 Day

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.

Prerequisites

  • Basic knowledge of the Microsoft Windows operating systemand its core functionality.
  • Working knowledge of Transact-SQL.
  • Working knowledge of relational databases.

Who Can Benefit

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.

Session Outline

Class Outline

Module 1: Introduction to Database Development

  • Lesson 1: Introduction to the SQL Server Platform
  • Lesson 2: 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

  • Lesson 1: Designing Tables
  • Lesson 2: Data Types
  • Lesson 3: Working with Schemas
  • Lesson 4: Creating and Altering Tables

After completing this module, you will be able to:

  • Design tables using normalization, primary and foreign keys
  • Work with identity columns
  • Understand the 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

  • Lesson 1: Partitioning Data
  • Lesson 2: Compressing Data
  • Lesson 3: Temporal Tables

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

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

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

  • 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

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

  • Lesson 1: Index Strategies
  • Lesson 2: Managing Indexes
  • Lesson 3: Execution Plans
  • Lesson 4: The Database Engine Tuning Advisor
  • Lesson 5: Query Store

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 the Database Tuning Advisor to improve the performance of queries
  • How to use the Query Store to improve query performance

Module 7: Columnstore Indexes

 

  • Lesson 1: Introduction to Columnstore Indexes
  • Lesson 2: Creating Columnstore Indexes
  • Lesson 3: Working with Columnstore Indexes

After completing this module, you will be able to:

  • Describe column store indexes and identify suitable scenarios for their use
  • Create clustered and nonclustered column store indexes
  • Describe considerations for using column store indexes

 

Module 8: Designing and Implementing Views

  • Lesson 1: Introduction to Views
  • Lesson 2: Creating and Managing Views
  • Lesson 3: Performance Considerations for 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

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

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

  • 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

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

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

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

 

  • Lesson 1: Memory-Optimized Tables
  • Lesson 2: Natively Compiled Stored Procedures

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

  • Lesson 1: Introduction to CLR Integration in SQL Server
  • Lesson 2: Implementing and Publishing CLR Assemblies

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

  • Lesson 1: Introduction to XML and XML Schemas
  • Lesson 2: Storing XML Data and Schemas in SQL Server
  • 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

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

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

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

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

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

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

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

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

After completing this module, you will be able to:

  • Understand Extended Events and how to use them
  • Work with Extended Events
  • Understand Live QueryStatistics
  • Optimize the file configuration of your databases
  • Use DMVs and performance Monitor to create baselines and gather performance metrics

Need to train your team?

All of our sessions can be customized to meet your team’s specific need. Build the perfect program by picking and choosing topics from any of the courses in our catalog. A personalized private session gives you the ultimate flexibility and helps maximize your team’s valuable time!

Requesting Team Training
DateTimeTypePriceAdd To Cart

Developing SQL Databases

5 Day
Classroom, Virtual

$3,095.00

Chat with a Coach

Chat with a Coach

Have a more immediate need? Why spend the next hour searching online for answers when you can spend just 15 minutes with one of our experts and get accurate and personalized answers to all of your questions. 

$9.99 | 15 Minutes

Book Now