Developing Microsoft SQL Server 2014 Database

Session Detail:    Classroom 5 Day

This five-day instructor-led course introduces SQL Server 2014 and describes logical table design, indexing, and query plans. It also focusses on the creation of database objects including views, stored procedures, parameters, and functions. Other common aspects of procedure coding, such as transactions, concurrency, error handling, triggers, and SQL CLR are also covered in this course. Also this course helps you prepare for the Exam 70-464.

Prerequisites

This course requires that you meet the following prerequisites:

  • Knowledge of writing T-SQL queries.
  • Knowledge of basic relational database concepts.

Who Can Benefit

The primary audience for this course is IT Professionals who want to become skilled in SQLServer 2014 product features and technologies for implementing a database.
The secondary audiences for this course are individuals who are developers from other product platforms or previous versions of SQL Server looking to become skilled in the implementation of an SQLServer 2014 database.

Session Outline

Class Outline

Module 1: Introduction to Database Development

  • Lesson 1: Introduction to the SQL Server Platform
  • Lesson 2: SQL Server Database Development Tasks

After completing this module, you will be able to:

  • Describe the architecture and editions of SQL Server 2012
  • Work with SQL Server tools
  • ConfigureSQL Server Services

Module 2: Designing and Implementing Tables

  • Lesson 1: Designing Tables
  • Lesson 2: Data Types
  • Lesson 3: Working with Schemas
  • Lesson 4: Creating and Altering Tables
  • Lesson 5: Partitioning Data
  • Lesson 6: Compressing Data

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

  • Lesson 1: Enforcing Data Integrity
  • Lesson 2: Implementing Domain Integrity
  • Lesson 3: Implementing Entity and Referential Integrity

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

  • Lesson 1: Core Indexing Concepts
  • Lesson 2: Data Types and Indexes
  • Lesson 3: Single Column and Composite 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: Designing Optimized Index Strategies

  • Lesson 1: Covering Indexes
  • Lesson 2: Managing Indexes
  • Lesson 3: Working with Execution Plans
  • Lesson 4: Using the DTE

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

  • Lesson 1: Introduction to Columnstore indexes
  • Lesson 2: Creating Columnstore Indexes
  • Lesson 3: Working with Columnstore Indexes

After completing this module, you will be able to:

  • Create column store indexes
  • Describe the considerations for updating tables with non-clustered column store indexes

Module 7: Designing and Implementing Views

  • Lesson 1: Introduction to Views
  • Lesson 2: Creating and Managing Views
  • Lesson 3: Performance Considerations for Views

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

  • Lesson 1: Introduction to Stored Procedures
  • Lesson 2: Working With Stored Procedures
  • Lesson 3: Implementing Parameterized Stored Procedures
  • Lesson 4: Controlling Execution Context

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

  • Lesson 1: Overview of Functions
  • Lesson 2: Designing and Implementing Scalar Functions
  • Lesson 3: Designing and Implementing Table-Valued Functions
  • Lesson 4: Implementation Considerations for Functions
  • Lesson 5: Alternatives to Functions

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

  • Lesson 1: Designing DML Triggers
  • Lesson 2: Implementing DML Triggers
  • Lesson 3: Advanced Trigger Concepts

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

  • Lesson 1: In-Memory Tables
  • Lesson 2: Native 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 2014

  • Lesson 1: Introduction to SQL CLR Integration
  • Lesson 2: Importing and Configuring Assemblies
  • Lesson 3: Implementing SQL CLR Integration

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

  • Lesson 1: Introduction to XML and XML Schemas
  • Lesson 2: Storing XML Data and Schemas in SQL Server
  • Lesson 3: Implementing the XML Data Type
  • Lesson 4: Using the T-SQL FOR XML Statement
  • Lesson 5: Getting Started with XQuery

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 2014 Spatial Data

  • Lesson 1: Introduction to Spatial Data
  • Lesson 2: Working with SQL Server Spatial Data Types
  • Lesson 3: Using Spatial Data in Applications

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

Module 15: Incorporating Data Files into Databases

  • Lesson 1: Querying Data with Stored Procedures
  • Lesson 2: Implementing FILESTREAM and File Tables
  • Lesson 3: Searching Data Files

Need to train your team?

All of our sessions can be customized to meet your team’s specific need. Build the perfect program by picking and choosing topics from any of the courses in our catalog. A personalized private session gives you the ultimate flexibility and helps maximize your team’s valuable time!

Requesting Team Training
DateTimeTypePriceAdd To Cart

Developing Microsoft SQL Server 2014 Database

5 Day
Classroom

$3,095.00

Chat with a Coach

Chat with a Coach

Have a more immediate need? Why spend the next hour searching online for answers when you can spend just 15 minutes with one of our experts and get accurate and personalized answers to all of your questions. 

$9.99 | 15 Minutes

Book Now