CA12 - Microsoft SQL Server Development - 4 Days

Course Description

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

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

Course Enquiry