This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here                     
decoration
decoration
decoration

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

Cost: £499 to Dec 31 2012, 549 from 1 Jan 2013 +VAT
decoration

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. 

decoration

Location

LMG Training and Development
15 Risborough Street
Southwark
SE1 0HG
decoration

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 plus any credit card charges incurred. No refunds will be given to registrants who cancel less than 2 weeks before the beginning of the course or who fail to attend.

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.

decoration
decoration decoration decoration decoration
decoration
decoration