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.

Upon completing this course, the student will be able to meet these objectives:

• 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
• Implement Managed Code in SQL Server
• Store and Query XML Data
• Work with Spatial Data
• Store and Query Blobs and Text Documents

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.

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

Module 1: Designing and Implementing Tables

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

Lab : Designing and Implementing Tables

• Designing Tables
• Creating Schemas
• Creating Tables

Module 2: Advanced Table Designs

• Partitioning Data
• Compressing Data
• Temporal Tables

Lab : Using Advanced Table Designs

• Partitioning Data
• Compressing Data

Module 3: Ensuring Data Integrity through Constraints

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

Lab : Using Data Integrity Through Constraints

• Add Constraints
• Test the Constraints

Module 4: Introduction to Indexes

• Core Indexing Concepts
• Data Types and Indexes
• Heaps, Clustered, and Nonclustered Indexes
• Single Column and Composite Indexes

Lab : Implementing Indexes

• Creating a Heap
• Creating a Clustered Index
• Creating a Covered Index

Module 5: Designing Optimized Index Strategies

• Index Strategies
• Managing Indexes
• Execution Plans
• The Database Engine Tuning Advisor
• Query Store

Lab : Optimizing Indexes

• Using Query Store
• Heaps and Clustered Indexes
• Creating a Covered Index

Module 6: Columnstore Indexes

• Introduction to Columnstore Indexes
• Creating Columnstore Indexes
• Working with Columnstore Indexes

Lab : Using Columnstore Indexes

• Creating a Columnstore Index
• Create a Memory Optimized Columnstore Table

Module 7: Designing and Implementing Views

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views

Lab : Designing and Implementing Views

• Creating Standard Views
• Creating an Updateable view

Module 8: 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

• Create Stored procedures
• Create Parameterized Stored procedures
• Changes Stored Procedure Execution Context

Module 9: 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

• Format Phone numbers
• Modify an Existing Function

Module 10: Responding to Data Manipulation via Triggers

• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts

Lab : Responding to Data Manipulation by Using Triggers

• Create and Test the Audit Trigger
• Improve the Audit Trigger

Module 11: Using In-Memory Tables

• Memory-Optimized Tables
• Natively Compiled Stored Procedures

Lab : Using In-Memory Database Capabilities

• Using Memory-Optimized Tables
• Using Natively Compiled Stored procedures

Module 12: 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

• Assessing Proposed CLR Code
• Creating a Scalar-Valued CLR Function
• Creating a Table Valued CLR Function

Module 13: 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

• Determining when to use XML
• Testing XML Data Storage in Variables
• Using XML Schemas
• Using FOR XML Queries
• Creating a Stored Procedure to Return XML

Module 14: 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

• Become Familiar with the Geometry Data Type
• Add Spatial Data to an Existing Table
• Find Nearby Locations

Module 15: 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

• Enabling and Using FILESTREAM Columns
• Enabling and Using File Tables
• Using a Full-Text Index

Module 16: SQL Server Concurrency

• Concurrency and Transactions
• Locking Internals

Lab : SQL Server Concurrency

• Implement Snapshot Isolation
• Implement Partition Level Locking

Module 17: Performance and Monitoring

• Extended Events
• Working with extended Events
• Live Query Statistics
• Optimize Database File Configuration
• Metrics

Lab : Monitoring, Tracing, and Baselining

• Collecting and Analyzing Data Using Extended Events
• Implementing Baseline Methodology