Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.
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."
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Adam Machanic
Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.