patternMinor
SQL Loop Use Case - Not Sure How or Why These Are Useful
Viewed 0 times
casewhythesesqlareloopsureusefulhowuse
Problem
What is a good use case for writing a loop in a SQL function? I can see a loop as useful for generating random data, but apart from that, I can only think of a few other reasons.
For example, you may receive a list of data that you need to iterate over and insert new records based on each item.
But beyond that, it's really hard to imagine what a loop would be used for. Part of the difficulty is that a lot of things that you may think to use a loop for, like deleting odd number of rows/updating odd number of rows, you can do in a query.
For example, you may receive a list of data that you need to iterate over and insert new records based on each item.
But beyond that, it's really hard to imagine what a loop would be used for. Part of the difficulty is that a lot of things that you may think to use a loop for, like deleting odd number of rows/updating odd number of rows, you can do in a query.
Solution
situations
Loops can be quite useful in many scenarios. I understand the desire to avoid them when set-based solutions are available, but not every interaction with a database can or should be done in a single query.
batching
One thing that may be unpleasant is large data modifications. Increased lock-times, transaction log growth, etc. are inconvenient at best, and may lead to all sorts of things no one wants to deal with:
This is a good article about them:
maintenance
When taking backups, checking for corruption, or updating statistics, you may not want to do them in parallel in many circumstances.
A great example of how loops can be used to deal with this are Ola Hallengren's scripts.
More recently, Ola has added options to parallelize certain activities. This is likely a response to greater hardware capabilities than anything else, though.
sampling
There are times when you'll want to sample or run data collection, and a loop is pretty well-suited to that.
I use them in my stored procedure, sp_HumanEvents, in several places. One of them is to pull data out of Extended Events sessions to log them to tables.
You'll also see loops via cursors in sp_WhoIsActive to populate
placemat
Loops and cursors are sometimes the best options for queries as well. Here are a couple examples from other Q&A on this site:
While they do have a bad reputation for good reasons, most of the time they're just misunderstood.
Loops can be quite useful in many scenarios. I understand the desire to avoid them when set-based solutions are available, but not every interaction with a database can or should be done in a single query.
batching
One thing that may be unpleasant is large data modifications. Increased lock-times, transaction log growth, etc. are inconvenient at best, and may lead to all sorts of things no one wants to deal with:
- Long blocking chains
- Running out of worker threads
- Filling up a drive
This is a good article about them:
- Take Care When Scripting Batches
maintenance
When taking backups, checking for corruption, or updating statistics, you may not want to do them in parallel in many circumstances.
A great example of how loops can be used to deal with this are Ola Hallengren's scripts.
More recently, Ola has added options to parallelize certain activities. This is likely a response to greater hardware capabilities than anything else, though.
sampling
There are times when you'll want to sample or run data collection, and a loop is pretty well-suited to that.
I use them in my stored procedure, sp_HumanEvents, in several places. One of them is to pull data out of Extended Events sessions to log them to tables.
You'll also see loops via cursors in sp_WhoIsActive to populate
- query text
- query plans
- locking and blocking
- agent job names
placemat
Loops and cursors are sometimes the best options for queries as well. Here are a couple examples from other Q&A on this site:
- Rejoining range seek on nullable composite index?
- Most cost efficient way to page through a poorly ordered table?
While they do have a bad reputation for good reasons, most of the time they're just misunderstood.
Context
StackExchange Database Administrators Q#318593, answer score: 4
Revisions (0)
No revisions yet.