T-SQL Tuesday #119: Change of Mind

Get up, come on get down with the SPACES!

Advertisements

Because I’m super late (as usual), about to go to bed (because I’ll be commuting tomorrow), and lazy (aren’t we all?), this will be a quickie.

This month’s #tsqltuesday hosted by the amazing Alex Yates, in which he asks us to discuss something about which we’ve changed our minds over the course of our career (or some subset of time therein).

I’m really excited to read some of the submissions I’ve skimmed on the Twitter feed so far, such as Oracle vs. MS-SQL and the importance of diversity. After all, what else am I gonna do while I sit in the vanpool for 3 hours? (round-trip, thankfully, not one-way!)

Tabs vs. Spaces

Oh my! Them’s fightin’ words. Even back in the early days of this very blog, I wrote about my preference for tabs. But now.. *gasp*.. I’m down with the spaces!

blasphemy-300
Blasphemy of the highest order!

Why, you ask?

Well, partially because I’ve changed some of my overall T-SQL coding style preferences and methods of construction. When I learned the Alt-Shift select method (block selection, aka vertical selection) in SSMS, it definitely set me on a track away from tabs. Now I don’t go all cray-cray with vertically aligned sections/clauses/etc. too much, but I will say that in certain instances, it’s made the query much easier to read. And in such instances, spaces definitely trump tabs for ease-of-use with said vertical-alignment efforts.

If you’re not sure what I’m talking about (because, admittedly, it’s hard to write about and much easier to show visually), just search Youtube for an example of SSMS block-select tricks.

And this is within the last 4 years, so I still find old stored-procs that I’ve written that have the tabs, and I chuckle slightly to myself as I Ctrl-K-Y (that’s the Red Gate SQLPrompt shortcut to ‘format this code in my current style’) and make my modifications.

Miscellaneous Little Things

I’ve developed some other preferences, too, which contradict some of my old formative-years’ habits. For example, I used to write my TSQL in pure lowercase. I now prefer the ANSI-CAPS for language constructs and keywords, but if I ever need to write dynamic-SQL, it goes in lowercase.

caps-lock-not-always-necessary
I even re-used old images, instead of finding new ones. LAAAZZZYY!! :O)

Some of these habits come from Aaron Bertrand and other SQL-community big-name bloggers. Like preferring CONVERT over CAST, or changing from trailing-commas to leading-commas. (Although he may have flipped on that again, I can’t remember.) While others just kinda happened organically. Like, two tabs for the ON line under each JOIN — the join predicate — just felt silly after a while, so I reverted to one. I used to be stickler for forcibly quoting identifiers that collided with language keywords — like if you have a column named Date or Value, you best be puttin them square-brackets around those suckers ([Date], [Value]), but now.. honestly, I don’t care enough to bother. Unless you do something really heinous, like timestamp. =P

Anyway, that’s all I have for now. There are much more important things that I could, and should have, written about, but as I said, and as always, I’m already late to the party. ‘Til next time! ❤

T-SQL Tuesday 118: Fantasy Feature

It really shouldn’t be this difficult. But it is. And that’s why we get paid. Still, it’d be nice if load-testing were easier, wouldn’t it?

Aka “hey look at us Microsoft, we want stuff!!” Because they shut down Connect and its replacement (Azure Feedback aka rebranded UserVoice) is awful. Just plain terrible. In unrelated news, I’ve never been an MVP.. wonder why? 🙄😜

Anyhoo, this month’s invitation is brought to us by the lovely and talented Kevin Chant. He asks us to fantasize about SQL Server. No, not like that Erik, get your mind out of the gutter.

And I don’t apologize. At all.

This IS a complaint. But hopefully it’s also an idea for those who are better at building stuff than me to.. ya know.. build stuff.

And shout-out to my favorite fellow blogger Shane (@SOZDBA) who’s too polite for his own good. ❤

Load Testing is HARD.

