Training in SQL Server 2016 Development

Key Information

Duration: 5 Days days

Course Learning Skills: SQL 2016
Additional Material: Download Course Slides Link

Brief

Objectives This SQL Server course will enable delegates to program a database. This includes developing in Transact-SQL. The course will cover the structure of a SQL Server 2012 database. Delegates will be able to create stored procedures, triggers, functions and views. The course will cover improving database performance by indexing tables and using SQL Servers inbuilt tools. The course will also look at the new features in SQL Server 2016 that are not available in SQL Server 2012. Audience This course is designed for those who are responsible for implementing database objects and programming SQL Server databases by using Transact-SQL. Prerequisites An understanding of basic relational database concepts, including: Logical and physical database design Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many) How data is stored in tables (rows and columns)

Objectives

 – This course will enable delegates to program a SQL Server 2016 database. This includes developing in Transact-SQL. The course will cover the structure of a SQL Server 2016 database. Delegates will be able to create stored procedures, triggers, functions and views. The course will cover improving database performance by indexing tables. The will cover the new features in SQL Server 2016 that are not available in SQL Server 2012.

Duration:

4 Days

Audience

This course is designed for those who are responsible for implementing database objects and programming SQL Server databases by using Transact-SQL.

Prerequisites

An understanding of basic relational database concepts, including:

• Logical and physical database design
• Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many)
• How data is stored in tables (rows and columns)

4 Top Takeaways from the Course

1. Improve database design
2. Improve query performance
3. Make your existing data work harder for you
4. Secure data stores by restricting user access

Module 1: SQL Server 2016 Overview

1. SQL Server 2016 Components and Architecture
2. SQL Server 2016 Developer Tools
3. SQL Server 2016 Security Implementation

Module 2: Language Features

1. SQL Server Programming Tools
2. Elements of Transact-SQL
3. SQL Server Object Names
4. Additional Language Elements
5. Local Variables
6. Operators
7. Functions
8. Ways to Execute Transact-SQL Statements
9. New Transact-SQL (T-SQL) Features in 2016

Module 3: Design A Database

1. Define Entities
2. Apply Normalisation
3. Database Objects
4. System Tables
5. Defining Databases
6. Setting Database Options
7. Managing Data and Log File Growth

Module 4 Implement Tables

1. Create Tables
2. Adding and Dropping a Column
3. Generating Column Values
4. Adding Constraints

Module 5: Access and Modify Data

1. Selecting Data
2. Using Sub Queries
3. Select Queries with Joins
4. Modifying Data
5. Importing Data to Tables
6. Writing and Reading XML

Module 6: Advanced Query Techniques

1. 1.    Inner Joins
2. 2.    Outer Joins
3. 3.    Self Joins
4. 4.    Sub Queries
5. 5.    Creating Pivot Queries
6. 6.    Working with XML

Module 7: Implement Views

1. Introduction to Views
2. Advantages of Views
3. Defining Views
4. Modifying Data through Views
5. Optimizing Performance by Using Views

Module 8: Implement Functions

1. What Is a User-defined Function?
2. Defining User-defined Functions
3. Types of User-defined Functions
4. Scalar
5. Inline
6. Multi-statement

Module 9: Implement Triggers

1. Introduction to Triggers
2. Defining Triggers
3. How Triggers Work
4. Examples of Triggers
5. Performance Considerations

Module 10: Implementing Stored Procedures

1. What Is A Procedure?
2. Types of Stored Procedure
3. Creating Stored Procedures
4. Declaring Parameters
5. Set Return Value in A Stored Procedure

Module 11: Indexing Tables

1. Introduction to Indexes
2. Index Architecture
3. How SQL Server Retrieves Stored Data
4. How SQL Server Maintains Index and Heap Structures
5. Deciding Which Columns to Index

Module 12: Managing Transactions and Locks

1. Introduction to Transactions and Locks
2. Managing Transactions
3. SQL Server Locking
4. Managing Locks

Module 13: Design and Administer Security Levels

1. Design security plan
2. Administer authentication
3. Administer authorisation
4. Administer permissions
5. Administer users, groups and roles

Module 14: Monitoring and Tuning

1. Use SQL Profiler to monitor a database
2. Describe how the Index Tuning Wizard works and when to use it
3. Define database partitioning

Module 15: Programming Replication

1. Overview of SQL Server Replication
2. Replication Programming Interfaces
3. Configuring Replication
4. Synchronizing Data

Module 16: Introduction to Integration Services

1. Importing and Exporting Data
2. Integration Services Tools
3. Building a Package
4. Troubleshooting a Package

Module 17: Introduction to Reporting Services

1. Understanding Reporting Services
2. Configuring Reporting Services
3. Building a Simple Report
4. Creating, Publishing, and Viewing Reports
5. Using and Managing Published Reports

 Module 18: New Features in SQL Server 2016

1. Always Encrypted
2. Stretch Database
3. Real-time Operational Analytics
4. PolyBase into SQL Server
5. Native JSON Support
6. Enhancements to AlwaysOn
7. Enhanced In-Memory OLTP

 

 

 

 

Book an Instructor Led Course

Do a short survey to tell us what you think about training?

Click here to take the survey, it’ll only take a few minutes!