|
3-Day (COURSE-DB-ADVANCEDTSQL)
Description
In this course you will learn the details and capabilities of T-SQL in the following areas: Query Tuning; Subqueries, Ranking Functions, Joins and Set Operations; Aggregating and Pivoting Data; TOP and APPLY; Data Modification; Data Type Related Problems; Programmable Objects (Dynamic SQL, Views, User Defined Functions, Stored Procedures, Triggers, Transactions and Concurrency.
During the course you will learn how to use T-SQL to solve practical problems such as: Relational Division, Ranking, Missing and Existing Values (Islands and Gaps), Separating Elements, Tie Breakers, Running Aggregations, Pivoting and Unpivoting, Custom Aggregations, Histograms, Dynamic Analysis of Grouping Sets, TOP Problems, Paging, Median, Handling Sequences, and more.
You will learn how to tune your queries, how to develop efficient routines including user defined functions, stored procedures and triggers, work in multi-user environments with transactions and isolation levels, and use dynamic SQL securely and efficiently.
With each querying/programming problem the discussions will revolve around logical aspects, set-based vs. procedural programming and optimization of the solutions.
For details on booking courses or seminars, please visit our booking page.
Author
This course was developed by Greg Low and Itzik Ben-Gan. Both are well-known mentors with SolidQ and authors of several books about T-SQL and SQL Server. Both write for SQL Server Magazine, and a regular speakers in international SQL Server related events.
Audience
- T-SQL Programmers, DBAs, Architects, and Analysts.
- Those that need to write or review T-SQL code in SQL Server 2005 and 2008.
- SQL Server developers who are responsible for developing SQL Server queries and stored procedures.
Note: This course assumes at least one year's practical experience writing T- SQL.
Prerequisites
Before attending this course, it is recommended that students have the following skills:
- Experience with SQL Server
- Understanding of database concepts
- Experience with SQL Server administration
- At least one year of T-SQL querying and programming experience in SQL Server
- Knowledge of SQL Server Performance Tuning concepts
Delivery Format
3 days, 12 modules, including exercises and labs.
Course Outline
Day 1
Module 01: Subqueries
- Nested and correlated subqueries
- Derived Tables
- Common Table Expressions
- LAB
Module 02: Joins and Set Operations
- Joins
- Join Strategies
- Set Operations
- LAB
Module 03: Ranking and Aggregating Data
- Analytical Ranking Functions
- The OVER Clause
- Running and Sliding Aggregations
- LAB
Module 04: Pivoting Data
- Pivoting and Unpivoting Data
- Custom Aggregations
- Grouping Sets
- LAB
Day 2
Module 05: TOP and APPLY
- TOP
- APPLY
- Techniques for APPLY
- LAB
Module 06: Data Modification
- Inserting, Updating, Deleting Data
- Merging Data
- OUTPUT Clause
- LAB
Module 07: Full Text Indexing
- Full Text Indexing Concepts
- Implementing Full Text Indexing
- Full Text Best Practices
- LAB
Module 08: Datatype Related Problems
- Date and Time Data Types
- Manipulating Dates
- Datatype Enhancements
- LAB
Day 3
Module 09: Programmable Objects
- Dynamic SQL
- Views , Functions, Stored Procedures and Triggers
- Table Type and Table-Valued Parameters
- LAB
Module 10: SQL Server Spatial Data
- Spatial Applications
- Spatial Data Types
- Spatial Indexing and Tuning
- LAB
Module 11: SQL CLR Integration
- SQL CLR Concepts
- Implementing SQL CLR
- Best practices for SQL CLR
- LAB
Module 12: Exception Handling
- T-SQL and Errors
- Error Handling - pre 2005
- TRY/CATCH and SQL Server 2005+
- Error Handling in SQL CLR Code
- Error Handling on Clients
- LAB
|