Too hard. So hard that nobody does it. At least not productively, efficiently, or willingly. About the only times that I can personally point out an instance where I’ve actually buckled down and done something roughly comparable to a true load test (which I’ll talk about in a minute), was when I was forced to do so by my managers to prove that a hardware environment upgrade hadn’t gone awry, and that our servers truly were running at least as good as, if not better than, before.

But guess what? We never really proved anything conclusively. We had inklings, feelings, warm fuzzies.. okay maybe a DiskSpd output file or two.. which indicated that things were “mostly probably pretty good and kinda sorta better.”

Why? BECAUSE IT’S FREAKING HARD.

What is “True Load Testing”?

a graphic representing software load testing
Something like that… maybe?

Glad you asked. Simply put, it’s the ability to execute these 3 steps, easily and efficiently, with minimal configuration overhead and without needing to pour agonizingly over tomes of docs:

  1. Capture and store a SQL server workload — i.e. ALL the transactions run against an instance in a given time frame — AND performance metrics from said instance while said workload was running.
  2. Run that captured workload against another SQL server instance, and capture THE SAME performance metrics.
  3. Compare results from each set of gathered performance metrics, with a concise, easy to understand rating system that tells you which instance ran better and why.

Now, could we argue that the “capturing” of #1 adds some overhead to the instance? Sure! So I’m fine with NOT gathering the performance metrics during the same window as the workload-capture. Put it off to step 2, where we replay said workload against 1 or more instances and measure the performance on them. So we could replay the same workload on the original instance, and a new one, and we’d have our two sets of measurements to compare.

Got me? Good.

The Plumbing is There

I know. I know what you’re screaming at your monitor/screen right now. “But Nate, that’s exactly what Distributed Replay is for!!”

Bruh, have you even USED Distributed Replay?!? It’s way too complicated to set up, let alone manage and operate. Remember what I said about tomes of docs? Yeah. ANGTFT.

That’s the sad part. Microsoft has built up the plumbing and scaffolding for all of this over the past few decades. But we’ve yet to see that final layer, that chrome polish and finishing touch that makes the user go “Ahhh, now THAT was an educational and enjoyable experience!”

red easy button
Staples’ trademark be damned!

Obviously when it comes to performance metrics we’ve got a huge wealth of knowledge in the system DMVs. Great! Now let’s condense and simplify those into like 4 key ratings of your instance, for those of us who aren’t Paul Randal or Glenn Berry.

Oh, 3rd party monitoring products you say? Sure! Great! Love em. Do they do what I just said? Nope. Because “it depends.” Anybody else sick of hearing that?

It Really Shouldn’t Be This Difficult

But that’s why we get paid. Because it is. And no matter how many cloud services Azure & AWS try to shove down our throats, the reality is that enterprises will continue to rely on human engineers to prove (or disprove) that NewFancyServerX is better than OldCrappyServerA for running YourTerribleSqlWorkloadZ.

Because we can’t architect perfection. And we live in the real world where business decisions and financial constraints have an actual measurable impact on our technology stack choices and roadmaps. So I’m not saying it’s inexcusable that we don’t have this — this easy, measurable, understandable toolset for performance-load-testing — yet. I’m just saying it’s mildly annoying. And perhaps a little frustrating.

With that, I think I’ve written two angry rant-y posts in a row, so I do apologize to you, dear reader (but not, and never, to Microsoft). I’ll leave you with this cute picture of my dog being ridiculous, because it always makes me smile. Til next time!

husky dog laying on back in silly position
doggo being doggo

Intermission: Update Fatigue

Just try to be conscious of how inconvenient it is to be constantly asked for updates all the time.

Wow it’s been a while! My apologies dear reader. July and August came and went far too quickly. While I try to cobble together part 2 of my replication post, allow me a short interim rant.

Software updates are a fact of life

Sure, I get it. Everybody wants to keep their apps up-to-date and patched against all these vulnerabilities and exploits that the forces of evil come up with every day. Fine. Or the eager developers want to release new features that marketing (ugh, marketing) promised to stakeholders. Whatever.

