SQL Advanced – Improving Productivity, Performance and Security

Course Length: 2 Days

Learning Objectives:
  • Understand basic relational database design principles and how tables and other objects are created and maintained
  • Use a wide variety of tools and techniques to improve performance of complex queries on large databases
  • Follow best practices to improve security of data
  • Create and manage complex views and handle updating of views, the Check Option, materialized views, and virtual columns
  • Create and manage indexes, including knowing when to create and when not to create indexes, and the use of partitioning, clustering, filtered, full-text, and other types of indexes
  • Create stored procedures, including the use of required and optional parameters, variables, conditional statements, looping, cursors, exception handling, transactions, and debugging
  • Create user-defined functions, including the use of parameters and all language features; create scalar and table functions and understand the differences between table functions, stored procedures, and views
  • Create triggers, including table, database and server triggers, before, instead of, and after triggers, handling various events, using the "transition" data involved, and dealing with transaction issues
Target Student:
This course is designed for application developers.

Prerequisites :
Before taking this course, students should take SQL Basics and Intermediate or have equivalent work experience and have several weeks of experience using SQL.

Course Outline:
Section 1: Introduction
  • The basics of relational database design
  • The basics of maintenance
  • Tools for diagnosing and improving performance
  • Best practices for improving security
Section 2: View Issues
  • Overview/review of creating views
  • Updatability of views
  • The Check option
  • Materialized views
  • Virtual columns
Section 3: Index Issues
  • Overview/review of indexes
  • Indexing for performance
  • Special index types
  • Partitioning
  • Clustering
  • Index-organized tables
  • Filtered
  • Full-text
Section 4: Stored Procedures
  • Overview/review of Microsoft's Transact-SQL, Oracle's PL/SQL, and other procedure languages
  • Parameters: input, output, optional parameters with default values
  • Variables, conditional statements, looping
  • Returning a result set
  • Exception handling
  • Transaction handling
  • Debugging
  • Overloading
Section 5: User-Defined Functions
  • Scalar functions: parameters, default values
  • Table functions and comparison to views
Section 6: Triggers
  • Triggers compared to stored procedures or user-defined functions
  • Triggers compared to column constraints or application code
  • Uses of triggers
  • Defining a trigger
  • Specifying a table-level, database-level, or server-level trigger
  • Before, Instead Of, and After triggers
  • Specifying the table (and possibly column(s)) controlled by the trigger
  • Specifying the event(s) that fire the trigger
  • Handling the old (Deleted) and new (Inserted) data values
  • Handling commit/rollback issues in triggers
  • Nested and recursive triggers

This Course Comes With

PDF Manual

Take the manual anywhere and save the environment with an electronic copy of your training manual for use on any of your computers or devices

Customize Your Course

Tailored Training

Your manual will follow a standard course outline but your class can be tailored to focus on the subjects that are most important to you

Delivered by Our Trusted Partner Protech
Date(s) Price Qty
May 24 & 25, 2022 10am to 5pm EST $1,440.00 (CAD)*  
September 6 & 7, 2022 10am to 5pm EST $1,440.00 (CAD)*  
December 19 & 20, 2022 10am to 5pm EST $1,440.00 (CAD)*  
April 11 & 12, 2023 10am to 5pm EST $1,440.00 (CAD)*  

* 13% HST will be applied on checkout

Online registration closes two business days before the start of the class.
Do you have questions about this course? Would you like a class sooner or on a different date? Are you interested in scheduling a private group class? Please submit the simple form below and we will respond promptly.
Private Training for GroupsIf you are training 4+ people, a private training session may be more cost-effective. Click to contact us today and learn more.
Register for this class here
Learn more about each course here
Contact us for more information here