Chapter 5 TOP and OFFSET-FETCH

This chapter takes a look at ordering and row numbers. The chapter opens with a look at the TOP and OFFSET-FETCH filters. TOP returns a given number of rows (usually sorted), OFFSET-FETCH is similar but gives the number of rows to skip.

The chapter continues with a look at paging using various optimization techniques, including optimizing TOP, OFFSET-FETCH, ROW_NUMBER, and TOP with modifications. The importance of indexes is highlighted.

This was another useful practical chapter, with many uses, especially for reporting. Useful code is provided that you’re sure to adapt to your own needs. Additionally, code is provided for solving ‘top N per group’ and the calculation of the median.

Chapter 6 Data modification

This chapter is concerned with inserting, deleting and updating data, with particular emphasis on inserting. Various methods of inserting data are discussed in detail, including: SELECT INTO, BCP, BULK INSERT, and OPENROWSET. There’s some innovative code that shows the amount of logging associated with the different insert methods.

The chapter continues with a look at the sequence object. The section starts with a look at the problems associated with the identity property, and moves onto discuss the flexibility of using sequence objects. Various performance considerations are examined, including the impact of caching. There’s a very useful comparison of identity and sequence objects.

The chapter ends with a look at merging data. Plenty of MERGE examples are provided, and preventing merge conflicts are discussed with examples. The use of the OUTPUT clause for auditing and debugging is noted.

This was a pragmatic chapter, while not comprehensive, it did discuss the common cases. The use of the fn_dblog function to show how much logging is involved is a useful technique. There’s also a useful pattern for deleting duplicate data.

Chapter 7 Working with date and time

It might seem odd to devote a chapter specifically to date and time processing. But the authors are well aware these features in particular cause problems, with many pitfalls and potential for sub-optimal code.

The chapter looks at the various date and time data types, with plenty of example code. Types include datetime, smalldatetime, date, and time. There’s a useful grid showing each type’s storage size, range, and precision. The section continues with a look at various built-in date and time functions.

The next section provides a very helpful discussion of date and time problems, together with their solutions. Many of the code examples should prove useful in your everyday work with date and time types. The chapter ends with another set of puzzles, relating to intervals. Links are provided to the author’s related sqlmag.com articles.

Although this is quite a specific topic, if you’ve worked with date and time you’ll be aware of the potential for awkward code (e.g. storing dates as strings), the detail in this chapter should help create better code, as well as providing a useful reference.

Chapter 8 T-SQL for BI practitioners

With an increasing amount of data being stored and reported upon, there’s a greater need for a detailed understanding of statistics. This chapter provides an interesting discussion of data values and statistical methods that can be applied to them. Both discrete (e.g. eye colour) and continuous (e.g. height) data is discussed. The following statistical features are examined, with useful code provided and discussed: mode, median, mean, range, inter-quartile range, mean absolution deviation, mean squared deviation, degrees of freedom and variance, standard deviation and coefficient of variation, skewness, kurtosis, covariance, correlation, linear regression, analysis of variance, chi-squared test, and confidence limits.

I found this an interesting chapter. I worked with most of the features discussed many years ago, so it was helpful to refresh my understanding. The chapter could stand alone as a very good introduction to further statistical methods. Helpfully there’s a useful long summary included (why do the other chapters only have a short summary section?!).

Chapter 9 Programmable objects

This chapter discusses a miscellany of topics that help improve the creation of SQL solutions. The chapter opens with a look at dynamic SQL, the reason why it is needed, its security implications, and provides some useful dynamic search and sorting code.

The chapter continues with a look at the benefits of stored procedures (e.g. reuse, centralisation, security). The pitfalls of parameter sniffing are explained, together with various methods of correcting it (while noting that it is often a good thing).

Triggers are examined next, these are special stored procedures that respond to specific events. After DML, Instead Of DML , and After DDL triggers are all examined. There’s a very useful section on efficient trigger processing.

The next section examines SQLCLR programming. This provides a way of accessing .NET functionality from within SQL Server. While SQL is great for data processing, it can be awkward/slow when performing calculations or loop processing – here .NET typically has the advantage. The section shows how to create, publish, and use a simply CLR routine.