oprah yelling "you get an update and you get an update"
EVERYBODY GETS AN UPDAAAAAAAAAATE!

Can we all admit that we’re getting just a little sick and tired of it? I mean seriously. Seems like every damn day something yells at you from your phone or your tablet or your laptop or your watch or your smart-TV or your talking refrigerator (well, hopefully not, but I’m sure it happens) wanting a new update.

And I work in the freakin industry, for god’s sake! I KNOW these updates are generally for the best and generally a good idea to install sooner than later. But it still makes me grumpy.

Yes, we’re all Agile and DevOps-y and Unicorn-y

And all those other silly buzzwords. That’s great. Really, I’m not suggesting we go backward. There’s no arguing that, as a general function of the evolution of the software development lifecycle and the push for better build-test-release-deploy-operate-feedback-repeat pipelines, overall software quality and user-experience has improved.

yeah science bitch
Because reasons.

Yet, sometimes, it’s super inconvenient. How many of us have bemoaned an unintentional Windows update that sucks up hours of our productivity time just because we didn’t know enough or pay enough attention to catch the “do this later” option? If it was even given!

Another example. iTunes had been begging me for weeks to update my phone’s OS, whenever I plugged it into the laptop just for charging (sure, I could not use a USB port and switch to a pure power source, but again, convenience!). So I finally let it, thinking “Oh this’ll only take a few minutes”. 15 minutes later, late to catch my vanpool ride from work… You get the picture. And why? Because Apple just HAD to give me all these new features.. that.. wait for it.. ONLY apply to iPhone X’s and above! (I have an 8+). Hmm. Something seems maybe not quite ideally efficient here.

Yeah yeah, platform consistency blah blah unified codebase blah blah. Spare me. They have the resources to make this a smarter, more bespoke process. But that’s not the point.

Even now, at this moment, Red Gate’s SQL Prompt (and I love this tool, don’t get me wrong) is asking me to update it from 9.5.14 to 9.5.15. Does it give me any features or fixes that I really care about? Doubtful. Does it bug me every time I start up SSMS? Yep. Can I dismiss it or say “remind me later” or “skip this version”? Of course! So at least they’ve given me that courtesy.

So what IS your point?

You ask me that a lot, don’t you?

All I ask is that developers, in general, be more conscious of how inconvenient it is to be asked to update their apps all the time. Architect things in such a way that back-end fixes and improvements are de-coupled from the UX/front-end. As much as possible. Obviously this isn’t always feasible, and sometimes you literally do need to fix the UX. Great! But with more careful, thoughtful design, this should be far less frequent.

yo dawg i heard you like windows updates
You can’t go wrong with the classics.

‘Should’, of course, being the operative word. We’re still human. We still design and create systems with human assumptions and human error. I get it. Believe me, my code is FAR from perfect. If I had to put out a fix to every stored-procedure I wrote as often as they were found, by a user-base of any more than just myself and my dozen developers, I’d go insane. (-er.) Fortunately, those don’t require people to download an update package and wait for it to install. 😉

Anyway. Hope you enjoyed this rant. Now go update your apps and tools because they’re important. And probably vulnerable to some new zero-day exploit that’s going to take over your system and steal your cookies and bitcoins. =P

Replication “Just Trust Me”

For what seems like years, I’ve bemoaned the fact that SQL Transactional Replication doesn’t come with a “Just Trust Me” option. I’ll explain more about what I mean in a moment. The other thing I’ve complained about is that there’s no “Pause” button — which not entirely accurate, since obviously you could just stop the distribution and subscription agents. But specifically what I mean is, it’s not easy to ‘put it on hold so you can make some schema changes to one of the tables that’s being replicated’, and then easily “Resume” it after you’re done with said changes.

Well, I’m happy to say that now I have both of these tools/methodologies in my arsenal!

