Developing SQL Databases – Advanced course

Start Dates: TBC

Duration: 5 days

Full Fee: €1000

Network Members Fee: €700

https://www.ifsskillnet.ie Book Now

Programme overview

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.

Learning outcomes:

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

Who is the course for?

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.

Course Content:

1 – Introduction to Database Development

Introduction to the SQL Server Platform

SQL Server Database Development Tasks

2 – Designing & Implementing Tables

Designing Tables

Data Types

Working with Schemas

Creating and Altering Tables

Lab: Designing and Implementing Tables

3 – Advanced Table Designs

Partitioning data

Compressing Data

Temporal Tables

Lab: Using Advanced Table Designs

4 – Ensuring Data Integrity through Constraints

Enforcing data Integrity

Implementing Domain Integrity

Implementing Entity and Referential Integrity

Lab: Using Data Integrity Through Constraints

5 – Introduction to Indexes

Core Indexing Concepts

Data Types and Indexes

Heaps, Clustered, and Nonclustered Indexes

Single Column and Composite Indexes

Lab: Implementing Indexes

6 – Designing Optimized Index Strategies

Index Strategies

Managing Indexes

Execution Plans

The Database Engine Tuning Advisor

Query Store

Lab: Optimizing Indexes

7 – Columnstore Indexes

Introduction to Columnstore Indexes

Creating Columnstore Indexes

Working with Columnstore Indexes

Lab: Using Columnstore Indexes

8 – Designing and Implementing Views

Introduction to Views

Creating and Managing Views

Performance Considerations for Views

Lab: Designing and Implementing Views

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

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

11 – Responding to Data Manipulation via Triggers

Designing DML Triggers

Implementing DML Triggers

Advanced Trigger Concepts

Lab: Responding to Data Manipulation by Using Triggers

12 – Using In-Memory Tables

Memory-Optimized Tables

Natively Compiled Stored Procedures

Lab: Using In-Memory Database Capabilities

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

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

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

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

17 – SQL Server Concurrency

Concurrency and Transactions

Locking Internals

Lab: SQL Server Concurrency

18 – Performance and Monitoring

Extended Events

Working with extended Events

Live Query Statistics

Optimize Database File Configuration

Metrics

Lab: Monitoring, Tracing, and Baselining

Trainer Profile

Please contact info@ifsskillnet.ie for further information on training providers.