Shortly after Microsoft SQL Server 7.0 shipped in the late 1990s, I quizzed a senior developer in the Microsoft IT department about the programming languages she and her team were using when working with SQL Server. "In the past we were writing code in C and Visual Basic, but now we are developing only in T-SQL." This revelation was an eye opener for me. At that time, I had been working on developing new versions of SQL Server for about three years, and it was only then that I realized that very large projects were being developed using T-SQL and that the T-SQL programming language had become the tool of the trade for many individuals. Since then, Microsoft has invested heavily in the new releases of SQL Server including significant enhancements to the T-SQL language. The number of SQL Server installations worldwide has increased by an order of magnitude, and today there are many developers, database administrators, data analysts, testers, and architects for whom T-SQL is the programming language of choice and, in many cases, the only programming tool they use.
A closer look at the T-SQL constructs reveals two kinds of statements–those that extract, insert, and manipulate data in the database and those that provide control of flow, output, variable declaration and manipulation, and other functions we find in most non-database programming languages. SELECT, INSERT, SEND, and RECEIVE are examples of the first kind and DECLARE, BEGIN, IF, ELSE, WAITFOR, and PRINT are examples of the second kind of statements in T-SQL. Itzik used the approximate line between the two classes of statements to split his T-SQL material into two books–Inside Microsoft SQL Server 2005: T-SQL Querying and Inside Microsoft SQL Server 2005: T-SQL Programming.
Neither of these books is a complete language reference, and there is no need to write one because you can download it from the Microsoft Web site at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx. Instead, Itzik used his vast T-SQL teaching experience to include the more intricate subjects in his books, together with those new to SQL Server 2005. Another advantage of having a teacher write a book is that he has addressed hundreds of questions related to the subject from his students. Itzik very skillfully uses this experience to present the material as if answering potential questions and includes many examples and tips throughout the book. My favorite tip is in the first chapter. It explains how you can use a small test table to find out if your ALTER TABLE statement against a huge table in your database will take seconds or hours. This great tip can be a real lifesaver (or career enhancer?)–but be careful and don’t forget to use a non-empty test table!
My favorite example in the book is the Dynamic Pivot in Chapter 4. SQL Server 2005 introduced PIVOT and UNPIVOT statements, but they cannot handle rotating an unknown number of elements. This restriction has been loudly and frequently criticized by various audiences. Itzik shows how you can achieve dynamic pivoting by using a batch consisting of three(!) statements (if you don’t count the variable declarations) in SQL Server 2005. If you are not using the newest version of SQL Server, you will find the pre-2005 version of the dynamic pivot batch in the book as well. It uses 10 statements, and comparison of the two versions of the pivot batch is a startling example of the increased programmability power of SQL Server 2005. Including the pre-2005 code snippets and command alternatives makes the book attractive even to programmers who are not using SQL Server 2005 yet; they can still apply the pre-2005 solutions immediately.
The T-SQL Programming book is generously laced with practical code samples you can easily use in your own work. The authors think beyond the correctness of the samples and take security, performance, and potential blocking into consideration as well. Therefore, the book contains numerous examples in which the query plans are examined and compared with alternative plans. For instance, in Chapter 9, you will find information about how to use dynamic management views (DMVs) to troubleshoot blocking scenarios. DMVs were newly introduced in SQL Server 2005 to provide information about the server state that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
SQL Server is a complex product, and consequently it offers more than one way to solve almost any problem. Itzik and the coauthors thoroughly examine the multiple options and explain how you should choose among them. Sometimes one of the solutions is universally better but more often there are pros and cons, and the authors provide criteria for making the correct choice. For example, in Chapter 1, you will learn why you should use ′20060212′ instead of ′02/12/06′ or ′12/02/06′ in the T-SQL code to represent February 12, 2006. Chapter 2 presents a face-off between table variables and temporary tables. You will learn when to use, and even more importantly, when not to use cursors in the Chapter 3. Chapter 4 covers discrepancies between EXEC and sp_executesql. Next, in Chapter 5, you will learn when to use Common Table Expressions (CTE), which are new in SQL Server 2005, instead of creating views. Chapter 7, which covers stored procedures, explains why the names of objects should be schema-qualified. Chapter 8 clarifies when you should move the content of the inserted or deleted table inside the triggers into a temporary table for performance reasons. In Chapter 11, you will learn when to use Service Broker and when it is better to use MSMQ or BizTalk. This list is far from being exhaustive, but it gives you a taste of some of the book’s many contributions.
Lubor Kollar Group Program Manager Microsoft SQL Server