Advanced T-SQL: Replacing Slow Cursors with Window Functions

Question

Advanced T-SQL: Replacing Slow Cursors with Window Functions

sabyda

SSC-Addicted

Points: 459
More actions
August 3, 2026 at 12:00 am

#4884253

Comments posted to this topic are about the item Advanced T-SQL: Replacing Slow Cursors with Window Functions
Sabyasachi Mukherjee

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jpratt-797544 SSC Eights! Points: 941 More actions · Answer 1

This explanation makes sense, but what happens when there are 20,000 accounts? There is also the question of what is being done with the aggregate data once generated: Are summary tables in the database being updated? Is the data being returned to the UI, or maybe passed to a caller through an API?

Anything that can be done with a straight SQL statement such as the windowing example provided should be done that way. But, sometimes a cursor is actually the simplest approach if it can be performant.

Over the decades I have found one way to reliably make cursors faster and less impactful on memory: only select the main entity ID in the cursor DECLARE statement, then do all the querying, summing, and/or updating needed for that ID inside the cursor body.

This method supports far more index seeks for queries executed inside the cursor as well as the cursor itself, allows custom data retrieval and/or processing for different entity statuses or types, allows for detailed error logging if applicable, and supports sending detailed messages to the console when debugging (for example, if an @Verbose parameter = 1).

J Pratt

Alan Burstein SSC Guru Points: 61161 More actions · Answer 2

"But, sometimes a cursor is actually the simplest approach if it can be performant."

Respectfully - I'm struggling to think of an example of where a cursor is the "simplest approach", or where it will outperform a well-designed set-based solution like the one presented in sabyda's excellent article. If you have an example I'd love to see it!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61161 More actions · Answer 3

Second time posting this - the first time I included more than one link, which prevents your post from being published.

Great article. I wish there were more articles like this.

Let me suggest one small improvement: RowGenerator is using a recursive CTE (rCTE) to generate rows. rCTE's are slow and generate a lot of IO. Using them in the way that you did creates some of the same problems that cursors cause. Consider a tally table for this instead. See the article by Jeff Moden titled, " Hidden RBAR: Counting with Recursive CTE's".

For a faster generator consider using fnTally by Jeff Moden or GetNumsAB by me (Alan Burstein).

For links go to Google and search for: "sqlservercentral.com: fntally" or "sqlservercentral.com: GetNumsAB"

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004773 More actions · Answer 4

First, I appreciate anyone that steps up to the plate to share information. Thank you for that.

On the subject of rCTEs (rcurstive CTEs), don't use the for incremental purposes. They're actually slower and more resource intensive that even a well written WHILE loop. Please see the following article for 3 other methods prior to SQL Server 2022 (where the finally came of with the GENERATESERIES() function) that blow the doors off of rCTEs.

https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)