Welcome!

@CloudExpo Authors: Yeshim Deniz, Elizabeth White, Pat Romanski, Liz McMillan, Zakia Bouachraoui

Related Topics: @CloudExpo, Microsoft Cloud, @DXWorldExpo

@CloudExpo: 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.


CloudEXPO Stories
Andi Mann, Chief Technology Advocate at Splunk, is an accomplished digital business executive with extensive global expertise as a strategist, technologist, innovator, marketer, and communicator. For over 30 years across five continents, he has built success with Fortune 500 corporations, vendors, governments, and as a leading research analyst and consultant.
Nicolas Fierro is CEO of MIMIR Blockchain Solutions. He is a programmer, technologist, and operations dev who has worked with Ethereum and blockchain since 2014. His knowledge in blockchain dates to when he performed dev ops services to the Ethereum Foundation as one the privileged few developers to work with the original core team in Switzerland.
Most DevOps journeys involve several phases of maturity. Research shows that the inflection point where organizations begin to see maximum value is when they implement tight integration deploying their code to their infrastructure. Success at this level is the last barrier to at-will deployment. Storage, for instance, is more capable than where we read and write data. In his session at @DevOpsSummit at 20th Cloud Expo, Josh Atwell, a Developer Advocate for NetApp, will discuss the role and value extensible storage infrastructure has in accelerating software development activities, improve code quality, reveal multiple deployment options through automated testing, and support continuous integration efforts. All this will be described using tools common in DevOps organizations.
DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
Dynatrace is an application performance management software company with products for the information technology departments and digital business owners of medium and large businesses. Building the Future of Monitoring with Artificial Intelligence Today we can collect lots and lots of performance data. We build beautiful dashboards and even have fancy query languages to access and transform the data. Still performance data is a secret language only a couple of people understand. The more business becomes digital the more stakeholders are interested in this data including how it relates to business. Some of these people have never used a monitoring tool before. They have a question on their mind like "How is my application doing" but no idea how to get a proper answer.