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.
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.
I recently read the book Agile Data Warehouse Design - Collaborative Dimensional Modeling, from Whiteboard…
You can find the slides for the session Building the €100 data warehouse with the…
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
View Comments
Recently was skimming through the Query Tuning chapter of the "2008 T-SQL Querying" book. Most of it is still relevant (at least most of the concepts) even after 10 years, and the code snippets still work out of the box. I regularly scan what is being published in this space of T-SQL and even after 10 years these books have almost no peers in terms of the depth and breath of covered topics. People should be forbidden from publishing yet another SQL Beginner Guide, we need more up-to-date advanced stuff. Hopefully, Itzik is considering updating these two 2009 titles for 2020+. Meanwhile, you can follow their blog at solidq.
My employer uses Skillport, whose membership includes tens of thousands of books. This is one of them. The SQL 2016 standard has good stuff on RPR. I highly recommend it.