Developing Microsoft SQL Server Databases 2016
Programme
Module 1: Introduction to Database Development
- Description
- This module introduces database development and the key tasks that a database developer would typically perform
- Lessons
- Introduction to the SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
- Lab: Introduction to Database Development
- Start SQL Server Management Studio
- After completing this module, you will be able to:
- Describe the architecture and editions of SQL Server 2016
- Work with SQL Server tools
- Configure SQL Server Services
Module 2: Designing and Implementing Tables
- Description
- This module explains how to design, create, and alter tables
- Also it focusses on working with schemas
- Lessons
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
- Lab: Designing and Implementing Tables
- Improving the Design of Tables
- Creating a Schema
- Creating the Tables
- After completing this module, you will be able to:
- Design Tables
- Work with Schemas
- Create and Alter Tables
Module 3: Ensuring Data Integrity through Constraints
- Description
- This module explains how to enforce data integrity, and implement domain integrity to maintain high quality data
- Also it focusses on implementing Entity and Referential Integrity
- Lessons
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
- Lab: Ensuring Data Integrity through Constraints
- Designing Constraints
- Testing the constraints
- After completing this module, you will be able to:
- Explain the available options for enforcing data integrity and the levels at which they should be applied
- Implement domain integrity
- Implement entity and referential integrity
Module 4: Introduction to Indexing
- Description
- This module describes the concept of an index and discusses selectivity, density and statistics
- It covers appropriate data type choices and choices around composite index structures
- Lessons
- Core Indexing Concepts
- Single Column and Composite Indexes
- SQL Server Table Structures
- Working with Clustered Indexes
- Lab: Creating Indexes
- Creating Tables with Clustered Indexes
- Improving Performance through Nonclustered Indexes
- After completing this module, you will be able to:
- Describe core indexing concepts
- Choose appropriate data types for indexes
- Design and implement clustered and nonclustered indexes
Module 5: Advanced Indexing
- Description
- This module explains 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 indexingstrategies
- Lessons
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
- Designing Effective Nonclustered Indexes
- Performance Monitoring
- Lab: Planning for SQL Server 2016 Indexing
- Exploring Existing Index Statistics
- Creating Covering Indexes
- After completing this module, you will be able to:
- Describe the elements of an execution plan
- Design effective indexing strategies
- Monitor your system to assess the performance of your indexing strategy
Module 6: Columnstore Indexes
- Description
- This module explains columnstore indexes, introduces clustered and nonclustered columnstore indexes, and discusses considerations for using columnstore indexes
- Lessons
- Columnstore Indexes
- Best Practices for Columnstore Indexes
- Lab: Using In-Memory Database Capabilities
- Creating Columnstore Indexes
- After completing this module, you will be able to:
- Create columnstore indexes
- Describe the considerations for updating tables with non-clustered columnstore indexes
Module 7: Designing and Implementing Views
- Description
- This module introduces Views, and explains how to create and manage Views
- Also it focusses on the performance consideration for Views
- Lessons
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
- Lab: Designing and Implementing Views
- Designing, Implementing and Testing the WebStock Views
- Designing and Implementing the Contacts View
- Modifying the AvailableModels View
- After completing this module, you will be able to:
- Explain the role of views in database development
- Implement views
- Describe the performance related impacts of views
Module 8: Designing and Implementing Stored Procedures
- Description
- This module describes the potential advantages of the use of stored procedures along with guidelines on creating them
- Lessons
- Introduction to Stored Procedures
- Working With Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
- Lab: Designing and Implementing Stored Procedures
- Creating stored procedures
- Creating a parameterized stored procedure
- Altering the execution context of stored procedures
- After completing this module, you will be able to:
- Describe the role of stored procedures and the potential benefits of using them
- Work with stored procedures
- Implement parameterized stored procedures
- Control the execution context of a stored procedure
Module 9: Designing and Implementing User-Defined Functions
- Description
- This module explains 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 Scalar Functions
- Designing and Implementing Table-Valued Functions
- Implementation Considerations for Functions
- Alternatives to Functions
- Lab: Designing and Implementing User-Defined Functions
- Formatting Phone Numbers
- Modifying an Existing Function
- Resolving a Function-Related Performance Issue
- After completing this module, you will be able to:
- Design and implement scalar functions
- Design and implement table-valued functions
- Describe implementation considerations for functions
- Describe alternatives to functions
Module 10: Responding to Data Manipulation via Triggers
- Description
- This module, explains what DML triggers are and how they enforce data integrity
- Also it focusses on the different types of triggers available, and how to define triggers in a database
- Lessons
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- Lab: Responding to Data Manipulation via Triggers
- Creating and Testing the Audit Trigger
- Improving the Audit Trigger
- After completing this module, you will be able to:
- Design DML triggers
- Implement DML triggers
- Explain advanced DML trigger concepts
Module 11: Using In-Memory Tables
- Description
- This module covers the creation of in-memory tables and native stored procedures and discusses the advantages and disadvantages of using in-memory tables
- Lessons
- Memory-Optimized Tables
- Native Stored Procedures
- Lab: Using In-Memory Database Capabilities
- Working with Memory Optimized Tables
- Working with Natively Compiled Stored Procedures
- After completing this module, you will be able to:
- Design and implement memory-optimized tables
- Create native stored procedures
Module 12: Implementing Managed Code in SQL Server
- Description
- This module explains 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
- Lab: Implementing Managed Code in SQL Server
- Assessing Proposed CLR Code
- Implementing a CLR Assembly
- Implementing a CLR User-defined Aggregate and CLR User-defined Data Type
- After completing this module, you will be able to:
- Explain the importance of SQL Server CLR Integration
- Import and configure assemblies
- Implement objects that have been created within .NET assemblies
Module 13: Storing and Querying XML Data in SQL Server
- Description
- This module introduces 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
- Storing XML Data and Schemas in SQL Server
- Implementing the XML Data Type
- Using the T-SQL FOR XML Statement
- Getting Started with XQuery
- Shredding XML
- Lab: Storing and Querying XML Data in SQL Server
- Assessing appropriate Use of XML Data in SQL Server
- Testing XML Data Storage in Variables
- Retrieving Information about XML Schema Collections
- Querying SQL Server Data as XML
- Write a Stored Procedure Returning XML
- After completing this module, you will be able to:
- Describe XML and XML schemas
- Store XML data and associated XML schemas in SQL Server
- Implement the XML data type within SQL Server
- Use the T-SQL FOR XML Statement
- Work with basic XQuery queries
- Shred XML to a relational form
Module 14: Working with SQL Server Spatial Data
- Description
- This module introduces Spatial Data, and explains how to work with SQL Server Spatial Data Types
- Lessons
- Introduction to Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
- Lab: Working with SQL Server Spatial Data
- Querying the Geometry Data Type
- Adding Spatial Data to an Existing Table
- After completing this module, you will be able to:
- Describe the importance of spatial data and the industry standards related to it
- Explain how to store spatial data in SQL Server
- Perform calculations on and query SQL Server spatial data