Quick level-set: If you’ve been living under a virtual rock, SQL replication is an old-hat “tried-and-true” method of producing readable copies of your data on other SQL servers, whether for reporting or DR. It’s not an HA technology per-se, although I suppose you could use it for that if you were feeling adventurous. It’s more for “I need a reasonably up-to-date copy of my data ‘over there’ so I can run reports / crappy user-formed / EF-generated queries against it without slowing down my production OLTP system.”

Yes, I did just take a pot-shot at Entity Framework. #DealWithIt

i don't always break replication but when i do it drives me to drink
But not Dos Equis. That stuff is terrible. =P

Why?

The word that comes to most DBA’s minds when they think of replication is ‘brittle’. And for good reason — when it breaks, it breaks hard, and you’re often left trying to pick up the pieces while wondering how much worse it could be if you just started over from scratch (i.e. dropped all the replications and re-created them). Which, honestly, sometimes is easier. But not if you have a large volume of data, and certainly not if that data is indexed and you don’t want your apps to experience a performance-crisis!

Now, because this post has been sitting in my ‘Drafts’ area for far too long, I’m going to break this up into 2 parts, so I can get something out the door. In part 1, I’ll briefly explain each of the key components of the process. In part 2, I’ll dive into a little more step-by-step detail.

Primary resources that went into this: docs, article1, article2, article3. And my very own dba.SEanswer where I apparently went through a similar process back in 2016 and subsequently forgot about it (mostly).

Key 1: Sync-Type

TL;DR: the “Just Trust Me” option is, when you create the subscription, sys.sp_addsubscription, specifying the @sync_type = 'none' parameter value. Huge thanks to @garethn in the SQL Community Slack.

Sidebar: if you haven’t yet joined the SQL Community Slack, WHAT ARE YOU WAITING FOR?!?!? DO IT, DO IT NOW!!!

Ahnold ‘teh Governator’

@sync_type = 'replication support only' may be applicable in some scenarios as well, but I’m not 100% clear on the difference / use-cases at the moment. More to come later, hopefully.

Key 2: Script Publication Procs

Protip: sys.sp_scriptpublicationcustomprocs @publication = 'PublicationName' generates the internal repl-procs that control the table creations/updates on the subscriber. You run this ‘script’ command on the publisher, then get the results (the script it generates), copy-paste to a new SQL file, and run on the subscriber.

This has come in handy on several recent occasions, wherein I had to either swap tables behind-the-scenes due to a PK change, or make a column & index change that involved truncation. Using the “stop, shuffle, start” method, which I’ll get into in part 2, I’m able to tell the subscriber “Hey, the definition of this table has changed, you need to grab these new repl-procs so you can handle it correctly!”

Key 3: Publication Properties

In order to tell our publication that “We’re gonna be making some changes, don’t panic!”, we want to turn OFF 2 properties (assuming they’re true, which they likely are by default) using sys.sp_changepublication @publication='MyPub'. The properties are 'allow_anonymous' and 'immediate_sync', and you simply append the arguments to the proc call like so: @property='allow_anonymous', @value='false' / @property='immediate_sync', @value='false'.

Later, after we’re all done with our under-the-hood changes, we’ll want to turn the back on, in reverse order: first enable 'immediate_sync', then 'allow_anonymous'. Cool? Don’t ask me why; DBAs much smarter than I have decreed it so.

OMG, remember Xena Warrior Princess? Holy wow that’s some nostalgia for ya.

Honorable Mention: Pull Subscriptions

In one instance, I was using a PULL subscription (as opposed to PUSH). I had to re-start the Distribution agent (on the subscriber) twice for it to work (to start actually synchronizing). It STILL shows as ‘Uninitialized Subscription’ in the repl-monitor, though. Kinda annoying.

Pull subscriptions can be nice because they shift the burden to the subscriber DB, so that your publisher (master, primary, whatever you wanna call it) doesn’t get too bogged-down. But as always, there are trade-offs. Check out this handy little comparison guide on the topic from a fellow DBA blogger.

That’s all for now; stay tuned for more as I go into detail about how I used these in what scenarios. Thanks for reading! ❤

Facepalms Per Hour

My current velocity is sometimes measured in FPH – facepalms per hour.