The next section examines transactions and concurrency. The ACID properties of transactions are discussed, followed with a look at locks, blocking, and lock escalation. Delayed durability is discussed with reference to in-memory OLTP. The various isolation levels together with their associated potential problems (e.g. dirty reads, lost updates, phantoms) are also discussed. The section ends with a look at deadlocks, what they are, and how they can be monitored and captured.

The chapter ends with a look at error handling. In older versions of SQL Server this was restricted to examining the @@error global variable, however recent versions of SQL Server use the more modern TRY/CATCH pattern (and THROW since 2012).

This chapter contains a diverse array of topics loosely related to improving the hosting of SQL code. It might have been useful to include information on how to decode the deadlock information (or provide a link). The code to retry the deadlock might have benefited from a WAIT DELAY before retrying the code. 

Chapter 10 In-Memory OLTP

For many people, in-memory OLTP is the primary reason for upgrading to SQL Server 2014. This chapter starts with a look at the circumstances that led to in-memory OLTP (which primarily holds tables in memory rather than on disk), these include: cheaper memory, smaller rises in CPU clock speeds, and increasing number of cores.

The structure of in-memory OLTP tables is discussed (no pages!), together with the structure of rows and the impact of concurrency. The section then looks at natively compiled stored procedures (these compile to C code, and typically run significantly faster against in-memory tables). Example code is provided to create an in-memory table.

The chapter proceeds with a look at the various types of indexes used in in-memory tables. The indexes are part of the table structure, if you need to alter an index, the table needs to be dropped, recreated and reloaded. Nonclustered indexes, AKA range indexes, can be used with partial searches (e.g. OrderId > 500). Hash indexes require an exact match. The structure of each type of index is explained with examples.

The chapter then looks at the execution environment. Query interop can be used to run a traditional stored procedure against an in-memory table – it typically runs faster than the same stored procedure against a disk-based table, but slower than a natively compiled stored procedure – the latter are examined next. Current limitations on in-memory OLTP usage are examined, these include both limited data types (e.g. no MAX), and limited integrity (e.g. no foreign keys).

While this is a wide ranging and useful chapter, there is plenty more to say on the subject. No mention is made of the Analyze, Migrate, and Report (AMR) tool – a likely first point of contact when upgrading to SQL Server 2014. The tool contains several associated tools for identifying the tables and stored procedures, which should be migrated first, based on expected performance improvements. Perhaps some emphasis could have been given to these tools.

Chapter 11 Graphs and recursive queries

This chapter takes a look at graphs (i.e. hierarchies or trees) and recursive queries. The section starts with an overview of terminology, and continues with a look at processing graphs via both recursion and materializing extra information that describes the data structure (e.g. using the hierarchyid data type). The approaches are compared. The solutions provided give some interesting and innovative solutions to the problems.

The reservation I have about this chapter is, although it is interesting, is it really applicable to a wide range of everyday problems for the developer? I suspect not. Perhaps other topics should take precedence.

Conclusion

This book aims to give you a deeper understanding of T-SQL features, with an emphasis throughout on performance. It is an amalgamation and update of 3 previous well-respected books.

All the topics are explained in a very readable and interesting manner. Throughout, there are helpful explanations, screenshots, practical code examples, inter-chapter references, and links to websites for further information. Where possible, references to performance improvements are included. Additionally, there are plenty of useful incidental side tips. Although primarily intended for SQL Server 2014 and 2012, much of the content is applicable to earlier (and probably later) versions too.

This book is not for the beginner. It teaches advanced SQL concepts and techniques, it should take your understanding of T-SQL from around 4 to around 8. It is an ideal follow-on from Ben-Gan’s “Microsoft SQL Server 2012 T-SQL Fundamentals”, which I recently reviewed.

The book does have omissions, and this is to be expected, otherwise it would run into several volumes. I would have liked to have seen a section on websites/newsletters that would improve your T-SQL understanding. I would have also liked to have seen some discussion of Big Data and its implications. Then there’s the perennial problem of book updates, it would be helpful to know what is new compared to previous editions of the book (this should be easier in the digital world). That said, in the broader context, these are really minor points.

If you want a more advanced understanding of T-SQL features, together with the latest performance techniques, written by some of the best SQL educators, I can highly recommend this excellent book.

<ASIN:  0735685045>
<ASIN:  B00TPRWVHY>

<ASIN: 0735658145>
<ASIN: B00JDMPI0I>