Review: “
Expert T-SQL Window Functions in SQL – Master The Most Useful Addition To SQL In Over A Decade” by Kathi Kellenberger with Clayton Groom, Apress, 152 pages. Paperback > ISBN-13: 978-1-4842-1104-5, eBook (Kindle) > ISBN-13 (electronic): 978-1-4842-1103-8.
This book is a gold mine of tips and techniques using T-SQL Window Functions in SQL Server. My approach to writing effective and efficient queries has definitely changed after reading this book.
SQL queries often require complex expressions to yield the desired data set from the tables in your database. While many books provide an exhaustive presentation of basic to intermediate SQL expressions, few tend to delve into, or fail to even mention, more advanced techniques.
Knowing only the basics of SQL can lead to the creation of unnecessarily complicated queries. However, these complex queries can be simplified to a great extent by using the techniques presented in “Expert T-SQL Window Functions in SQL Server” by Kathi Kellenberger with Clayton Groom (Apress). Paperback ISBN-13: 978-1-4842-1104-5 (paperback) / eBook ISBN-13: 978-1-4842-1103-8.
Why we need this book
Window functions can only appear in the SELECT or ORDER BY clauses. That an entire book is devoted to using them effectively, suggests there is more to learn than just syntax rules and a few key words.
Solutions for typical queries can be found online, however, the explanation of the code presented is usually limited to the scope of the application being presented or discussed. For example, the following statement will add row numbers to any SQL data set:
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowID, *
FROM TableName;
Although this satisfies our immediate requirement, there are more options available with the OVER() function than the example above demonstrates. According to Microsoft’s documentation, the syntax for ROW_NUMBER() is as follows:
ROW_NUMBER() OVER([PARTITION BY value_expression, ... [n]] ORDER BY clause)As you continue reading, you discover that there’s much more to learn about each component of the statement and eventually realize that ROW_NUMBER() is just one of several functions that fall into a broader category of Window functions such as RANK, DENSE_RANK, and NTILE.
Many people stop learning when they’ve found what they think is the solution for their query. In this case, stumbling onto a window function such as ROW_NUMBER() is literally only scratching the surface.
The latter point becomes evident when the OVER() clause is used in conjunction with aggregate functions in a SELECT statement without requiring a subsequent GROUP BY clause while preserving the original data set. Though similar results can be achieved through other means, use of the OVER() clause greatly simplifies the query and may improve the overall performance of the query itself.
Combing through online documentation is typically a fragmented process, leaving you to put the pieces of the puzzle together for yourself. Expert T-SQL Window Functions in SQL Server is a succinctly coherent presentation of Window functions with detailed explanations and practical examples to demonstrate their use, thereby saving you a lot of time and effort.
This is not a book for beginners. The content is focused on the Window functions themselves and, considering the context in which they are used, you should have at least some experience writing SQL Queries before reading this book. Understanding select statements using a variety of joins, subqueries, and / or common table expressions is pre-requisite knowledge.
Chapter 3 demonstrates how Window functions can be used to enhance working with existing aggregate functions. One example introduces custom aggregate functions using C# to find the median of a data set. Read “Advantages of Using Managed Code to Create Database Objects” for more information on this topic.
Improving Performance
One of the intriguing aspects of SQL is performance. For users, execution time is all that matters where queries are concerned. While there are often several solutions that can be used to create the desired data set, they may vary significantly where performance is concerned.
SQL Server Management Studio has several tools available that will help to identify opportunities to improve execution time and overall query performance and is the topic of chapter 4, “Tuning for Better Performance”. Execution Plans, Statistics IO, and Time Comparisons are used as a guide to identify specific elements where improvements can be realized.
The first three chapters of the book focus on window functionality introduced in SQL Server 2005, where chapters 5 through 9 discuss features starting with SQL Server 2012. The concept of frames provides even greater granularity to the data set being queried and a new powerful level of aggregate functionality.
It is worth mentioning how few lines of SQL code are required to solve the “Subscription Problem” using the new aggregate features in SQL Server 2012 as compared to the winning solution presented in 2009 when the “Subscription Problem” competition was held. The T-SQL solution that returned the correct results with the fastest time determined the winner of the competition.
Final Words
This book provides a comprehensive discussion of Window Functions complete with examples to demonstrate their application. Window Functions are extremely powerful and can be used to develop solutions that would otherwise require long and complicated queries.
You owe it to yourself to read Expert T-SQL Window Functions in SQL Server to leverage the powerful capabilities Window functions will bring to your SQL queries. The majority of the examples in the book are based on the AdventureWorks database which is typical to most SQL Server installations. Additionally, code is available for download from the book’s website.
I highly recommend the Kindle (eBook) edition: eBooks are offered at a lower price, can be read on multiple devices using the free Kindle app, are good for the environment, and save you from having to carry and possibly lose your hardcopy!
Related Reading
Advantages of Using Managed Code to Create Database Objects. This extract from Microsoft’s webpage summarizes the context of the article:
“In addition to the Transact-SQL programming language, you can use .NET Framework languages to create database objects and retrieve and update data for SQL Server databases. In Visual Basic or Visual C# projects, you can create stored procedures, triggers, aggregates, user-defined functions, and user-defined types. Database objects that are written in managed code are called SQL Server Common Language Run-time objects or SQL CLR objects.”
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference) – 1st Edition, Itzik Ben-Gan, 224 pages, Microsoft Press, ISBN-13: 978-073658363 (Paperback).
Writing Efficient SQL: Set-Based Speed Phreakery. This article, written by Kathi Kellenberger, author of “Expert T-SQL Window Functions in SQL Server“, discusses an SQL code competition where the fastest query yielding the correct results wins. A variety of solutions are presented and demonstrates the performance differences between them although the resulting data sets are the same.
Until Next Time …
