All the Tools in Your Toolbox

I recently began writing an article to compare the performance of several window functions to traditional query methods. I began with my favorite function, LAG, but soon found that there were so many other ways to write the query that the article never covered the additional window functions.

The article was quite fun to write, and I learned that using a temp table to pre-aggregate the rows improved the performance of all other methods. An OUTER APPLY with the indexed temp table was almost as fast as LAG. I even found that a cursor was faster than several of the methods that I tried.

At first, I hesitated to include the cursor solution in the article because I didn’t want to encourage anyone to write a cursor in place of a set-based approach. I decided to include it because it performed so much better than some of the other methods, though, I wouldn't recommended it in any case.

In my DBA days, I used cursors in some of my admin scripts to do something on each database, server, or user, for example. Many might say, use sp_MSforeachdb for databases instead, but I don’t see the difference since it loops through the databases as a cursor might. At least, I found that the cursor gave me more flexibility and worked when it had to be one action at a time. Of course, you could also use PowerShell (still loops!) for most admin tasks today instead.

Before Microsoft added several new window functions in 2012, a cursor may have been a fast solution for quite a few tricky queries. The old Speed Phreakery articles showed that there were faster solutions than a cursor back then, but they were challenging to write. That all changed in 2012 with functions like LAG and moving aggregates.

Over the years, I’ve heard things like “just turn it into a table-valued function” or “APPLY is always faster than a join,” or “never use a CTE if you care about performance.” Like everything else in the database world, it depends. Even your least used T-SQL method is still a tool in your toolbox. It may be rusty from disuse, but it's there when you need it.

As my article demonstrated, there can be many ways to solve a problem without getting stuck with one methodology. If one way has poor performance, there are likely other techniques you can try.

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

IDC’s recent Global DataSphere Forecast, 2021-2025 predicts that global data creation and replication will experience a compound annual growth rate of 23% over the forecast period. But what’s causing the growth? Where will all of that extra data be stored? And how should we, as data professionals, prepare for it? Read this blog to explore these important questions.

A few days ago, we looked at how to check for enab...

Quick query today. I needed a list of database siz...

Extended Events have been part of SQL Server since...

This article will explain the impact of TDE (Transparent Data Encryption) on one of the crucial system databases, tempdb. Everybody wants to secure their data to prevent unauthorized access...

Steve Stedman 0:15 So Derrick, one of the things we run into is customers who are a little bit resistant to running CheckDB as frequently as maybe we think...

Reading Time: 3 minutes For my T-SQL Tuesday cont...

This article explores the Azure built-in policies ...

This article will explore the Azure alerts configu...

Continuing the topic of High Availability from the...

First step login into the Azure portal and find SQ...

We continue investment into #sqlmiops, and after r...

Registering Windows 10 devices with an identity pr...

The team at DCAC is proud to say that we have memb...

As with all programming tasks there are a number o...

Introduction In my previous post, I covered the us...

An Adminstrator asked how to use Powershell for adding members to Local Administrator group

Recently I have taken part in various discussions about why there is an increase in demand for Data Platform automation...

Throughout my career as an IT consultant, working ...

This article aims to walk you through the process ...

This article intends to give comprehensive details...

Paul Randal closes out his series on latch bottlen...

While I have not yet signed the contract, I have s...

I was recently looking on how to use SQL Profiler ...

Ever run into an issue where a duplicate name does...

In the world of modern technology, we can all unde...

My post earlier this year on enabling query foldin...

In this article, we are going to talk about the Az...

It is all about measuring time using useless clocks. Script takes a system time and displays any given clock in a rather “static” way. You can choose between analog,...

Replication is one of my least favorite thi...

Learn how to use IIF, Switch and Choose logical functions in SSRS reports to make your reports more dynamic and more appealing to report users.

SQL Server 2017 Cumulative Update 26 On July 12th,...

In this article we cover how to setup partitioning...

Learn how to convert SQL Server data to different data types such as string, date, integer and numeric using the CAST and CONVERT functions.

Have you ever needed to delete duplicate records f...

Russian Nesting Dolls If you have ever seen the Ma...

Hi Everyone, this is John Sterrett. I am a SQL Ser...

Any SQL Server DBA, Developer, or end-user always ...

In this blog today, let's configure AD (Active Dir...