Page 1 of 3
Author: Miguel Cebollero, Michael Coles and Jay Natarajan
Audience: SQL Server developers
Reviewer: Ian Stirk
Despite a misleading title, this book aims to provide SQL developers with the knowledge to get the most out of SQL Server 2014.
Firstly, from the book’s title I expected an in-depth discussion of the various aspects of the T-SQL programming language from a SQL Server 2014 perspective. Even the blurb on the publisher’s website says:
“Pro T–SQL Programmer’s Guide is your guide to making the best use of the powerful, Transact-SQL programming language that is built into Microsoft SQL Server's database engine.”
However, this book is not a discussion about T-SQL specifically; rather it is a guide to SQL Server 2014’s functionality from a developer’s viewpoint.
The book’s title seems incorrect.
The book is aimed at SQL Server developers wanting to get up to speed on functionality in SQL Server 2014. It’s not an introductory book, and assumes a working knowledge of T-SQL .
Below is a chapter-by-chapter exploration of the topics covered.
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:
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).