Advanced TSQL
19th March 2013 - 20th March 2013
Who is it for?
This course designed for SQL Server DBAs and developers with at least one year's experience of writing TSQL.
What you will learn
What is advanced TSQL? It is not just knowing the syntax of the less commonly used statements and functions. It is much more how to apply your TSQL knowledge to the problem.
TSQL is a declarative language; this means that we describe our problem and the engine performs the operation in the best way that it sees fit. Mostly this all works as expected and you get the correct results in a timely fashion. Problems commonly arise though when scaling systems: an operation that is simply inefficient with a hundred rows can be catastrophic when run against a million rows. Not only is that single operation running slowly, but because the server is spending precious resources servicing that query everyone else has to suffer too.
In this two day course we will initially be ‘lifting the bonnet’ on the SQL Server engine to allow attendees to determine how the engine operates and how to apply this new found knowledge to TSQL code. Once an understanding of these principles has been established, we need to ensure that multiple users and able to perform multiple operations on the same system, with little or no impact upon each other.
DAY 1
Internals
- Statistics
- Query optimizer
- Execution plans
- Join operations
- Statement Cache
- Compilation and Re-Compilation
- Temporary table & Table vars
Design – best practices
- Extensible schemas
- SET options
-
Indexing
- Clustered vs Non-clustered
- Optimization
- Filtered Indexes
- Sargability
- Search predicates & indices
- Views & Indexed views
- BLOB data
Transactions
- ACID principles
- Isolation levels & their impact
- Concurrency
- Locking, Blocking and Deadlocking
- NOLOCK <> go faster button
DAY 2
TSQL
- Set based operations
- Ranking functions
- Common table expressions
- APPLY
- User defined functions
- Cursors
- MERGE
- Dynamic SQL
TSQL in the real world
- Debugging
- Esoteric TSQL Techniques
Service broker
- Queuing operations
- Internal & External activation
SQL Server 2012 – TSQL Enhancements
- Windowing functions
- Analytic functions
- Metadata discovery
Key benefits
Upon completion of this course attendees will have an understanding of the SQL Server engine and the importance of the data and metadata that it operates with. This will enable them to develop well performing and scalable SQL Server based solutions, and analyse and fix issues with existing systems.
"Dave walks through real world examples explaining how to use T-SQL effectively to reduce calls to the database and therefore get the most out of storage sub-systems. He also takes times to carefully dissect common mistakes made by inexperienced developers illustrating how they can lead to real world problems with both database performance and data integrity. After the course Developers and DBAs who attended have been able to successfully refactor legacy stored procedures, views and triggers to successfully reduce blocking, deadlocks and performance issues. Overall the level of training provided was excellent, and for what could be seen as a dry subject is presented by Dave in a humorous manner that keeps everyone engaged and interested."
Les Reading, Database Manager at ASOS
Trainer
Dave Ballantyne is a freelance SQL Server database developer / designer and has been working in the IT field for over 20 years, the past 15 of which has been specialising within SQL Server environment. Dave regularly contributes to online forums and is a regular speaker at events such as SQLBits and user groups.
legal disclaimer
Cancellations must be submitted in writing, either via email or by post. Registrants whose cancellations are received at least 2 weeks before the beginning of the course are entitled to a full refund minus a £80 processing fee. No refunds will be given to registrants who cancel less than 1 week before the beginning of the course or who fail to attend. Substitutions for paid registrants may be made at any time. However, written notice is required and for complete accuracy must include all pertinent changes for the substituting attendee.
In the extraordinary case where the course is cancelled a full refund will be given to all paid registrants. Crossjoin Consulting will not refund any other amount paid by registrants to other companies, including travel expenses and hotel reservations.
Chris Webb's Introduction to MDX course was well-run, informative and good value. It gave me a good understanding of the key principles and ideas and how to apply them to real world situations. It was great environment to learn in, with Chris giving excellent practical exercises and having the depth of knowledge to answer to any group questions.
Calvin Ferns
Consultant, Adatis
Chris has an incredible ability to explain even the most complex logic of Analysis Services and MDX in an easy and funny way. He is one of the best Microsoft Business Intelligence trainers I've ever met.
Stephan Stoltze
Consultant, Stoltze IT
