Welcome!

Steven Mandel

Subscribe to Steven Mandel: eMailAlertsEmail Alerts
Get Steven Mandel via: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Related Topics: Cloud Computing, MySQL Journal

Blog Post

Book Review: Expert T-SQL Window Functions in SQL Server | @CloudExpo #API #Cloud #TSQL #SQLServer

These Window functions make it easy to calculate running totals, moving averages and more

There is an old saying that good things come in small packages. This saying definitely applies to this book, which focuses in on a set of functions that most people are not aware of that are available in SQL Server. To start off with we need to answer the following question: What are T-SQL Window functions? These functions have nothing to do with the Microsoft Windows API. They are in essence a way to create a window into your data as you process each line in a query. Their strength lies in the ease with which you can solve tricky queries. The authors also point out that these functions don't add any new functionality per se as you could create the queries the old way; however, in general the functions are much more efficient.

One very neat capability of these functions is to allow you to include non-aggregated columns in an aggregated query, which is a big limitation with existing aggregate functions. These Window functions make it easy to calculate running totals, moving averages and more. Also starting with SQL Server 2012, you can add an order by to window aggregates to calculate running totals.

I like this book because the authors provide real-world examples that these functions can solve. Each chapter discusses some of the functions and, in many of the chapters, examples of how to solve real problems are provided. Here is a partial list of some of the examples provided.

  1. De-duplicating data
  2. Solving the island problem
  3. Calculating the percentage of sales
  4. Solving the subscription problem
  5. Finding gaps problem
  6. Creating queries where the row number starts over when a certain criteria of the data changes

There is also a well-written chapter on performance. It discusses how to determine how well these functions perform. It includes a discussion on tuning and how to understand execution plans and statistics. These give you a basic understanding of how to gauge performance.

This book is well worth having for any SQL developer wishing to expand his or her knowledge and skills but especially if you generate a lot of reports with a lot of analytical data. If you do a lot of number crunching with SQL Server, you'll want to buy this book.

Expert T-SQL Window Functions in SQL Server

Kathi Kellenberger and Clayton Groom

ISBN-13: 978-1484211045

 

More Stories By Steven Mandel

Steven Mandel has worked in the IT industry for over 15 years designing databases using Microsoft Access and SQL Server. He has developed Web and Windows applications using VB.NET and has written numerous articles and reviews about ASP.NET and VB.NET.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.