Set Based Recursion
When a developer first transitions to working with SQL or set based code they begin to apply the same techniques used in a procedural language. Cursors are very attractive because they work within a loop very much like a “for” or ‘foreach” structure. We know how to work with them intuitively because they work much as we have done previously. The problem is that cursors are rather slow in performance, and take up a lot of resources.
Cursors can be a cause of much locking or blocking activity on a database. They aren’t the end of the world, and can be used effectively. However, there are set based alternatives.
One of my favorite tools is a sequence table or number table consisting of a sequential set of numbers from zero to , well, as many as you need. I always have a sequence table available in some database in any instance of an SQL Engine. Sometimes I even put it into my model database for SQL Server so that every database created will have a sequence table.
Sequence tables are like magic. The first time I saw one demonstrated I was blown away by how powerful y it performed, how simple it was to use, and the flexibility for using it for different kinds of features.
For example, if you wanted to find every position of the letter B in the string “The Big Bad Boy had Blue Britches on his Bum” you could find it with a sequence table or a loop. Either one will work well. But observe how easily this can be done with a sequence table…
DECLARE @String VARCHAR(4000) = 'The Big Bad Boy had Blue Britches on his Bum'
DECLARE @Length INT = DATALENGTH(@String)
SELECT Number
FROM Sequence
WHERE Number BETWEEN 1 AND @Length
AND SUBSTRING(@String, Number, 1) = 'B'
There you have it, a single command returning the character position of the letter B from the entire string.
Maybe tomorrow I’ll leave a few examples of my favorite things to do with a Sequence table. Feel free to add your favorites below, and drop me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
What’s In a Name? On Database Naming Standards
Why do those of us in IT spend so much time and effort developing and enforcing naming standards? Maybe what something is called is not quite so trivial a matter after all!
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)
Featured Script
Save / append logfile (ASCII)
This generic proc appends/creates an ASCII (log) file to an alternate place…. (read more)