Advanced TSQL in SQL Server 2000 - 2008
Simon Sabin
Learn advanced T-SQL techniques to solve complex problems and enhance your
database performance. You will also learn how to to review existing T-SQL and optimise it for simplicity and
performance.
Optimising your database application is very much like tuning a racing car, you
can separate it into a few areas. You can tune the hardware that your database runs
on i.e. The road, wheels and shock absorbers. You can tune the structure of your
database, i.e. The engine of the car. Finally you can tune how your application
uses the database, i.e. the driver.
In this seminar we will focus on optimising the way in which you drive your database. We will touch on some of the other areas of optimisation where
we need to.
In racing, different drivers take different racing lines, in SQL Server the same
applies. The intent behind a query can be achieved in a number of different ways,
some that perform well, some that perform very badly. You will learn in this seminar
to identify how queries can be rewritten to achieve the best performance, and simplify
them.
The seminar will cover the following topics
Introduction to optimisation
Before you can start looking at optimising your SQL you need to understand what
you are optimising for and how you identify what is optimised. We will look at,
- What indicates good and bad performance
- What to avoid to get good performance
- What do we measure
- How do we measure it
Query plans and statistics
Query plans are what SQL Server uses to run your query. Understanding how a query
plan is generated and used is essential in analysing query performance. In this
session we will look at some of the basics of query plans and the most common issues
around query plans,
- What is a query plan
- Estimated v Actual statistics
- Parameter usage
- Conditional joins and parameters
Procedural v SET based code
TSQL is a procedural language, that allows you to do set based operations using
SQL. Choosing between procedural and set based operations needs careful consideration.
In this section we will look at,
- Transactions, locks and blocking
- Loops and Cursors
- Block updates to data
- Use of functions
Combining data from tables
All databases contain more than one table and most queries require data from more
than one table. SQL has many ways of achieving this, joins, sub queries, views,
each has pros and cons. This session will cover the following,
- Returning data from multiple tables - Outer Join, Apply
- Filtering with sub queries
- Handling missing joins and many to many relationships
- Limitations
Writing reporting queries
In most systems the most complex queries are those required for reporting. These
queries need to process a large amount of data. Writing the correct query is essential
to avoid processing data unnecessarily. Many of the approaches you will learn in
this section can be used in non-reporting queries as well. In this section we will
look at,
- Pivoting data using PIVOT and CASE
- Ranking and grouping aggregates with Windowed aggregates
- Complex aggregates using Derived tables and CASE
- Simplifying queries using common table expressions
- Temporary data stores - table variables v temp tables
Course Length/Type
One day instructor led seminar.
Location
St Leonards Hall, Edinburgh.
Pollock Room
St. Leonards's Hall
18 Holyrood Park Road
Edinburgh
EH16 5AY
St Leonards Hall in the Pollock Halls complex will be the venue. Map for
Pollock Halls.