This is a rant. Fair warning.

I guess the new ‘Millenial’ colloquialism for “grumpy” or “sarcastic” is “salty“. So I’m feeling extra salty this week. For several reasons. One, it’s audit season. Two, I had to churn out about a dozen new reports in the span of 4 days because the manager who was supposed to be tracking that project dropped the ball and forgot they were due by the end of this month until… yeah, last Friday. Wheeeeee!

Thus, I decided, my current ‘velocity’ (a SCRUM/DevOps term for “how much work are you getting done”) shall be measured in FPH – Facepalms Per Hour. Currently I’m at 3. Earlier this week I was approaching the double-digits, when the lovely report consumers kept thinking of “just one more little thing” they forgot about until after I’d delivered the ‘final’ product.

‘Final’ actually being a meaningful adjective in this context approximately NEVER.

facepalm original picard
The original gangsta.

Change Logs

How best to describe this scenario while still maintaining separation of “real job” from “blog land”… Hrm. So let’s say we have a CRM, like most companies. This stores customers, among other things, in a database. And since it also stores sales transactions and financials, it’s heavily audited — it has a lot of change-tracking mechanisms.

Now, auditors come along and want a report of some specific type of change over time. I happily oblige. Then… PANIC! And not at the disco. “What are all these changes to these customers by these users who don’t have permission to make said changes?!?”

K, calm down sparky. Try not to sound the alarm; auditors are a sensitive bunch.

Turns out, those changes are, in a word, “fake”. You see, there’s this background “customer sync” process that keeps them up to date with another part of the CRM where the actual changes were made. But, because it’s written poorly, it thinks that ANY field change, even just the Name or Address (which a lot more CSR’s, customer service reps, have the permission to change, because, you know, that’s their job), constitutes a change to the ENTIRE customer record on the other end. So the change tracker logs a change to every single field on the receiving end of that sync process, even though nothing really changed on the source side except maybe one or two fields.

With me so far? Great. So now the question is, “Well, can we get a report that doesn’t show those ‘fake’ changes?” But wait, it has to be “system generated” and you’re not allowed to “filter” or “add special exceptions” to it, because it still needs to be audit-able.

So what you’re saying is, give me a report that shows me what I care about, but you’re not allowed to change the logic behind said report.

Riiiiiiight.

So I give them a new report. I don’t explain how the sausage is made, I just make it and serve it up. “But why is this different from the original report?”

double facepalm

Well, do you want the audit-able answer, or the real answer? The audit-able answer is, “We made a system change that allowed us to prevent the ‘fake’ changes from being logged incorrectly.”

The real answer is, “B*tch, I AM the system!” — meaning yes, I excluded those with some hacky logic, and you need to stop asking questions about it.

Anyway. Change Logs are super fun.

Reports

Speaking of reporting. I could really go on for pages about how terrible and broken this whole system of “request-based report development” is. But it’s frankly all we have right now. Until there’s sufficient business buy-in to the concept of agile data warehousing and collaborative cross-functional data modeling, shit just comes in one funnel and goes out another with a little sparkle spackled to it. And we call it a report.

Example, you say? Sure! Let’s say we run a special sale on certain types of widgets every quarter. We want to track how these ‘specials’ perform — do they increase our sales of those widgets? By what factor, compared to the other not-on-sale widgets? Can we trend this over several quarters?

Oh but wait. The data structures that govern widget pricing and time-span-based sale pricing, and the logic that relates customer orders to what pricing structure they used at the time of ordering, is awful, terrible, and changes every time there’s a new quarterly promotional sale.

So you’re saying you want a report that trends sales of widgets based on arbitrarily changing promotional pricing as compared to other widgets that may or may not be subject to ‘normal’ pricing during that same time period, all without a simple definitive data-point that says “This is a Quarterly Promo sale, and That is Not.”

elrond's facepalm
Mister Anderson… I mean, wait.

