| Pro T-SQL 2012 Programmer’s Guide |
Page 1 of 3
Author: Jay Natarajan et al
This book aims to provide SQL developers with knowledge to get the most out of SQL Server 2012. How does it fare? 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 2012 perspective. Even the blurb on the publisher’s website says “Pro T–SQL 2012 Programmer’s Guide is every developer’s key to making full use of SQL Server 2012’s powerful, built–in Transact–SQL language.” However, this book is NOT a discussion about T-SQL specifically, rather it is a guide to SQL Server 2012’s functionality from a developer’s viewpoint. The book’s title is misleading. The book is aimed at SQL developers wanting to get up to speed on functionality in the later releases of SQL Server, and also those developers coming to SQL Server from other platforms. So it is not an introductory book. Read on for 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. 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 13). 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 new 2012 keywords IIF and CHOOSE. Finally the use of the much castigated cursor is discussed – I tend to agree with the author, 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 some new 2012 features, metadata discover, and WITH RESULT SETS. 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 longest running, executed most, and using the most reads/writes. This could be useful in tracking down your performance problems. In the sample 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 useful sample code. One criticism is Common Table Expressions (CTEs) are used before they have been discussed, which happens in Chapter 8. |
||||
| Last Updated ( Monday, 30 September 2013 ) |
