Book Review: T-SQL Window Functions by Itzik Ben-Gan

A couple of years back Itzik Ben-Gan (the T-SQL guru) wrote the book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions. It’s has been one of my most favorite technical books (I included it in this list): it was really an eye-opener to how useful and powerful window functions can be. It teaches the basics extremely well and also proposes solutions to common use cases.

Recently, a second edition was published titled T-SQL Window Functions – For data analysis and beyond (a much better title imo). For those of you wondering, are there any changes to window functions in SQL Server since SQL Server 2012? Why yes there are.

• support for batch mode was introduced, and in SQL Server 2019 batch mode on rowstore was released as well. This can lead to different performance optimization choices.
• a couple of new functions were introduced, such as STRING_AGG.

Even more than in the first edition, Itzik explains the power of window functions in the SQL standard, even if the implementation is not (yet) supported in SQL Server. There is for example a whole chapter on row-pattern recognition, which would definitely be awesome if SQL Server supports it one day.

The power of the book is still the same as in the first book: it is quite short (just over 300 pages) but it explains all of the concepts really well.

You start with a short chapter introducing you to window functions: the background, the different elements and logical query order. The second edition also adds an evolution of window functions over the various versions of SQL Server.

The second chapter explains all the different types of window functions: window aggregate, ranking ,statistical (called distribution in the first edition) and offset functions. The first two chapters lay a good foundation and are already a good start for learning window functions and you’re only 100 pages into the book.

The third chapter dives into ordered set functions, which aren’t supported in SQL Server except for LIST_AGG (called STRING_AGG in SQL Server). If you’re just here for the T-SQL, you can skip this chapter but it’s an interesting read nonetheless as it gives you an idea how powerful the SQL standard is. Chapter four (an entirely new chapter) is the same: it talks about row-pattern recognition which is not present (yet) in SQL Server. Again, if you only use SQL Server you can skip it, but it’s a very interesting and mind-challenging read. I’d advise to read it.

Chapter 5 and 6 are the more practical chapters: the first one dives into optimization of window functions, while the last one presents solutions for certain use cases using T-SQL window functions. In the optimization chapter more content is added about emulating the NULLS LAST functionality, while the last chapter has more content about emulating IGNORE NULLS (which exists in Snowflake for example) and a solution for a trimmed mean.

In short: don’t have either of the books? Definitely buy the second edition. If will improve the quality and effectiveness of your T-SQL programming. I wouldn’t advise this book for absolute SQL beginners, but it is a book you have to read somewhere in your SQL Server career. Doesn’t matter if you’re a DBA, a database developer or a BI developer, everyone will benefit from this book.

If you already have the first edition: I would recommend buying this book if you’re interested in the evolution of window functions, how they interact with columnstore indexes and batch mode and maybe for those extra solutions. But the extra theoretical content (e.g. row-patter recognition) is also very interesting.

Happy reading!

p.s.: I did not receive any copy of this book for this review. I got it at a promotion from Microsoft Press. I do not regret buying it 🙂
p.p.s.: the links in this blog post are Amazon affiliate links.