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.