Day 1
T-SQL Sets
In this exciting first day, we dig into the complex T-SQL language to discuss best practices for our queries for superior functioning. We look at how different ways of writing code can affect your queries. We study new T-SQL operators which can create powerful performance improvements to our code. We begin by comparing temp tables and table variables, and then dive in to derived sub-queries and common table expressions (CTE's) followed by the use of the 'apply' operator. We see how parameter sniffing drastically affects query execution. Finally, we turn to the considerations for various query operators including union, intersect, except, and pivot sets.
T-SQL Enhancements
The impressive work Microsoft started in SQL Server 2005 to improve the T-SQL language has continued in SQL Server 2008. SQL Server 2008 introduces valuable new T-SQL language features such as the keyword Microsoft implemented to handle upserts, called 'MERGE'. To make working within in-memory tables easier, we cover User Defined Table Types and Table Valued Parameters. Traditional T-SQL error handling using return types is cumbersome and difficult to maintain, we examine how try/catch error handling is a big improvement. Finally, we learn how to use windowing functions to solve tougher problems in fewer lines of code.
T-SQL Data Types
T-SQL data types are key elements in unlocking the power of SQL Server 2008. We examine essential principles and practices in data types with an in-depth study of the VARMAX types. Here, and in a later module, we look at developments and enhancements to the type system including the more traditional T-SQL types. We will also cover hierarchies and the file stream data type.
Day 2
Architecture
The world of the DBA is changing as they are pushed to manage more complex systems with incredible amounts of data, well into the Terabytes, while trying to keep cost low; DBAs are given more responsibilities of providing instant data access and creating applications that perform reliably to meet the heavy demands of the business. The internal architecture of SQL Server 2005 was slightly changed from previous versions as the demands placed on the DBA skyrocketed in terms of scalability, manageability and quick access to data. In this module, we inspect the internals, how they interrelate, the effects they have on performance and tuning issues, and what potential impacts they have for us as developers.
Transactions and Locking
How many times have you had users report time-outs and freezes causing a domino effect of jammed reports because a query incorrectly locked a table? Did you even know the time-out might have been caused by an improper transaction? The transaction module probes into the various choices we have when writing transactional code in SQL Server (both T-SQL as well as .NET). We see how SQL Server handles transactions, locking and discover alternatives that alleviate the headaches of poorly written code.
Security
There is a lot at risk when securing a server from potential attacks with the abundance of existing sensitive data available such as social security numbers, bank accounts, medical records, and credit card numbers. In this module, we look at how we as developers can use SQL Server's security features to properly protect our databases from internal and external threats. We examine how to encrypt data in the database, how schemas can make security permissions easier, and insider tips on security credentials and execution context.
Day 3
Extended Events
SQL Server has always had the ability and tools to monitor server state and diagnose issues inside SQL Server (for example, SQL Trace and performance monitors). SQL 2005 introduced the ability to use the tracing capabilities within the operating system event Tracing for Windows (ETW). SQL Server 2008 builds upon this with Extended Events (EE), a general event-handling infrastructure. Extended Events is by far the most flexible and efficient event engine available within SQL Server. We look at the architecture of EE, the various components that EE introduces and also how we can use EE together with ETW to correlate event data from inside SQL Server with data from the operating system.
Performance Tuning with Indexes
Often when developers are asked to solve performance problems, the answer is to "throw hardware at it". While inelegant, hardware can solve some performance issues. Once that option is exhausted, it's important to know how indexing, table partitioning, and query tuning can dramatically improve the performance of your SQL Server. Once the techniques taught in this module are applied, it will appear as if a switch has been turned on; the SQL Server will be running at an impressive new speed.
Performance Tuning with Cache
In this session, we look at SQL Server's Query Cache, what it is and how it works. We learn to interpret query plans, and to use system views to trouble-shoot queries. Students are given the tools and knowledge to manipulate the four most important performance counters when tuning SQL Server including CPU usage, memory usage, cache hit ratios, and disk I/O.
Day 4
Automatic Data Change Management
On our final day together we embark on learning a completely new feature in SQL Server 2008: Change Data Capture or Change Tracking. It is a lightweight solution that provides an efficient change-tracking mechanism for applications. Using change tracking in SQL Server 2008, we no longer need to roll into our applications our own solution to monitor changed data. This overview shows how change tracking is implemented inside SQL Server and the benefits gained.
LINQ and Entity Framework
Here we cover how LINQ works with SQL Server. We review the more advanced topics, such as mapping between the database and the business objects, and execution of stored procedures and user-defined functions. We learn to use existing stored procedures to update data in Entity Framework.
Additional Bonus Material:
This course also includes optional material that can be covered if time allows, including: Database Design Techniques, SQL Azure, XML, SQL CLR, SQL Server Reporting Services, SQL Server Integration Services, and Service Broker.
