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
- Improve database design
- Improve query performance
- Make your existing data work harder for you
- Secure data stores by restricting user access
Module 1: SQL Server 2016 Overview
- SQL Server 2016 Components and Architecture
- SQL Server 2016 Developer Tools
- SQL Server 2016 Security Implementation
Module 2: Language Features
- SQL Server Programming Tools
- Elements of Transact-SQL
- SQL Server Object Names
- Additional Language Elements
- Local Variables
- Operators
- Functions
- Ways to Execute Transact-SQL Statements
- New Transact-SQL (T-SQL) Features in 2016
Module 3: Design A Database
- Define Entities
- Apply Normalisation
- Database Objects
- System Tables
- Defining Databases
- Setting Database Options
- Managing Data and Log File Growth
Module 4 Implement Tables
- Create Tables
- Adding and Dropping a Column
- Generating Column Values
- Adding Constraints
Module 5: Access and Modify Data
- Selecting Data
- Using Sub Queries
- Select Queries with Joins
- Modifying Data
- Importing Data to Tables
- Writing and Reading XML
Module 6: Advanced Query Techniques
- 1. Inner Joins
- 2. Outer Joins
- 3. Self Joins
- 4. Sub Queries
- 5. Creating Pivot Queries
- 6. Working with XML
Module 7: Implement Views
- Introduction to Views
- Advantages of Views
- Defining Views
- Modifying Data through Views
- Optimizing Performance by Using Views
Module 8: Implement Functions
- What Is a User-defined Function?
- Defining User-defined Functions
- Types of User-defined Functions
- Scalar
- Inline
- Multi-statement
Module 9: Implement Triggers
- Introduction to Triggers
- Defining Triggers
- How Triggers Work
- Examples of Triggers
- Performance Considerations
Module 10: Implementing Stored Procedures
- What Is A Procedure?
- Types of Stored Procedure
- Creating Stored Procedures
- Declaring Parameters
- Set Return Value in A Stored Procedure
Module 11: Indexing Tables
- Introduction to Indexes
- Index Architecture
- How SQL Server Retrieves Stored Data
- How SQL Server Maintains Index and Heap Structures
- Deciding Which Columns to Index
Module 12: Managing Transactions and Locks
- Introduction to Transactions and Locks
- Managing Transactions
- SQL Server Locking
- Managing Locks
Module 13: Design and Administer Security Levels
- Design security plan
- Administer authentication
- Administer authorisation
- Administer permissions
- Administer users, groups and roles
Module 14: Monitoring and Tuning
- Use SQL Profiler to monitor a database
- Describe how the Index Tuning Wizard works and when to use it
- Define database partitioning
Module 15: Programming Replication
- Overview of SQL Server Replication
- Replication Programming Interfaces
- Configuring Replication
- Synchronizing Data
Module 16: Introduction to Integration Services
- Importing and Exporting Data
- Integration Services Tools
- Building a Package
- Troubleshooting a Package
Module 17: Introduction to Reporting Services
- Understanding Reporting Services
- Configuring Reporting Services
- Building a Simple Report
- Creating, Publishing, and Viewing Reports
- Using and Managing Published Reports
Module 18: New Features in SQL Server 2016
- Always Encrypted
- Stretch Database
- Real-time Operational Analytics
- PolyBase into SQL Server
- Native JSON Support
- Enhancements to AlwaysOn
- Enhanced In-Memory OLTP
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!