Developing Microsoft SQL Server 2014 Databases (3 days)

This instructor-led course is intended for Microsoft® SQL Server® database developers who are responsible for implementing a database on SQL Server 2014. Students will focus on the creation of database objects including views, stored procedures, along with parameters, and functions. Other common aspects of procedure coding, such as transactions, error handling, triggers, and SQL CLR are also covered in this course.

This course is intended for database professionals who need to fulfil a Database Developer role. Students should have knowledge of basic relational database concepts and writing T-SQL queries

This course requires that you meet the following prerequisites:

  • Working knowledge of Transact-SQL (ability to write Transact-SQL queries)
  • Working knowledge of relational databases (database design skills)
  • Core Windows Server skills
  • Writing Queries Using Microsoft® SQL Server® 2012/2014 Transact-SQL

After completing this course, students will be able to:

  • Describe the concepts of database development.
  • Design and implement tables.
  • Use constraints to ensure data integrity.
  • Describe indexes.
  • Implement advanced indexes.
  • Deploy in-memory database capabilities.
  • Design and implement views.
  • Design and implement stored procedures.
  • Design and implement user-defined functions.
  • Implement triggers to respond to data manipulation.
  • Deploy in-memory tables.
  • Implement managed code in SQL Server.
  • Store and query XML data in SQL Server.
  • Work with spatial data in SQL Server.
  • Incorporate data files into databases.
  • Full colour manual or book.
  • Take away exercises used in the course to practice at home.
  • USB stick to take the exercises away (where relevant).
  • Freshly prepared lunch in our onsite cafeteria.
  • Refreshments though out the day.
  • Training in fully air conditioned facilities.
  • Introduction to the SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services
  • Introduction to Database Development
  • Using Data Types
  • Working with Character Data
  • Designing Tables
  • Working with Schemas
  • Creating and Altering Tables
  • Designing and Implementing Tables
  • Enforcing Data Integrity
  • Implementing Domain Integrity
  • Implementing Entity and Referential Integrity
  • Ensuring Data Integrity through Constraints
  • Core Indexing Concepts
  • Table Structures in SQL Server
  • Single Column and Composite Indexes
  • Planning for SQL Server Indexing
  • Working with Clustered Indexes
  • Working with Nonclustered Indexes
  • Creating Indexes
  • Core Concepts of Execution Plans
  • Common Execution Plan Elements
  • Working with Execution Plans
  • Designing Effective Nonclustered Indexes
  • Performance Monitoring
  • Advanced Indexing
  • The Buffer Pool Extension
  • Columnstore Indexes
  • Using In-Memory Database Capabilities
  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views
  • Designing and Implementing Views
  • Introduction to Stored Procedures
  • Working with Stored Procedures
  • Implementing Parameterized Stored Procedures
  • Controlling Execution Context
  • Designing and Implementing Stored Procedures
  • Overview of Functions
  • Designing and Implementing Scalar Functions
  • Designing and Implementing Table-Valued Functions
  • Considerations for Implementing Functions
  • Alternatives to Functions
  • Designing and Implementing User-Defined Functions
  • Designing DML Triggers
  • Implementing DML Triggers
  • Advanced Trigger Concepts
  • Responding to Data Manipulation by Using Triggers
  • Memory-Optimized Tables
  • Natively Compiled Stored Procedures
  • Using In-Memory Database Capabilities
  • Introduction to CLR Integration in SQL Server
  • Importing and Cataloging Assemblies
  • Implementing CLR Integration in SQL Server
  • Implementing Managed Code in SQL Server
  • Introduction to XML and XML Schemas
  • Storing XML Data and XML Schemas in SQL Server
  • Implementing XML Indexes
  • Using the Transact-SQL FOR XML Statement
  • Getting Started with XQuery
  • Shredding XML
  • Storing and Querying XML Data in SQL Server
  • Introduction to Spatial Data
  • Working with Spatial Data Types in SQL Server
  • Using Spatial Data in Applications
  • Working with Spatial Data in SQL Server
  • Considerations for Working with Data Files in SQL Server 2014
  • Implementing FILESTREAM and FileTables
  • Searching Data Files
  • Implementing a Solution for Storing Data Files