Course Description

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.

Target Student

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.

Pre-requisites

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

Course Objectives

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

Lab : SQL Server Database Development Tasks
Module 2: Designing and Implementing Tables

• Designing Tables
• Data Types
• Working with Schemas
• Creating and Altering Tables

Lab : Designing and Implementing Tables
Module 3: Advanced Table Designs

• Partitioning data
• Compressing Data
• Temporal Tables

Lab : Using Advanced Table Designs
Module 4: Ensuring Data Integrity through Constraints

• Enforcing data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity

Lab : Ensuring Data Integrity through Constraints
Module 5: Introduction to Indexes

• Core Indexing Concepts
• Data Types and Indexes
• Single Column and Composite Indexes

Lab : Implementing Indexes
Module 6: Designing Optimized Index Strategies

• Covering Indexes
• Managing Indexes
• Execution Plans
• Using the DTE

Lab : Designing Optimized Index Strategies
Module 7: Columnstore Indexes

• Introduction to Columnstore indexes
• Creating Columnstore indexes
• Working Columnstore indexes

Lab : 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
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
Module 10: Designing and Implementing User-Defined Functions

• Overview of Functions
• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Implementation Considerations for Functions
• Alternatives to Functions

Lab : Designing and Implementing User-defined Functions
Module 11: Responding to Data Manipulation via Triggers

• Designing DML TriggersImplementing DML Triggers
• Advanced Trigger Concepts

Lab : Responding to Data Manipulation via Triggers
Module 12: Using In-Memory Tables

• In-Memory tables
• Native Stored Procedures

Lab : In Memory OLTP
Module 13: Implementing Managed Code in SQL Server

• Introduction to SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration

Lab : Implementing Managed Code in SQL Server
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 T-SQL FOR XML Statement
• Getting Started with xQuery

Lab : Storing and Querying XML Data in SQL Server
Module 15: Working with SQL Server Spatial Data

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

Lab : Working with SQL Server 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