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 shown 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 are: 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 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 2013. 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 17 Data Services

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

Chapter 18 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 is more popular. Various example code is given to show their usage. Additionally, the 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 to security threats. 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 19 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).

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 DMV usage.

Appendices

There are appendices for: Exercise Answers, XQuery Data Types, Glossary, and SQLCMD Quick Reference. 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 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.

If you exclude the chapter on In-Memory Programming, I think perhaps 95% of the book is the same as its previous edition. There’s a lot more that could be said on most topics (e.g. In-Memory Programming), and not all new 2014 specific topics are mentioned (e.g. the new cardinality estimator, resource governor limit by IO, and incremental option for create statistics). Although the book is aimed at SQL Server 2014, it covers features introduced from 2005 onwards, so could be applicable to previous versions.

The summaries and exercises at the end of each chapter are good for revision and understanding. Some links for further information are provided. It would have been useful to include places to go to keep up to date with SQL Server (e.g. websites, newsletters, blogs etc).

As a book about SQL Server 2014 from a SQL developer’s perspective, I would recommend it.

To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.