(Retired – no longer offered as a public class)

Overview

From developers to DBAs, everyone who codes against SQL Server databases use Transact-SQL in one way or another. This four-day class covers Transact-SQL and only Transact-SQL in both its individual SQL statement and procedural code (stored procedures, functions, triggers) forms, on SQL Server versions 2005-2012.

You will learn how to properly phrase SQL queries and use the right T-SQL constructs and data types for best performance, enforced by viewing and comparing query plans. You’ll also learn best practices for some of SQL Server’s enhancements to SQL that allow you to recognize, diagnose, and solve knotty problems in your own application, including dealing with data modification statements, summarization, windowing, and analysis queries as well as complex problem solving with hierarchies and entity attribute values. We’ll target appropriate data type usage, writing effective queries, summarization and analysis, optimizing procedural code (stored procedures, user-defined functions and triggers), best practices using action statements, as well as how to solve some of the more difficult problems in relational databases. 

This class is based on real-world problems and programming challenges, not academic scenarios, and the instructor, Bob Beauchemin, is a recognized expert in T-SQL development. It’s chock-full of practical knowledge which will be immediately applicable in your work environment. Also feel free to BYOP (bring your own problem)!

Prerequisites: Basic Transact-SQL and procedural code knowledge

Instructor: Bob Beauchemin

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Curriculum

Module 1: Data Types, Functions and Operators, and Scaling

This module covers the data types that are available in SQL Server from a design and usage point of view.  Also covers the T-SQL functions that apply to different data types as well as data integrity and uniqueness. Finally, this module covers how to scale out a SQL Server database. Topics covered include:

• Low-level coverage of basic data types
• Extended data types
• System functions and special variables
• FILESTREAM storage and FileTables
• Identity, sequences, uniqueidentifier, and enforcing uniqueness
• Data Integrity and nullability
• Designing for scale out

Module 2: Writing Effective Queries

This module starts out with an overview of the SQL language, illustrating best practices. Evaluating a query for syntactic correctness comes next, because knowing the parts of a SQL query helps to avoid most SQL coding errors. We cover set-based processing and query plan concepts here, to set up demonstrating how syntax features affect query plans in the remainder of the course. Topics covered include:

• JOINs in detail
• Query evaluation and query plan constructs
• Parallelism
• Relational operators – UNION, EXCEPT, INTERSECT, and relational division
• CROSS APPLY and OUTER APPLY
• Common Table Expressions
• Rewriting queries for best performance

Module 3: Summarization and Analysis

This module covers T-SQL functions that are used to summarize and group rows, with special attention paid to SQL Server windowing functions. We’ll show how each language feature affects query plans. Topics covered include:

• Aggregates – built in and user-defined
• ROLLUP, CUBE and GROUPING SETS
• Columnstore and aggregation
• Windowing and the OVER clause
• Window offset functions, ranking, and analytic functions
• TOP clause and OFFSET/FETCH
• PIVOT operator

Module 4: Optimizing Procedural Code – Stored Procedures

This module covers the things a programmer can be aware of in order to write better performing stored procedures. We cover all of the programmer-controllable areas that can result in slow and sub-optimal code. Topics covered include:

• Stored procedure background
• Choosing the correct table structures – temp table vs. table variables
• When and how to optimize code and queries
• Plan selections, statistics, and indexes
• Query processing and execution – observing and troubleshooting
• Dynamic SQL
• Parameterization and plan reuse best practices
• Exception handling
• Concurrency control – avoiding deadlocks

Module 5: Optimizing User-Defined Functions and Triggers

Each of the three types of user-defined function has its special usage, but user-defined functions are not stored procedures. Triggers can be used to enforce data or business rules and to track what has happened or to change what has happened. In this module, we investigate the best usage of these features. Topics covered include:

• UDFs vs. stored procedures
• User-defined function implementation and optimization
• Query plans produced by user-defined functions
• Invoking modules from triggers
• Trigger-specific convenience functions
• INSTEAD OF triggers

Module 6: Best practices with Action Statements

This module covers features of T-SQL verbs that change data. The newer additions that enlarge the scope of action statements as well as investigating execution plans are included. We’ll also cover the ways to insert data in bulk and optimize this function. Topics covered include:

• Row Constructors
• Set-based INSERTs
• UPDATE – updating via JOINs and CTEs
• DELETE statement – DELETE vs TRUNCATE TABLE
• MERGE statement – INSERT/UPDATE/DELETE in one statement
• Optimizing action statements with TOP clauses
• OUTPUT from action statements and composable T-SQL
• Inserting in bulk

Module 7: Solving Difficult Problems in Relational Databases

The module covers, in detail, the two most difficult problems in relational development: Hierarchies and Sparse Attributes. We’ll pay special attention to additions to T-SQL Server in newer versions to assist these designs. The topics in this module are covered if time permits. Topics include:

• Hierarchical Data – Adjacency Column, Path Enumeration, and. Nested Sets designs
• Recursive Common Table Expressions and adjacency column
• The HierarchyID data type – an implementation of the path enumeration model as built-in type
• Modeling sparse attributes – sparse table vs. sparse columns vs. Entity-Attribute-Value vs. XML
• Entity attribute value design and the PIVOT verb
• Declarative sparse column support

Questions?

If you have any questions, please contact us.