Join Predicates Are For Amateurs

Up here in The Cloud, we like our Data Big, our development Agile, our music undiscovered, and our organic kale locally-sourced and humanely killed. And so when these tiresome on-premise DBAs tell me things like "you need a join predicate" or "what the hell did you just do to tempdb you idiot", I laugh. When I want to pull 8 octillion rows into Management Studio, I don't let petty concerns like the fact that I can't even conceptualize what a yottabyte is (much less distinct sort a dozen of them in tempdb) stop me from catching the vision and delivering on real-time BI PowerQuery BigData that will supercharge executive decision-making. Umm, also ROI. I forgot to use that one.

I imagine René Descartes is smiling warmly while looking down from The Cloud (well, where else would he be? Surely not suffering in torment down in On-Premise!!), observing all these wonderful Cartesian joins, named after the old boy. This is an -actual- execution plan recovered from the wild:

T-SQL Tihai Generator

In a somewhat eccentric convergence of two of my interests, I decided during a slow period to write a bit of code that generates tihai patterns. Tihai is a concept in Indian classical music that fits a pattern of threes into the rhythmic cycle. You can read about it in general here or in painful detail here. Basically you have three equal musical phrases that end in a final stroke, possibly with a rest following each, but the third and final phrase ends with the stroke starting the next rhythmic cycle, on sam (first beat of cycle). I'm sure there are much cleverer ways to do this, but this worked for me (yay brute force iteration!!):


--tihai generator
DECLARE @MatrasToFill INT = 16;
DECLARE @rest FLOAT, @body FLOAT;
DECLARE @restlist VARCHAR(20) = 'X_2_3_4_5_6_7_8_9_';
DECLARE @bodylist VARCHAR(20) = 'B-2-3-4-5-6-7-8-9-';
SET @rest = 0.5;
WHILE (@MatrasToFill > 3) --Can't really make a proper tihai with 3 or fewer beats to fill
BEGIN
   PRINT 'To fill ' + CAST(@MatrasToFill AS VARCHAR(10)) + ' matras:'
   WHILE (@rest <= 5.0)
   BEGIN
       SELECT @body=(@MatrasToFill - (@rest*2))/3;
       IF FLOOR(@body) = (@body) AND @body > 0
       BEGIN
           PRINT
               CAST(@body AS VARCHAR(10)) + ' notes in body, '
               + CAST(@rest AS VARCHAR(10)) + ' notes in strike/rest.'
           PRINT
               LEFT(@bodylist, @body*2)+LEFT(@restlist,@rest*2)
               +LEFT(@bodylist, @body*2)+LEFT(@restlist,@rest*2)
               +LEFT(@bodylist, @body*2)+'|Sam';
       END
       SET @rest += 0.5;
   END
   PRINT CHAR(13);
   SET @MatrasToFill -= 1;
   SET @rest = 0.5;ENDGO

Output as follows, if anyone (like me) finds it useful when composing taans and trying to find a good pattern to fill a set amount of remaining matras:

To fill 16 matras:
5 notes in body, 0.5 notes in strike/rest.
B-2-3-4-5-XB-2-3-4-5-XB-2-3-4-5-|Sam
4 notes in body, 2 notes in strike/rest.
B-2-3-4-X_2_B-2-3-4-X_2_B-2-3-4-|Sam
3 notes in body, 3.5 notes in strike/rest.
B-2-3-X_2_3_4B-2-3-X_2_3_4B-2-3-|Sam
2 notes in body, 5 notes in strike/rest.
B-2-X_2_3_4_5_B-2-X_2_3_4_5_B-2-|Sam

To fill 15 matras:
4 notes in body, 1.5 notes in strike/rest.
B-2-3-4-X_2B-2-3-4-X_2B-2-3-4-|Sam
3 notes in body, 3 notes in strike/rest.
B-2-3-X_2_3_B-2-3-X_2_3_B-2-3-|Sam
2 notes in body, 4.5 notes in strike/rest.
B-2-X_2_3_4_5B-2-X_2_3_4_5B-2-|Sam

To fill 14 matras:
4 notes in body, 1 notes in strike/rest.
B-2-3-4-X_B-2-3-4-X_B-2-3-4-|Sam
3 notes in body, 2.5 notes in strike/rest.
B-2-3-X_2_3B-2-3-X_2_3B-2-3-|Sam
2 notes in body, 4 notes in strike/rest.
B-2-X_2_3_4_B-2-X_2_3_4_B-2-|Sam

To fill 13 matras:
4 notes in body, 0.5 notes in strike/rest.
B-2-3-4-XB-2-3-4-XB-2-3-4-|Sam
3 notes in body, 2 notes in strike/rest.
B-2-3-X_2_B-2-3-X_2_B-2-3-|Sam
2 notes in body, 3.5 notes in strike/rest.
B-2-X_2_3_4B-2-X_2_3_4B-2-|Sam
1 notes in body, 5 notes in strike/rest.
B-X_2_3_4_5_B-X_2_3_4_5_B-|Sam

To fill 12 matras:
3 notes in body, 1.5 notes in strike/rest.
B-2-3-X_2B-2-3-X_2B-2-3-|Sam
2 notes in body, 3 notes in strike/rest.
B-2-X_2_3_B-2-X_2_3_B-2-|Sam
1 notes in body, 4.5 notes in strike/rest.
B-X_2_3_4_5B-X_2_3_4_5B-|Sam

To fill 11 matras:
3 notes in body, 1 notes in strike/rest.
B-2-3-X_B-2-3-X_B-2-3-|Sam
2 notes in body, 2.5 notes in strike/rest.
B-2-X_2_3B-2-X_2_3B-2-|Sam
1 notes in body, 4 notes in strike/rest.
B-X_2_3_4_B-X_2_3_4_B-|Sam

To fill 10 matras:
3 notes in body, 0.5 notes in strike/rest.
B-2-3-XB-2-3-XB-2-3-|Sam
2 notes in body, 2 notes in strike/rest.
B-2-X_2_B-2-X_2_B-2-|Sam
1 notes in body, 3.5 notes in strike/rest.
B-X_2_3_4B-X_2_3_4B-|Sam

To fill 9 matras:
2 notes in body, 1.5 notes in strike/rest.
B-2-X_2B-2-X_2B-2-|Sam
1 notes in body, 3 notes in strike/rest.
B-X_2_3_B-X_2_3_B-|Sam

To fill 8 matras:
2 notes in body, 1 notes in strike/rest.
B-2-X_B-2-X_B-2-|Sam
1 notes in body, 2.5 notes in strike/rest.
B-X_2_3B-X_2_3B-|Sam

To fill 7 matras:
2 notes in body, 0.5 notes in strike/rest.
B-2-XB-2-XB-2-|Sam
1 notes in body, 2 notes in strike/rest.
B-X_2_B-X_2_B-|Sam

To fill 6 matras:
1 notes in body, 1.5 notes in strike/rest.
B-X_2B-X_2B-|Sam

To fill 5 matras:
1 notes in body, 1 notes in strike/rest.
B-X_B-X_B-|Sam

To fill 4 matras:
1 notes in body, 0.5 notes in strike/rest.
B-XB-XB-|Sam