Microsoft SQL Server Development

This course will enable delegates to program a SQL Server database. This includes developing in Transact-SQL. Along with developing databases in the Azure cloud. The course will cover the structure of a SQL Server database. Delegates will be able to create stored procedures, triggers, functions and views. The course will cover improving database performance by indexing tables. They will cover the new features in the latest version of SQL Server.

5 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
5. Work with data in the cloud or on-premises

The modules are as follows:

Module 1: SQL Server Overview
SQL Server Components and Architecture
SQL Server Developer Tools
SQL Server 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

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
Inner Joins
Outer Joins
Self Joins
Sub Queries
Creating Pivot Queries
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
Always Encrypted
Stretch Database
Real-time Operational Analytics
PolyBase into SQL Server
Native JSON Support
Enhancements to AlwaysOn
Enhanced In-Memory OLTP

Module 19: Working with Azure SQL Databases
Creating an Azure SQL Database
Connecting to an Azure SQL Server
Exporting data from an on-premises database
Querying an Azure SQL Database

Virtual Live Classroom

• Enquire for dates

Who’s it for?

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

What are the Course Requirements?

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)

Are any Financial Options Provided?

• 0% Interest Free payments available.

Reward

Informal assessment will take place through group discussion, class room activities, and questions and answers sessions as guided by your tutor. Course Completion Certificate will be awarded on successful completion of the course.

What is provided with the Course?

• Course Material
• Aftercare Course Support