Description

This training will teach you how to develop databases with SQL Server 2014/2012. You will see how to create database objects including views, stored procedures as well as parameters and functions. This course covers all the topics necessary to prepare for the Microsoft 70-464 exam.

Who is this training for ?

For whom ?

Developers.

Prerequisites

Knowledge of basic relational database concepts

Training objectives

create databases with SQL Server 2014/2012

Develop stored procedures and functions

Know how to use indexes to guarantee database performance

Enter an educational objective here

Training program

• Introduction to the database
  • The architecture of the SQL Server platform.
  • Tools and services.
  • Practical work Getting started with SQL Server Management Studio .
• Tables and diagrams
  • SQL Server tools and services.
  • Design tables.
  • Work with schemas.
  • Practical work Create and modify tables.
• Data integrity and indexing
  • How to ensure data integrity? Referential and entity integrity.
  • The principles of indexing.
  • The structures of SQL Server table.
  • Cluster indexes.
  • Execution plans.
  • Monitor the system to evaluate the performance of the indexing strategy.
  • Presentation of columnstore indexes.
  • Practical work Constraint analysis.
  • Create tables with clustered indexes.
  • Create columnstore index.
• Views and Stored Procedures
  • Presentation of the view concept.
  • Stored procedures.
  • Parameterizable stored procedures.
  • Control the execution context.
  • Practical work Create views.
  • Develop stored procedures.
• Functions and triggers
  • Introduction to functions.
  • Scalar functions and table functions.
  • What are DML triggers? Advanced concepts on triggers.
  • Practical work Create a new function and modify an existing one.
  • Create and test a trigger.
• In-memory tables and managed code in SQL Server
  • Advantages and disadvantages of using in-memory tables.
  • Native stored procedures.
  • Introduction to SQL CLR integration.
  • Import and configure assemblies.
  • Practical work Working with memory-optimized tables.
  • Implementing CLR SQL integration.
• Managing XML data in SQL server
  • Overview of XML.
  • How to store XML data and schema in SQL Server.
  • The T-SQL FOR XML statement.
  • The XQuery query language.
  • Practical work Store XML data then execute a query in XML format.
• Spatial data and SQL Server
  • What is spatial data? SQL Server and spatial data.
  • How to integrate spatial data into applications? Practical work Add spatial data to an existing table.