This 5-day instructor-led course provides participants with the knowledge and skills to develop a Microsoft SQL Server 2016 database. The course focuses on teaching individuals how to use SQL Server 2016 product features and tools related to developing a database.
20762 Developing SQL Databases
20762 Developing SQL Databases
Overview
Prerequisites
Participants who wish to take up this course should meet the following prerequisites:
- Basic knowledge of the Microsoft Windows operating system and its core functionality
- Working knowledge of Transact-SQL
- Working knowledge of relational databases
Who Should Attend?
This course is highly recommended for IT professionals who want to become skilled on SQL Server 2016 product features and technologies for implementing a database.
This course is also recommended for individuals who are developers from other product platforms looking to become skilled in the implementation of a SQL Server 2016 database.
CURRICULUM
20762 Developing SQL Databases
- Introduction to the SQL Server Platform
- SQL Server Database Development Tasks
- Lab: SQL Server Database Development
- Designing Tables
- Data Types
- Working with Schemas
- Creating and Altering Tables
- Lab: Designing and Implementing Tables
- Partitioning Data
- Compressing Data
- Temporal Tables
- Lab: Using Advanced Table Designs
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
- Lab: Ensuring Data Integrity through Constraints
- Core Indexing Concepts
- Data Types and Indexes
- Heaps, Clustered, and Nonclustered Indexes
- Single Column and Composite Indexes
- Lab: Implementing Indexes
- Index Strategies
- Managing Indexes
- Execution Plans
- The Database Engine Tuning Advisor
- Query Store
- Lab: Optimizing Indexes
- Introduction to Columnstore indexes
- Creating Columnstore indexes
- Working Columnstore indexes
- Lab: Using Columnstore indexes
- Introduction to views
- Creating and managing views
- Performance consideration for views
- Lab: Designing and Implementing Views
- Introduction to Stored Procedures
- Working with Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
- Lab: Designing and Implementing Stored Procedures
- 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
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- Lab: Responding to Data Manipulation via Triggers
- Memory-Optimized Tables
- Natively Compiled Stored Procedures
- Lab: Using In-Memory Database Capabilities
- Introduction to CLR Integration in SQL Server
- Implementing and Publishing CLR Assemblies
- Lab: Implementing Managed Code 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
- Introduction to Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
- Lab: Working with SQL Server Spatial Data
- Considerations for BLOB Data
- Working with FILESTREAM
- Using Full-Text Search
- Lab: Storing and Querying BLOBS and Text Documents in SQL Server
- Concurrency and Transactions
- Locking Internals
- Lab: SQL Server Concurrency
- Extended Events
- Working with extended Events
- Live Query Statistics
- Optimize Database File Configuration
- Metrics
- Lab: Monitoring, Tracing, and Baselining