Audience profile
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.
Prerequisites
- M20761 Querying Data with Transact SQL
Course outline
INTRODUCTION TO DATABASE DEVELOPMENT
· Navigate the SQL Server Platform
· Perform SQL Server Database Development Tasks
DESIGNING & IMPLEMENTING TABLES
· Design, Create and Alter Tables
· Understand the Different Data Types
· Create and Manage Schemas
Lab: Designing and implementing tables
ADVANCED TABLE DESIGNS
· Partition and Compress Data
· Create Temporal Tables
Lab: Using advanced table designs
ENSURING DATA INTEGRITY THROUGH CONSTRAINTS
· Enforce Data Integrity
· Add and Test Constraints
· Implement Data Domain, Entity and Referential Integrity
Lab: Using Data Integrity Through Constraints
INTRODUCTION TO INDEXES
· Understand Core Indexing Concepts
· Learn about Data and Index Types – Including Heaps, Clustered, Nonclustered, Single Column and Composite Indexes
· Develop Heaps and Clustered and Covered Indexes
Lab: Implementing Indexes
DESIGNING OPTIMIZED INDEX STRATEGIES
· Learn About Index Strategies, Managing Indexes and Execution Plans
· Work with the Database Engine Tuning Advisor
· Use the Query Store to Identify and Fix Queries
Lab: Optimizing Indexes
COLUMNSTORE INDEXES
· Interpret, Create and Work with Columnstore Indexes
· Build a Memory Optimized Columnstore Table
Lab: Using Columnstore Indexes
DESIGNING AND IMPLEMENTING VIEWS
· Create and Manage Views
· Understand Performance Considerations for Views
· Set up Standard and Updateable Views
Lab: Designing and Implementing Views
DESIGNING AND IMPLEMENTING STORED PROCEDURES
· Create and Work with Stored Procedures
· Build and Implement Parameterized Stored Procedures
· Control Execution Context
Lab: Designing and Implementing Stored Procedures
DESIGNING AND IMPLEMENTING USER-DEFINED FUNCTIONS
· Understand the Types of Functions, Alternatives and Key Considerations for Implementation
· Design and Implement Scalar and Table-Valued Functions
Lab: Designing and Implementing User-Defined Functions
RESPONDING TO DATA MANIPULATION VIA TRIGGERS
· Design and Implement DML Triggers
· Learn about Advanced Trigger Concepts
· Create, Test and Improve Audit Triggers
Lab: Responding to Data Manipulation by Using Triggers
USING IN-MEMORY TABLES
· Work with Memory-Optimized Tables
· Establish Natively Compiled Stored Procedures
Lab: Using In-Memory Database Capabilities
IMPLEMENTING MANAGED CODE IN SQL SERVER
· Learn about CLR Integration in SQL Server
· Implement and Publish CLR Assemblies
Lab: Implementing Managed Code in SQL Server
STORING AND QUERYING XML DATA IN SQL SERVER
· Understand XML and XML Schemas
· Store XML Data and Schemas in SQL Server
· Implement the XML Data Type
· Use the Transact-SQL FOR XML Statement
· Get Started with XQuery and Shredding XML
Lab: Storing and Querying XML Data in SQL Server
STORING AND QUERYING SPATIAL DATA IN SQL SERVER
· Store and Query Spatial Data
· Work with SQL Server Spatial Data Types
· Apply Spatial Data to Applications
Lab: Working with SQL Server Spatial Data
STORING AND QUERYING BLOBS AND TEXT DOCUMENTS IN SQL SERVER
· Understand Considerations for Working with BLOB Data
· Store Unstructured Data Using FILESTREAM
· Perform Full-Text Searches
Lab: Storing and Querying BLOBs and Text Documents in SQL Server
SQL SERVER CONCURRENCY
· Learn how to Create Concurrency in SQL Server
· Execute Transactions and Lock Internals to Establish Concurrency
Lab: SQL Server Concurrency
PERFORMANCE AND MONITORING
· Work with Extended Events
· View and Interpret Live Query Statistics and Metrics
· Optimize Database File Configuration
Lab: Monitoring, Tracing, and Baselining
