Author: Jay Natarajan et al
Publisher: Apress
Pages: 696
ISBN: 978-1430245964
Audience: SQL Developers
Rating: 4.5
Reviewer: Ian Stirk

This book aims to provide SQL developers with knowledge to get the most out of SQL Server 2012. How does it fare?

I did wonder if the next three chapters should be in this book, but I guess as the job of the SQL developer becomes wider in scope, more developers will spend time working with .NET and developing client code. Additionally it makes sense to see how SQL Server is being used by clients. All of which should make SQL developers more employable!

Chapter 14 CLR Integration Programming

This chapter shows you how to write routines in a .NET language (C#), and how that functionality can be used within SQL Server. This chapter starts with a short history of how SQL Server programming was enhanced in the past, by using Extended stored procedures (typically written in C), and also OLE automated stored procedures. Both were awkward and complex, and could bring down SQL Server.

The newer approach is to use the .NET framework and the CLR. The CLR uses safer managed code and is tightly integrated with SQL Server. It is not a replacement for T-SQL but rather an adjunct that should be used for non-set based programming (e.g. loops and calculations). There is a step-by-step guide to creating an assembly in Visual Studio 2010. The useful sample code provided illustrates the use of Regular Expression functionality, this functionality is limited when done in T-SQL alone. Another example shows the processing of a Yahoo RSS feed. This chapter is a good introduction to using the CLR, and supplied useful step-by-step code examples.

Chapter 15 .NET Client Programming

This chapter shows the various ways in which different .NET clients can use SQL Server. Knowing how clients use T-SQL code should give you a better insight into how your code is used, and might also led you to different way of writing your T-SQL (e.g. paging, caching etc).

The chapter introduces the .NET framework and the SqlClient class, the latter is both efficient and simple to use. The .NET data types are shows together with their equivalent SQL Server data types. Various example C# client code is provided, which is useful as a template for your own code. Other technologies covered include multiple active resultsets (MARS), LINQ to SQL, and the Entity Framework.

If you’re interested in extending you programming knowledge to the client, or want a better insight into how your T-SQL code is being used, this chapter is very useful.

Chapter 16 Data Services

This chapter shows how various platforms can get data from SQL Server. Clients discussed include JDBC, PHP, ODBC, and ADO.NET. Typically the SQL Server is access via a web service or more recently by a WCF service. There is some useful sample .NET code for defining, creating and using a WFC data service.

Chapter 17 Error Handling and Dynamic SQL

Prior to SQL Server 2005, error handling was mostly a matter of inspecting the @@error variable for any errors, this was restrictive and messy. Now a TRY/CATCH model similar to that used by .NET has been introduced, and in 2012 there is the new THROW statement. Various example code is given to show their usage. Additionally, the newer 2012 functions TRY_PARSE, TRY_CONVERT, and TRY_CAST are all explained. Related aspects of debugging are discussed, including: the SSMS debugger, the ubiquitous PRINT statement, and the use of trace flags.

Dynamic SQL, while flexible, is often misused, since it can inadvertently expose databases, servers and networks. A key point is not to trust the user’s input, and all input should be checked for spurious characters. The system SP sp_executeSQL should be used to run the dynamic SQL since it allows parameters. All these things should help reduce the chance of SQ injection attacks.

I have no idea why these two disparate topics have been grouped together in the same chapter, there is no obvious linkage.

Chapter 18 Performance tuning

This chapter starts with a discussion of storage, files and filegroups. The idea being that placing different tables and/or indexes on different filegoups (mapped to different physical disks) should improve performance, because there are more disk controllers working in parallel. The chapter goes into detail about extents, GAM, SGAM, IAM, and PFS. I’m not sure if this level of detail is required or warranted in such a generalist book.

Data compression and sparse columns are discussed, the premise being that you get more data per read if you enable these features. Some useful sample code is provided.

The importance of indexes is discussed. Clustered indexes are good when you want a large amount of data especially in the sort order of the cluster. Non-clustered indexes are good for retrieving a specific subset of data. Filtered indexes are also discussed, these are indexes that have a WHERE condition.

An 8 step methodology for approaching and resolving performance problems is given. Although the steps are quite obvious, it might be useful as a checklist to work against.

Using waits and queues is a well known performance tuning approach. Only waits are discussed here. When SQL Server can’t run your SQL because it is waiting on some resource, the reason for the wait is recorded internally. Examining these waits should allow you to determine what the major problems are on the server, resolving these should improve the performance of your queries.

A limited amount of information about Extended Events is given. These are the preferred mechanism for profiling (SQL Profiler is deprecated). Importantly, SQL Server 2012 contains a built-in GUI for creating Extended Events, previously in 2008 an add-in needed to be installed.

Performance tuning is a big topic. The major tools and approaches have been introduced here, but only introduced. The chapter should have made links to the chapters on tools and on DMVs (and elsewhere there is SQL to identify the longest running SPs etc). Surprisingly, no information is given on ColumnStore indexes, which can greatly improve performance in SQL Server 2012.

Appendices

There are appendices for Exercise Answers, XQuery Data Types, Glossary, and SQLCMD Quick Reference. I think only the Exercise Answers is needed, the other information can be easily obtained via a quick internet search.

Conclusion

From a developer’s perspective, I found this book an enjoyable read, with lots of useful example code, having wide-ranging and in-depth coverage of the major SQL Server features. It will certainly get developers from previously versions of SQL Server or from other platforms up-to-speed, but be aware it is not a book for beginners. Although the book relates to SQL Server 2012, it covers features introduced from 2005 onwards, for example the use of the CLR hasn’t really changed since 2005.

The summaries and exercises at the end of each chapter are good for revision and understanding. Most of the references for further information tend to refer to Apress’s own titles, this is perhaps understandable from the publisher’s perspective, but wider references would have been useful.

As a book soley about T-SQL 2012, I would award a rating of 2.5, as a book about SQL Server 2012 from a SQL developer’s perspective, I would rate it 4.5. Recommended.