20464D: Developing Microsoft SQL Server Databases

Module 1: Introduction to Database Development

This module explains the database development and the key tasks that a database developer would typically perform.

Lessons

• SQL Server Platform Introduction
  
• SQL Server Database Development Tasks
  
  

Module 2: Designing and Implementing Tables

This module focuses on working with schemas and explains how to design, create, and alter tables. Also it.

Lessons

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

Module 3: Ensuring Data Integrity through Constraints

This module focusses on implementing Entity, Referential Integrity and describes how to enforce data integrity, and implement domain integrity to maintain high-quality data.

Lessons

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

Module 4: Introduction to Indexing

This module explains the concept of an index and discusses selectivity, density, and statistics.

Lessons

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

Module 5: Designing Optimized Index Strategies

This module describes covering indexes and the INCLUDE clause as well as the use of padding, hints, and statistics. The module also covers the use of the Database Engine Tuning Advisor and index related dynamic management views to assess indexing strategies.

Lessons

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

Module 6: Columnstore Indexes

This module describes Columnstore indexes and how to use them to maximize performance and scalability of database applications.

Lessons

• Columnstore indexes Introduction
  
• Creating Columnstore Indexes
  
• Working with Columnstore Indexes
  
  

Module 7: Designing and Implementing Views

This module focuses on the performance consideration for Views and describes Views, and explains how to create and manage Views.

Lessons

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

Module 8: Designing and Implementing Stored Procedures

This module explains the potential advantages of the use of stored procedures along with guidelines on creating them.

Lessons

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

Module 9: Designing and Implementing User-Defined Functions

This module describes how to design and implement user-defined functions that enforce business rules or data consistency, and modify and maintain existing functions written by other developers.

Lessons

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

Module 10: Responding to Data Manipulation via Triggers

This module describes DML triggers and how they enforce data integrity.

Lessons

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

Module 11: Using In-Memory Tables

This module describes the creation of in-memory tables and native stored procedures and explains the advantages and disadvantages of using in-memory tables.

Lessons

• In-Memory Tables
  
• Native Stored Procedures
  
  

Module 12: Implementing Managed Code in SQL Server 2014

This module describes how to use CLR integrated code to create user-defined database objects that are managed by the .NET Framework.

Lessons

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

Module 13: Storing and Querying XML Data in SQL Server

This module describes XML and shows how XML data can be stored within SQL Server and then queried, including queries written in a language called XQuery.

Lessons

• Introduction to XML and XML Schemas
  
• Implementing the XML Data Type
  
• Storing XML Data and Schemas in SQL Server
  
• Using the T-SQL FOR XML Statement
  
• Getting Started with XQuery
  
  

Module 14: Working with SQL Server 2014 Spatial Data

This module explains Spatial Data and describes how to work with SQL Server Spatial Data Types.

Lessons

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

Module 15: Incorporating Data Files into Databases

Lessons

• Querying Data with Stored Procedures
  
• Implementing FILESTREAM and File Tables
  
• Searching Data Files