Curso 20762: Developing SQL Databases

Descripción

This five-day instructor-led course provides students 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.

Objetivos

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.

Contenidos del curso

Module 1: Introduction to Database Development

 

• Introduction to the SQL Server Platform
• SQL Server Database Development Tasks

Module 2: Designing and Implementing Tables

 

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

Module 3: Advanced Table Designs

 

• Partitioning Data
• Compressing Data
• Temporal Tables

Module 4: Ensuring Data Integrity through Constraints

 

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

Module 5: Introduction to Indexes

 

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

Module 6: Designing Optimized Index Strategies

 

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

Module 7: Columnstore Indexes

 

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

Module 8: Designing and Implementing Views

 

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

Module 9: Designing and Implementing Stored Procedures

 

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context

Module 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

Module 11: Responding to Data Manipulation via Triggers

 

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

Module 12: Using In-Memory Tables

 

• Memory-Optimized Tables
• Natively Compiled Stored Procedures

Module 13: Implementing Managed Code in SQL Server

 

• Introduction to CLR Integration in SQL Server
• Implementing and Publishing CLR Assemblies

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 Transact-SQL FOR XML Statement
• Getting Started with XQuery
• Shredding XML

Module 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

Module 16: Storing and Querying BLOBs and Text Documents in SQL Server

• Considerations for BLOB Data
• Working with FILESTREAM
• Using Full-Text Search

Module 17: SQL Server Concurrency

 

• Concurrency and Transactions
• Locking Internals

Module 18: Performance and Monitoring

 

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