Demos: Connections, Fall 2009, Las Vegas

Thanks to everyone who attended my sessions this week at SQL Server Magazine Connections in Las Vegas. Attached to this post you will find the demos for the following sessions:

SDV301: Best Practices for Exception Handling and Defensive Programming in SQL Server 2005 and 2008

As developers, we sometimes become lax about dealing with error and exception conditions by the time our code gets down to the data level. Exceptions can feel like something that only application code needs to worry about, until you realize that in SQL Server they can have a tremendous effect on your transactions and your data integrity. Learning to properly handle them is, therefore, of paramount importance to those of us who write data-centric applications. SQL Server 2005 greatly improved exception handling options by adding support for the structured TRY/CATCH syntax, but there is a lot more to the story than just that feature. In this session, we'll delve into the ins and outs of exceptions in both SQL Server 2005 and SQL Server 2008, starting with the database engine itself: types of exceptions, when and why they're thrown, and how the server treats them. Next, learn how to configure and throw your own custom exceptions, as well as how to leverage the SQL Server exceptions infrastructure with a variety of exception handling and defensive programming techniques both with and without the TRY/CATCH syntax. Most importantly, we review the effect of exceptions on transactions, and how to take programmatic control over the outcome of your transactions in the face of an exception.

SDV303: T-SQL Power! Learning to Harness the Under-Used OVER Clause

First introduced in SQL Server 2005, the OVER clause is an ANSI SQL enhancement that gives you tremendous control when dealing with aggregations. By using the OVER clause, query writers can simultaneously aggregate columns based on multiple groups. The feature also enables the query engine to provide windowing mechanisms for ranking and row numbering. Leveraging these powerful language enhancements allows you to solve a surprisingly large number of difficult query problems—including custom paging schemes, data de-duplication, "top-N" problems, and complex statistical calculations. Even better, this feature can be creatively applied to help with performance optimization of certain tough queries. In this session, you will learn all of these techniques and see why, after applying the OVER clause in dozens of projects since the release of SQL Server 2005, I consider it to be one of the most powerful T-SQL features available.

SDV210: What Happened? Auditing, Tracking, and Change Monitoring Technologies in SQL Server 2008

Regulatory bodies...end-users...your boss. They all want answers. Many questions are easy enough to deal with: "Did someone drop my view?" Others are a bit trickier: "What was the previous value of this row?" And some are seemingly impossible: "Who selected the data from this table over the past week?" For many DBAs, the answer to some or all of these questions is often "Umm..." But don't blame yourself; getting this information in SQL Server has never been especially easy—until now. SQL Server 2008 ships with several new technologies designed to help you track and report on exactly what happened, who did it, and when. In this session, you will learn about SQL Server 2008's Change Tracking, Change Data Capture, and SQL Server Audit features, each of which provides a distinct set of capabilities and has specific strengths and weaknesses. Looking at each of these technologies in turn, you will see how they work and where you might want to leverage them in your SQL Server infrastructure. If you're used to saying "Umm..." get ready to say "I'll be right back with the answer."

Enjoy!