Let’s try to get at the root of the problem, shall we? The business doesn’t seem to understand that the way they implement promo-sales is detrimental to long-term/comparative reporting. The data model makes this harder, not easier. Can we perhaps put some heads together and come up with a compromise that both A) makes more business sense, and B) improves the data model to be a bit more intuitive?

In Closing…

What’s your FPH? What causes you to facepalm on a regular basis? Let me know in the comments!  :o)

T-SQL Tuesday #115: Dear 20-year-old Self

Don’t be afraid of that big change, that big opportunity.

This month’s #tsql2sday is brought to you by Mohammad Darab, a relatively new #SQLSaturday speaker (congrats!) and all-around-great-guy. Feeling introspective, he invites us to write a letter to our 20 year old self. Quite coincidental that I’ve been in a “letter writing mood” lately on my other blog. 😉

Dear Nate_the_College_Student

A couple things. First, DTB (rated PG-13 link). SRSLY. The high school girlfriend is NOT the one, nor does she treat you well at all. It’s gonna be over a year after you graduate, so do yourself a favor and break it off now so you can enjoy college more.

Second, study more, game less. It’s gotten ridiculous. You had straight A’s. You’ll get your first D EVER if you don’t take that network programming class seriously. And it will hurt. Your ego AND your GPA.

Finally! Hardware ain’t for you. Software is where it’s at. And data stuff. ALL the data! Someone’s gonna coin the phrase “data is the new oil”. Hey! That should be YOU! Do it.

Where the Rubber Meets the Road

Your first job is going to be amazing. You’ll spend NINE years at that company. Probably a bit too long. They weren’t very understanding when your wife was very sick and needed your help running around to doctors and pharmacies and such. But you’ll work some truly awesome people, connections that will last many years. That’s called ‘networking’ — making sure you stay in touch with those people. It will help open up future career opportunities.

Yes, I said “wife”. We’ll get to that in a bit.

Spend your 20s doing silly things, adventurous things, but focus on your career. If I had to do it all over again, I would branch out sooner, explore more opportunities earlier, and look at the world of tech beyond this little suburb. There is so much more out there. Not that there’s anything wrong with suburbia — our town is great. I love it. But it’s not a tech-job haven. So don’t be afraid of that big change, that big opportunity.

Because once you go for it, once you finally take that chance, you’ll be oh-so-much happier! Ride that wave of confidence and of learning new things. Start attending SQL Saturdays earlier, and look into other data-centric meetups and events. There is so much to learn, and never enough time.

Oh, and PS: be super careful driving. Please.

Love

Yes, you will find love. You will find the love of your life. The One. Your soulmate. When you least expect it. You will spend your latter 20s and early 30s having the best years of your life with her. She will be your everything. She is laughter, passion, heartache, support, grace, care, light, love, and life itself. Cherish every moment.

For our time on this earth is but a whisper on the winds of eternity.

A question that’s often asked of grievers is, if they knew what would happen, would they go back, change anything, do anything different. In this thought experiment, even — would I tell my past self what would happen to my wife? No, I don’t think I would. For even now, as I look back on the million little moments that we shared and loved and laughed and cried. I would do it all again in a heartbeat. Our love was once-in-a-lifetime. I know that in my soul. And I carry that flame in my heart. May it never be extinguished.

i love you 3000 with picture of wife inside
Always. ❤

Follow-up: Cribbage “15’s Counter”

The actual method involves joining 5 copies of the table together, by each right-side table only including cards with higher ID values than the table to its left.

To be honest, my T-SQL Tuesday puzzle was a bit of a last-minute idea, which is why I didn’t have a solution ready-made. But, dear reader, you’re in luck! I have one now.

The code is over here in Gist. You can read thru it, but since the final query — the actual “answer” — is kinda ugly, let me explain my thought process.

Modeling is Important

