patternsqlMinor
When are procedural queries absolutely necessary?
Viewed 0 times
necessaryabsolutelyareproceduralwhenqueries
Problem
I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not give you the results?
I understand the difference between the two, I just have never come to a situation where I need to use a cursor. I'm wondering if there are such situations.
I understand the difference between the two, I just have never come to a situation where I need to use a cursor. I'm wondering if there are such situations.
Solution
In my experiences, I've run into a few times when procedural/iterative approaches were warranted.
API only allows for single-row operation
If I wanted to programmatically change the data type from real to decimal in a table that has 500 mis-typed columns like this SO question asks, a cursor is a fine approach as the DDL does not allow for altering multiple columns in a single statement.
Set based doesn't scale
If you have the SQL Server MVP Deep Dives book, chapter 4 "Set-based iteration: the third alternative" by Hugo Kornelis has some great use cases for combined cursor/set based operations. Two of classic problems that the chapter author references is the Running Totals and Bin Packing.
I used the set-based iteration approach with good success for a poorly designed process I inherited at the last job. In short, there was a process that once a year had to update 50-75M rows and attempting to do so in a single set would blow our logs. By chunking the updates into smaller batches of N rows, it allowed the log to keep up and actually finished faster than the previous year when they just allocated a metric ton more disk space.
API only allows for single-row operation
If I wanted to programmatically change the data type from real to decimal in a table that has 500 mis-typed columns like this SO question asks, a cursor is a fine approach as the DDL does not allow for altering multiple columns in a single statement.
Set based doesn't scale
If you have the SQL Server MVP Deep Dives book, chapter 4 "Set-based iteration: the third alternative" by Hugo Kornelis has some great use cases for combined cursor/set based operations. Two of classic problems that the chapter author references is the Running Totals and Bin Packing.
I used the set-based iteration approach with good success for a poorly designed process I inherited at the last job. In short, there was a process that once a year had to update 50-75M rows and attempting to do so in a single set would blow our logs. By chunking the updates into smaller batches of N rows, it allowed the log to keep up and actually finished faster than the previous year when they just allocated a metric ton more disk space.
Context
StackExchange Database Administrators Q#4610, answer score: 9
Revisions (0)
No revisions yet.