Chapter 1 Foundations of T-SQL

The chapter provides an overview of how SQL has developed, its basic structure, and the importance of coding style. A brief summary of the development of Codd’s relational model is given. The difference between declarative (e.g. SQL) and imperative (e.g. C) languages is highlighted – i.e. WHAT to do as opposed to HOW to do it. The basic constituents of SQL are outlined in discussing schemas, tables, indexes, stored procedures, user-defined functions, 3-value logic, etc. This is followed by a review of the importance of coding style and formatting. This is a good overview of where SQL came from and its major components.

The chapter is well written, with useful explanations, code examples, outputs, inter-chapter links, and diagrams. A helpful summary, together with chapter-related exercises, are provided. These traits apply to each chapter of the book.

Chapter 2 Tools of the Trade

This chapter provides an overview of the major tools SQL Server developers use. The primary tool is of course SQL Server Management Studio (SSMS), since developers spend a long time there, it make sense to have a good grounding in its functionality, including: IntelliSense, code snippets, T-SQL debugging and context-sensitive help. Other tools discussed include: SQLCMD, SQL Server Data Tools, SQL Profiler, BCP, Extended Events, and Books Online.

None of the tools are given in great detail, but there’s sufficient information to get you started. I’m surprised PowerShell wasn’t mentioned, especially since it seems to be Microsoft’s preferred scripting language. Similarly, I would expect Dynamic Management Views (DMVs) to be mentioned here (they are discussed in chapter 14). Overall this is a useful background chapter.

Chapter 3 Procedural Code and CASE Expressions

This chapter discusses the common T-SQL language control structures. The chapter starts with the importance of three-valued logic (true, false, and unknown i.e. NULL). This is followed by various flow-of-control statements (e.g. IF…ELSE, WHILE, BREAK, GOTO etc). Both the simple and searched CASE expressions are shown, together with the keywords IIF and CHOOSE. Finally the use of the much castigated cursor is discussed – I tend to agree with the authors, there are certain times, chiefly related to expediency, when using a cursor is practical.

Several analogies are made between SQL and C#/.NET, which is fine if you know the language, but not everyone will. Overall this is a good background chapter.

Chapter 4 User-Defined Functions

This chapter discusses the three types of User-Defined Functions (UDFs), namely: 

• Scalar Functions

• Multi-statement Table-Valued Functions

• Inline Table-Valued Functions 

In each case, good examples of usage are provided together with notes on any restrictions or performance improvements (e.g. RETURN NULL ON NULL INPUT).

The chapter provides a good overview of UDF functionality, with useful sample code – if a little long at times. I would have expected a note about the dangers of using UDFs in a SELECT or WHERE clause, when many rows are involved - that said, this is mentioned in Chapter 5 in relation to stored procedure best practices.

Chapter 5 Stored Procedures

This chapter provides a review of the workhorse of SQL Server, the stored procedure (SP). It starts with an outline of metadata discover. A sensible list of best practices is given, these include: 

• SET NOCOUNT ON

• Specify schema names with objects

• Don’t begin a SP with sp_ or sys

• Avoid scalar functions in SELECTS with many rows

• Use the correct data type with parameters

• Use and keep transactions small

• Use TRY…CATCH for error handling

• Use UNION ALL when possible 

The discussion continues with the advantages SPs have over inline SQL (i.e. performance, less network traffic, modularity/reuse, and easier security). The use of Table-Valued parameters to pass ‘arrays’ of data to a SP is discussed and contrasted with previous untidy or complex methods (xml, comma separated strings, or repeatedly calling SP).

The impact and causes of recompilation are outlined, as is parameter sniffing (which is typically a good thing). There’s a nice SP provided that identifies what SPs are executed most, largest in size, and using the most reads/writes. This SP should have been amended to identify the longest running SQL, making it much more useful (it just needs a simple amendment to the ORDER BY).

In the sample recursion code, the temporary tables are explicitly dropped at the end of the routine, but this can cause problems with performance (due to locking) especially if the tables are large. Current thinking suggests temporary tables should not be dropped explicitly, instead SQL Server should tidy-up these itself when it has time (in a similar manner to the garbage collector in .NET).

The chapter provides good detail about SPs, their usage and options, with some helpful sample code. One minor criticism is Common Table Expressions (CTEs) are used before they have been discussed (they are discussed in Chapter 9).