Microsoft SQL Server 2014 – Developing Databases: New Features

This course is included in our On-demand training solution.


Microsoft SQL Server 2014 builds from version 2012 to provide optimal performance for applications using In-Memory OLTP, which is a memory-optimized database engine integrated into the SQL Server engine. SQL Server 2014 also provides the ability to create hybrid platform and cloud scenarios by enabling cloud backups and recoveries to decrease on premise costs for storage. This course will discuss and demonstrate the objectives for SQL Server 2014 for the 70-464 Microsoft SQL Server: Developing Databases certification exam, this will include the In-Memory OLTP feature and how to write scripts to automate backups to the cloud. It will also demonstrate other database development tasks, such as designing and creating database objects and programming objects and how to optimize and troubleshoot queries.

Target Audience

Database Administrator and developers who create and manage databases within their organization as part of their daily tasks. This can include creating the database objects, ensuring their security, implementing and ensuring data integrity, managing transactions and locks, and ensuring the server instance and database are running efficiently.



Expected Duration

120 min.

Course Objectives

Course Introduction

Creating In-Memory OLTP Tables in SQL Server 2014

  • create in-memory OLTP tables
  • Troubleshooting In-Memory OLTP in SQL Server 2014

  • troubleshoot In-Memory OLTP issues
  • Monitoring In-Memory OLTP Resources in SQL Server 2014

  • monitor In-Memory OLTP issues
  • Automating Backups using Scripts in SQL Server 2014

  • create scripts to automate backups
  • Creating Calculated Columns in SQL Server 2014 Tables

  • create calculated columns in tables
  • Implementing Constraints in SQL Server 2014

  • implement cascading deletes
  • Creating Indexed Views in SQL Server 2014

  • create indexed views
  • Creating Aligned Indexes in SQL Server 2014

  • create a aligned index on a partitioned table
  • Implementing XML Selective Indexes in SQL Server 2014

  • create XML selective indexes
  • Managing Transactions in SQL Server 2014

  • manage transactions in a given scenario
  • Implementing Error Handling in SQL Server 2014

  • implement error handling using TRY, CATCH, and THROW
  • Designing Objects for Concurrency in SQL Server 2014

  • design for maximize concurrency and transaction isolation
  • Designing a Locking Strategy in SQL Server 2014

  • design index locking properties and transactions to minimize locking
  • Designing Query Paging in SQL Server 2014

  • design query paging, including OFFSET and FETCH
  • Designing SET-Based Operations in SQL Server 2014

  • create scripts that use SET based operations
  • Implementing Plan Guides in SQL Server 2014

  • create a plan guide
  • Optimizing Queries in SQL Server 2014

  • optimize queries for better performance
  • Troubleshooting SQL Server 2014 Database Size Issues

  • manage contention and auto growth in a database
  • Implementing Resource Governor in SQL Server 2014

  • implement Resource Governor to manage resources in a database
  • Using Extended Events in SQL Server 2014

  • review Extended Event logs and optimize Extended Event session settings
  • Using Activity Monitor in SQL Server 2014

  • use Activity Monitor
  • Integrating Data with SQL Traces in SQL Server 2014

  • integrate performance monitor data with SQL Traces