Even when I’m putting together a silly little demo script like this, I feel that good habits and fundamentals are important. You never know what future developer might read it, copy-paste it, and say to themselves “Cool, I’m gonna follow this example when I do this other thing over here!” So you’ll see my formatting preferences, naming convention (though I must admit, I argued with myself over whether to pluralize the table names or not!), and correctly allocated Primary Keys. And since we’re modeling a card deck, even though I didn’t need to store the ‘NumValue’ (which is what you’d use for a straight/run, where the Jack is 11, Queen is 12, etc.), I did anyway.

Now, when we set up our “Hands”, we’re going to use two ‘PlayerNum’s, just so we can test two different hands at the same time. Cribbage can be played with 3 or 4 players, but we’re keeping this simple. Also, I could have built the hands more aesthetically, i.e. by selecting from Cards using PtValue and Suit, but again, I was trying to script quickly, so I just used the IDs that I knew from the previous query (the “full deck”). And again, there’s a “little extra” tidbit, the ‘IsCut’ indicator — we won’t be using that right now. If you’re still not sure what that means, go read the rules.

The Method

At the end of the original post, I mentioned loops and cursors as possible routes to a solution. That may still be true, but I decided to challenge myself to avoid them. Not because they’re “always bad”, as popular media would have you believe; they’re just often an indicator that a developer isn’t thinking in set-theory when they probably should be.

Let’s start with some basic principles. You have 5 cards in your hand. It takes a minimum of two cards to make 15 (examples include Jack+5, 6+9, etc.), and up to a maximum of.. you guessed it, five cards. So we need to check all combinations of any two, three, four, or five cards. We cannot re-use a card within the same combination; and putting the same three cards in a different order, for example, does NOT count as a separate combo (another ’15’).

So as you start to think about these rules, and if you’ve been around data for a while, especially data with identity values, you might have a little light-bulb. “Aha! I know how to do that. We can simply order the combos by the ID value, and that way we won’t allow duplicates!” And that’s kinda what I did, by enforcing the JOIN predicates that every subsequent derived-table have a ‘CardID’ greater than the prior one. But I’m getting ahead of myself.

The actual method here involves JOINing 5 copies of the table together, mainly just on PlayerNum, but also, as I said, by each right-side table only including cards with higher ID values than the left-side. In this way, we ensure that we’re not allowing the same cards to be “joined” to each other, i.e. we’re removing them from the right-side tables.

And finally, we have four OR‘d conditions: simply “do any of those combinations add up to 15, by the Card’s PtValue?” These are echo’d in the CASE-expression in the SELECT line, where we want to essentially “show the combo”, i.e. tell you what cards make up the ’15’. (Again, for style’s sake, we have an ELSE, but we don’t really need it because it’ll never actually happen.)

Now, it does look kinda ugly. It’s not very extensible — meaning, if you wanted to scale it up to find the ’15’s in a 6- or 7-card hand, or you wanted to look for other kinds of combos (like ’18’s or ’27’s), you’d end up re-writing a good portion of it, or at least copy-pasting a lot. Fortunately for us, Cribbage is fairly simple in this regard — your hand is always the same size, and you only ever care about ’15’s.

(Well, and pairs, 3- and 4-of-a-kinds, straights, flushes, knobs, etc., but again, read the rules if you’re curious. We kept this very simple by limiting ourselves to just one small fraction of the game mechanics.)

The cool thing about this sample, though, at least to me, is that you’re already set up to build on it if you want to try out other Cribbage mechanics. Or even other card games, if you just use the base Suits & Cards.

What Did We Learn?

What’s the point of a puzzle like this? Well, besides introducing you to a fantastic card game, if you didn’t already know about it. The point is to make your brain think in a different way than usual. Are any of us programming card games using a SQL back-end? Probably not. (Although an in-memory equivalent like SQLite or something might be viable!) But the next time you have a “combinations problem” with some real-world data, you might wonder if a method like this could come in handy. Or at least, if it could work out better than a double-nested-loop. =)

PS: I believe, instead of the LEFT JOIN​s, we could have used OUTER APPLYs. We’d move the conditions from the JOINs into the inner WHERE clause of each derived table, i.e. “this ID > previous ID” and “PlayerNums are equal”. If you’re curious, try it out!