Editorials

Sets Over Loops

I have been a proponent of set operations in SQL Server as being preferred over procedural logic. If possible, work with sets instead of loops, and or cursors. The primary reason is that SQL Engines are tuned to do set operations. It does filtering and joins very quickly.

Below, I have a VERY contrived query demonstrating how you can use sets to determine different kinds of data through joining different sets. I am returning a short Fibonacci sequence set of numbers. For each number, I am joining to two other lists. One list has even numbers. The other list has odd numbers.

In the select clause of the query I return the number from the Fibonacci table, a Boolean value from the even table if a record exists or not, and a Boolean from the odd table if a record exists or not. As I said, the query is contrived. It would be much easier to do the Modulus on the Fibonacci.number value itself, rather than joining to another table. However, what if you were using the Fibonacci table as a random selection to a table of purchase orders? Then the query would make more sense.

As a note, I sometimes do use cursors or loops when I am working with extremely large sets. Then I break the set down into a range of data, iterated through a cursor. You can also use variables like min/max of a range, and increment them until no records are returned. The set logic is still used; you are simply reducing how many records are in the set when processing, so that your query doesn’t require more memory than what is available in the server during the execution.

The rule of thumb that I use is, “Loops are not wrong. Sets may be better.” Don’t take the lazy way out if you are new to working with sets. Learn to use sets when possible. Don’t give up. There are lots of online quizzes or books demonstrating so many different things you can do using sets.

Cheers,

Ben


-- Example Query:
DECLARE
@Fibonacci TABLE (Number INT NOT NULL)


INSERT
INTO @Fibonacci

VALUES (1),(1),(2),(3),(5),(8),(13),(21),(34),(55),(89)


;
WITH EvenNumbers

AS

(

SELECT Number

FROM [dbo].[Sequence]

WHERE Number % 2 = 0

)

,OddNumbers

AS

(

SELECT Number

FROM [dbo].[Sequence]

WHERE Number % 2 > 0

)

SELECT f.Number
,CONVERT(Bit, ISNULL(even.Number, 0)) AS IsEven

,CONVERT(Bit, ISNULL(Odd.Number, 0)) AS IsOdd

FROM @Fibonacci f

LEFT JOIN EvenNumbers even ON f.Number = even.Number

LEFT JOIN OddNumbers odd ON f.Number = odd.Number

Returns:

Number IsEven IsOdd
1 0 1
1 0 1
2 1 0
3 0 1
5 0 1
8 1 0
13 0 1
21 0 1
34 1 0
55 0 1
89 0 1