Blog

Movie Wednesday #3 (on a Thursday)

I’m a sucker for a good revenge flick. Especially if the protagonist is a strong female lead.

Advertisements

It’s that time again kids! Today (which is now yesterday), I saw Peppermint at the local cheap-seats theater. Aside from being surprisingly uncomfortable compared to the plush recliners of the deluxe place, it was a nice bit of nostalgia. There was almost nobody there. Frankly, I’m not sure how they’re still in business; their operating costs must be absolutely minuscule. But hey, works for me, $4 movies! Let’s get to it, shall we?

Revenge is Fun to Watch

sweet sweet revenge raccoon
Revengecoon is on point.

Call me a sucker for a good revenge flick. Even more so when the protagonist is a badass woman. I don’t get into the really graphic “rape-revenge” stuff like I Spit On Your Grave and its ilk, but in general, if you’ve got a leading lady kickin’ ass and taking names to avenge some injustice done to her or her loved ones, I’m game.

And the user reviews agree with me, but the critics decidedly do not. That’s typical. Critics look at lots of deep facets of film-making, but the average audience just wants to be entertained. Are there a few moments when it’s difficult to believe the character, or the lines feel just a tad forced? Sure. Are there some bits where perhaps we stretch reality just a bit? Of course. But it’s a damn fun movie to watch, as Garner takes out one gangster or corrupt-cog-in-the-justice-machine after another.

Jennifer Garner’s Appeal

Speaking of our leading lady… I’ve never seen Alias, so you’ll forgive the lack of comparison. I did see Elektra, which, although a pretty bad movie overall, at least showed that she could convincingly play a tough action hero, even if that particular character was over-sexualized. Obviously that was 13 years ago, but her age plays well as the seasoned, slightly weather-worn mother, who can still whip herself into better shape than the cartel thugs half her age, dispense her vigilante justice, and look good while doing so.

It’s purposeful, and effective, that she does not show skin or become a sex-object at any point. That’s not what this is about, nor should it be. She’s all business, and that business is bloody, brutal, and filled with sharp objects and shotgun shells.

The Bad Guys (and their deaths)

peppermint movie still showing judge trapped
See this? This is explosive rope. It can cut through really big trees and sh*t.

One of the best parts of a revenge flick is seeing the imaginative or poetic-justice-esque ways in which the hero deals death to those that deserve it. (Yes, I’m using those terms “hero” and “deserve” loosely and in the context of the film itself, not engaging in a philosophical debate outside the world of the story.)

While some of these are lackluster, especially for an R rating, there are few that really shine. The judge, a sort of Kevin Spacey lookalike, gets it good with nails-in-the-hands and a courtroom-sized explosion. The three shooters are strung-up by their ankles on a ferris wheel, the process and lead-up to which, I feel, would have been even more interesting to watch than the end result. And there are at least a few fantastic head-shots that you’ll just want to see for yourself.

As I said, given the rating, I do think the film makers could have gone darker with some of the kills, but overall, we get what we came for. It feels very similar to Taken, for obvious reasons (same director), and that’s a good thing.

Trailers Lie

Sadly, and seemingly more often these days, the trailer showed some sequences and dialog that either didn’t make it into the movie, or misrepresented it slightly. The titular ‘Peppermint’ moment isn’t there, at least not with the same impact; and a couple of the more badass-sounding vengeance lines are diluted by too much context or a lack of ‘oomph’ in the background score.

But again, that’s Hollywood. The trailer’s job is to make you desperately want to see the movie, and they often succeed. So we can’t blame them too harshly. The film is gritty, well-made, and compares favorably with others in its genre.

Speaking of trailers, they played one for Glass, a really intriguing upcoming crossover-continuation of Unbreakable and Split, from everybody’s favorite writer-director name to purposefully mispronounce. If you haven’t seen either of those, do yourself a favor. (McAvoy is phenomenal in the latter.)

Conclusion

peppermint angel graffiti wall
I brightened this up a bit from the original. I tried to find a better one but no luck so far.

If you enjoy watching a tough female lead, seeing bad-guys get their comeuppance, and following a story through to the end, this movie is for you. Even if you’re not generally a J Garner fan; she really does well with this role, and there are no hints of her typical rom-com personality sneaking in. It’s a good solid ride with a satisfying conclusion — exactly what you want from a popcorn vengeance flick.

Til next time, readers!

5 Things I Learned at SQLSaturday

Go find a SQL Saturday near you, at sqlsaturday.com!

The weekend before last, I attended my 4th #SQLSaturday event; my 2nd in San Diego CA (the others were Orange County CA, which is equally fantastic, and a little closer to work, though about the same distance from home). If you haven’t heard of or been to one, check out the home page and find one coming to a city near you! They’re fabulous FREE training events for the MS data platform, including but certainly not limited to SQL Server. For example, you’ll almost always find Kevin Feasel talking about aRrr or Kafka or Hadoop.

Did I mention free?

So I thought I’d share a few things that I learned this time!

The LinkedIn app’s Killer Feature

Did you know? The LinkedIn app has a “find nearby” feature that uses magic your phone’s various radios to instantly connect you with a fellow user who has the app open near you. It’s awesome! Now you don’t even have to look up from your convention coffee and security-blanket (phone) to network — just fire up the app, go to the People tab, hit “Find Nearby”, and commence trolling. =P

No, that’s horrible; be a normal human and talk to people. The tech is just there to help make the post-conversation connection.

linked-in find-nearby button

Storage Myths Busted

This was an interesting and even slightly entertaining session presented by Max @ SQLHA. One analogy that really stood out to me was this:

SANs have become a bit like the printer industry — You don’t pay a lot for the enclosure, the device itself, i.e. the SAN box & software; but you pay through the nose for ‘refills’, i.e. the drives that your SAN vendor gods deem worthy of their enclosure.

It’s frighteningly accurate. Ask your storage admin what it costs to add a single drive (or pair of drives, if you’re using something with built-in redundancy) to your SAN. Then compare that cost with the same exact drive off the retail market. It’s highway robbery. And we’re letting them get away with it because we can’t evolve fast enough to take advantage of storage virtualization tech (S2D, SOFS, RDMA) that effectively makes servers with locally attached SSDs a superior architecture. (As long as they’re not using a horribly outdated interface like SAS!)

Data Protection and Privacy is Hard

But completely necessary. We all need to become security practitioners to some extent. Even if it just means raising and documenting our concerns to our bosses. The great state of California has even jumped on the bandwagon with its very own privacy act. Still in the early stages, yet on the heels of GDPR, it can only mean that there will be more to come.

A few concrete action items from this, which should be “fairly simple” (with a big ol’ asterisk that says “depending on your organization and your change-management process”).

what if i told you you don't need 'sa'
For anything. Ever.
  1. At least encrypt your database backups. (And make a plan to implement some kind of “full” encryption across the data estate, be it TDE or AE or app-driven encryption via your developer teams.)
  2. Stop using sa! Reset the password, and disable it. Yes, your Agent Jobs will still run just fine.
  3. Disable Named Pipes & Shared Memory protocols; just use TCP/IP. Disable the SQL Browser service.
  4. Cut off your SQL servers from the public Internet (yes, you should still patch them; just download the patches to a fileshare and install them using PowerShell!). And stop letting people run SSMS on the server; that’s what client machines are for!

Columnstore All The Things!

Seriously. If you’re not using them yet, read about them, play with them, and start using them. They’re magic.

Okay, that’s a bit dramatic. As with any technology and feature, you need to know the WHY. Understand what the best use-cases are and how that translates to your own environment.

columnstore all the tables
Easy there sparky…

Here are just a few of the tips I gleaned from the session on this:

  • They were designed for data warehouses, but…
  • They’re also great for “operational analytics” — where you want to do aggregate reporting on your ‘live’ data, but that performance usually kinda sucks (and you don’t want to lock up those tables anyway).
  • Best with SQL 2016 or higher; 2012’s “v1” implementation was horrible, and 2014’s “v2” was semi-usable but still had some major drawbacks
  • Best candidate tables are “very large” (millions of rows or more), and best candidate columns have “low cardinality”, meaning they’re not full of unique values — they should be “compressible”. A simple example would be a Customer’s “State of residence” — you probably have millions of customers, but only 50-ish “State”s, and your typical report is asking “Hey, how many Customers per State ‘do X'” — that’s a great candidate for a columnstore index.

Users Don’t Like Date-Pickers

I mean they’re still better than text-entry boxes, but we can do better. Talking about SSRS here — reporting services, i.e. “the poor-man’s Tableau”.

Picture a typical business user, middle-manager type, going to your SSRS report that you just built for him/her. The first thing it asks them to do is “pick a start-date and an end-date” to define the “reporting period”. But you should know by now that they almost always want to see the current Fiscal Quarter. So you default them to those dates that define the current quarter. Great!

Now they want to quickly compare to the previous quarter, or the same quarter of the previous Fiscal Year. Ruh-roh. Nobody wants to go messing with those lame date-pickers with the pop-up calendar thing.

Give them a clickable label instead, which says “Compare to last Quarter” or “Previous Fiscal Year”.

The click action should be to “drill through” to the same report, while changing the (now internal/hidden) date parameters to the appropriate dates. Presto! The user only had to click once to get exactly what they wanted. Much nicer experience.

I’ll try to have a future post going into detail on this. I’ve got tons of ideas swimming around in my head after FishHeadTed‘s excellent SSRS classes, and not nearly enough time in the day to flesh them out.

i see what you did there?
Get it? Swimming, fish?!?

Stay tuned, and go find a SQLSaturday near you!

Career Day: SQL DBA

Build a solid CS/IT foundation, build your soft-skills, and then learn the latest & greatest tech stack. Or the mature enterprise stuff. There’s plenty of room for both!

Our benevolent overlord prompted us (months ago; I know, I’m always late to the party) to write about what we do in our day-to-day jobs as SQL Server professionals, aka #SQLCareer. The actual idea is to do 4 posts in a row, for 4 working days (either adjacently or the same day each week for 4 weeks).

Sticklers, us? Nope, never.

Without any kind of future guarantees, here is my first.

Morning Mash-up

Emails, tickets, and alerts, oh my! Today was actually pretty quiet on the monitoring front — no major performance issues, no interruptions, and no job failures. That one that occasionally takes 8+ hours did okay last night. More often than not, I have to kill it if I find it’s been running into the work-day. That should be automated. /backlogged

Re-wrote some analyst’s data change script to be more “friendly” and set-based, instead relying on identity values — which is important when they can differ between Dev & Production environments. Deployed a few change requests, including that one.

virtualize all the things
Even SQL? Yes! Especially SQL. Just know the caveats.

On the side of less tedium, I fleshed-out more of the plan to virtualize our remaining bare-metal SQL servers. The usual ‘gotchas’ have to be discussed with the Server Admins — reserving compute resources, preventing the ‘noisy neighbor’ problem, and having enough storage to do the migration.

Yes, that means about 2x the storage as they’re currently using. Got a problem with that? No? Good. 😉

Finally, I worked on some code for querying the SSRS ReportServer database to obtain report metadata (including stored-proc usage, parameters, and user activity). The core concepts came from Ted Stathakis‘s SQLSaturday session on SSRS 2016. Hopefully, some of that code will be coming to GitHub soon.

Afternoon Delight

“Delight” being sarcastic. No really, I love my job, but sometimes there are just some things that make you go ‘grr arg’.

First up, developing and finalizing some “data cleanup effort” reports. These are things that show the business users “what’s wrong with the data” and give them some guidance on how to “fix it”. Now, because there is no easy “management GUI” for this data, and because actual changes need to go through change-control, it’s not going to be a cake-walk. But given the right inputs and some domain knowledge, they can make the decisions as to what those changes should be, and then hand them off to the BA’s (business analysts). Usually in Excel form.

Excel spreadsheet with woman screaming in front of it
What? It’s under 1,048,576 rows! Barely…

Next we have the ‘grr’ part. This is where I spent about 40 minutes trying to explain to the 3rd-party software vendor exactly where and how to integrate with our core data systems. Most of the info should not be ‘new’ to them, but since I never know which support tech will be assigned to the issue, it’s best to err on the side of verbosity. To make things more confusing, the folks on my end, who have been interfacing with the vendor thus far, aren’t intimately familiar with the underlying technology bits, so the conversation gets a little dicey before we all arrive on the same page.

Fortunately, since I had their attention, I was able to sneak in a suggestion to upgrade the back-end database for the software (which we self-host, but is essentially managed by them & their software) to something from this decade. MySQL 5.0 is old, guys, mmkay?

Wrapping Up

A developer needs a new table & some initial data-load to production; it’s passed QA. Great! They wrote the query a little weird, but nothing ol’ SQLPrompt can’t fix.

Commit outstanding stored-proc code & RDL files (SSRS reports) to source control. Even if I’m the only one working on them, I’d rather they be safe & track-able.

Ruh-roh. A developer set off some proc in a Dev db, it’s been running for over 30 minutes… and then they cancelled it! But wait, it’s stuck in rollback. Yes, kids, rolling-back a transaction can take even longer than the transaction itself, due to rollbacks being single-threaded.

Now, since the user’s hitting of the ‘Cancel’ button (aka “Stop” in SSMS) does not wholly and entirely cause a kill SPID command, I do that for them (since only sysadmin or processadmin can kill sessions). Then I run ye olde kill 64 with statusonly (64 happened to be the SPID) to check on its rollback status. Good news, everyone! It’s at… 0%.

bad news nobody
You heard me.

And it stays at 0%. For fifteen minutes.

I move on to other tasks of course, since there’s nothing more I can do (short of pulling the plug on the server, which is never a good idea (unless you’re testing your Disaster Recovery Hope plan!). We also create a ticket to fix this stored-proc so that it doesn’t “take forever”, even if it has to churn through millions of records.

Finally, the statusonly check starts moving. It’s about 45% rolled back, with about “300 seconds remaining”. This, as anybody who’s been a DBA will tell you, is in “Microsoft time”. You know those progress-bars you typically get in Windows while something is happening? They’ll start off fast, make it to around 90-some percent, tell you there’s about 1 minute remaining, then take at least five minutes to “finish that last little bit”? Yeah, that’s a “Microsoft minute”. Patent-pending.

But fortunately for us, it does finish in just about the time it promised, maybe a bit longer. Just in time for the Dev to test what they wanted to test before going home for the day. And just in time for me to have my evening not ruined by a runaway transaction.

PS: Anybody who’s ever worked with SQL, including yours truly, has done this (set off a long transaction and/or tried to rollback a long transaction) at least once in their career. If you ask a seasoned DBA or DB-Dev, and they say “No, I’ve never done that!”, they’re a dirty rotten liar.  =D

Are You Not Entertained?

If the life of a DBA sounds like fun to you, get in touch with your local IT recruiter and find out what the job market is like! You’ll hear all sorts of doom & gloom about how “the Cloud is taking over everything” and “the DBA is dead; long live DevSecDataOpsUnicorns!”.

No, I joke. Yes, some of the fundamental responsibilities of the DBA are shifting to the cloud vendors, to IaaS/PaaS services, but at the end of the day, being in IT is about learning tech and working with people. Build a solid CS/IT foundation, build your soft-skills, and then learn the latest & greatest tech stack. Or the mature enterprise stuff. There’s plenty of room for both!

tee-shirt that says "tough enough to be a dba, crazy enough to love it"
We end on a happy note.

Quickie: SSRS Multi-Column List/Grid

While there’s no native control like it, with a few simple tricks and visual slight-of-hand, we can fake it.

Today I present to you, a hopefully less confusing and more intuitive explanation of how to make a “multi-column list” control on your SSRS report.  And if the term is confusing by itself, I’ll illustrate in a second.

Three links that helped me get going in the right direction:

They all roughly boil down to the same concept: placing redundant or repetitive controls (like a set of 2 or 3 tablixes), and using a RowNumber() expression with some modulo arithmetic to set “visibility” properties of the dataset rows shown in each.

We don’t quite get to “dynamic # of columns and rows based on the total # of records” nirvana (e.g. make it a 3×5 grid if you have somewhere around 15 records, but only make it 2×4 if you have 8 or less records, etc.), but I’d estimate that this approach would suffice in 90% of use-cases.  And whoever at Microsoft decided to not build a control like this into the toolset, curse you!!

Yes, we’re not even past the intro section and I’ve already cursed someone at MSFT.  Doesn’t change my passion for the platform in general; it’s just a minor curse.  =)

Concepts

SSRS uses a dataset to fill one or more controls with data; controls such as the tablix. In my regular implementations, a dataset is usually a stored-proc. The tablix shows the results of the proc in tabular form. The problem with the tablix control is that you can’t turn it into a multi-column layout, as you might see with some more advanced UI controls. You might think that the matrix control would be suitable for this, but no; that is just a table-like structure that can support hierarchical column-groups and row-groups.

For example, let’s say you have a table of Users, with columns ID, Name, and Role. Like so:

ID | Name    | Role   
---|---------|--------
1  | Bob     | Admin
2  | Alice   | User
3  | Jack    | User

But with many more records. We want to display this on a report, which is typically viewed on a PC screen, i.e. landscape orientation (wider than tall). So wouldn’t it be nice if we could display it like so?

ID | Name    | Role   || ID | Name    | Role   || ID | Name    | Role
---|---------|--------||----|---------|--------||----|---------|-------
1  | Bob     | Admin  || 2  | Alice   | User   || 3  | Jack    | User

etc. Right? Right.

Implementation

The actual work is fairly simple. We add 3 tablixes to the report and place them side-by-side. We connect them all the same dataset, i.e. stored-proc — which will only be executed once, conveniently! On the data row (as opposed to the header row) of each tablix, right click to Row Visibility. Then use an expression like so:

=IIf(RowNumber(Nothing) Mod 2 = X, False, True) — where X is going to vary from 1 to (# of columns) - 1, and finally to 0 for the last (right-most) tablix.

Remember, the expression defines the row’s Hidden property, not “Visible”. I know, it seems backwards, but that’s how it is.

In plain English, we mean, “if the row is the 1st row, show it, else hide it”. Likewise, for the second tablix‘s row, we’d say “if the row is the 2nd row show it”. And so on.

Not bad, right?

Final Thoughts

It’s certainly odd that there’s no native way of building such a report. But with a few simple tricks and visual slight-of-hand, we can fake it.

ssrs-multiple-tablix
I highlighted the middle tablix so you can see that there are 3 separate ones, but we want to make sure they’re precisely adjacent to each other so the end-user can’t tell them apart.

T-SQL Tuesday 106: 5 Things Not to Do With Triggers

Here are a handful of anti-patterns that I’ve seen with triggers in my time…

This month’s invite comes once again from our benevolent overlord* community pillar Steve Jones, head of SQLServerCentral.com! If you haven’t been there, stop reading immediately and go check out the helpful forums and ‘Stairways’ articles. Some truly excellent content to be had.

No, don’t stop reading immediately… save it to your favorites/reading-list and look at it in 5 minutes when you’re done here.  =)

*Though I’ve not had the pleasure of meeting him in person, I’ve heard Steve is a phenomenally humble and down-to-earth guy, so my silly comment about him is even sillier in that light. ❤

Triggers – Love ’em or Hate ’em

Borrowing a bit of a mini-game from the CodingBlocks guys, the first “Google-feud” (auto-complete result) for “sql server triggers are ” is sql server are triggers bad.  Well, they can be, if used improperly / to excess.  Like most bits of tech, they have their place and their use-cases.

I’ve blogged about a few such use-cases here (a “who did what” audit-trail type of trigger) and here (as part of the Nested Set Model implementation), which you should definitely read. I didn’t condone click-baity titles back then. Alas…

click this right now omg
All we need now is a “blink” tag…

Here are handful of anti-patterns that I’ve seen with triggers in my time.

Thing 1: Using them as Queues

Repeat after me:

A trigger is not a queue.

Triggers are executed within the same transaction as the query that fires them. Meaning, they’re subject to all that ACID-y goodness of a transactional system. This is a double-edged sword. If all is successful, it guarantees that trigger will do its job when the calling query runs, which is great for audit-ability. On the other hand, if the trigger has a problem, anything and everything that triggers it will also fail.

The fundamental distinction between this and a queue, is that the success of the queued action is not immediately critical to the continued operation of the thing that called (queued) it.

So if your requirement matches the latter behavior, and not the former, do us all a favor and use a real queue. Heck, find one of the few people who know Service Broker. (Hint: one’s a Warewolf, another’s a Poolboy.)

Thing 2: Making them WAY TOO BIG

Mostly because of the transactional thing, the rule of thumb with triggers is K.I.S.S. “Keep it Small and Simple.” Even the audit-trail example is a bit much if the table being audited is under significant write load. Typically, if the business requirements are both high-throughput and high audit-ability, you’ll be implementing a much more complicated tech-stack than just plain ol’ SQL Server with triggers.

Some of the offenders I’ve seen include: A trigger that wanted to write to several other tables with IF conditions that branched based on what column was being updated. And a trigger that required near-SA level permissions to do some back-end maintenance-y stuff. Those are both recipes for problem pie.

pie that looks like a kraken attacking a boat
We’re gonna need a bigger… fork?

Thing 3: Doing Nothing Useful

Somewhat opposite of above, there’s no point in introducing the management and performance overhead of triggers if they’re not performing a transaction-critical operation. For instance, something better left to a queue.

Thing 4: Housing Business Logic

There’s always been a holy war about whether “business logic” belongs in the database or in the application code. And, since geeks love sub-classifying things to the Nth degree, there’s a sub-holy-war about what “business logic” actually means. But I won’t go into that here.

If you fall on the 1st side of the fence, and you feel the desperate desire to embed some logic in the data layer, it belongs in stored procedures, not in triggers. Reasons include maintaintability, discoverability, performance, documentability. Not to mention source-control on procs is a helluva lot easier than on triggers.

Thing 5: Too Many of Them

there's too many of them
Stay on target..

While multiple triggers can be defined for the same action on the same table, that’s not an invitation. You enforce trigger execution order to an extent (first and last), but any more than that and you’re asking for confusion. Falling back on the KISS principle, if you need more than one trigger on a table & action (insert, update, or delete), you probably need to rethink the underlying design.

Bonus

Using INSTEAD OF vs. AFTER: it’s fairly self-explanatory, but just be aware of what you’re doing, especially with the former. You’re literally replacing the desired action of, say, an update query with the contents of your instead of update trigger. If this is not obvious to all users of this table, you’re in for some really surprised faces and angry messages.

And that’s all I have for today folks! Enjoy triggering stuff. In moderation, as all things. =)

SQL Style Guide – a Rebuttal

Both the original article, and my responses, are guidelines, meant to help you start a conversation with your own team about how you can get better at writing code.

In what will surely be a controversial post, I give my take on some of the major points of this “SQL style guide” that made the rounds on Hacker News / Reddit recently a few years ago.  Hey, I never claimed to be a source of breaking news.

stamp of controversy
Feels so empty, without me…

Now remember kids, these are opinions — everyone’s entitled to theirs, and it doesn’t mean you’re right or wrong.  As the author repeatedly points out, consistency among a team / project / environment is more important than anything else.  Both the original article, and my responses, are guidelines, written by people with some experience (guided by others with more experience) to help you start a conversation with your own team about how you can get better at writing code.  Because that’s what we’re paid to do, among other things.

Basics

I agree with most of the points here:

  • Consistent descriptive names (I would add “concise” too — autocomplete/intellisense has come a long way, but we’re still typing stuff sometimes)
  • White space & indentation
  • Datetime literals – hail Saint Bertrand
  • Comments – you, dear reader, know my thoughts already
  • Some OOP principles should be avoided because they usually lead to poor performance
  • Hungarian notation is best left in the ’80s where it belongs

Something I don’t abide by:

  • Sticking to the ‘standard’ and void vendor-specific functions/features

Some brief justification for my rejection:

Database code portability is largely a myth — it sounds great, in theory, but most teams/projects never actually do it.  If your data persistence layer is ever slated for migration to a new tech-stack, it’s going to require a massive overhaul anyway, in which those vendor-specific functions/code-bits will the least of your worries.  More likely, you’ll be swapping-out pieces of the data layer in small chunks to move into other storage tech, like a NoSQL store or a DocumentDB or something; and eventually over time, the whole original data layer will have been moved, and the concern over SQL code portability will be moot.

Furthermore, database vendors give you these features/functions for a reason — they’ve found that it greatly enhances their product and the productivity of developers who work with it.  So why not take advantage?

Finally, if your application is ‘cutting-edge’ enough that ALL db access is done via ORM or some kind of repository layer in the code-base… guess what?  You don’t have this problem in the first place!  Because the database is a dumb state storage mechanism, containing little to no actual code whatsoever (storec procs, functions, etc.).  So, port away!

now that's what i call edgy
I can almost feel the edgy-ness…

Other basic issues:

  • CamelCase (actually TitleCase) is pretty standard in a lot of DB schemas, and I see nothing wrong with it.  Despite my love of underscores (snake_case) , it does make for more awkward typing.
  • Plural or singular entity names should match the convention preferred by your overlaying ORM, if you’re at the point of DB design; most of the time, though, you’re working with a DB that you’ve inherited and you have no control over entity naming anyway, so stop whining about it and get on with life.
  • Leading vs. trailing commas: I prefer leading, but the arguments against it can sound convincing (not just his, but in general in the tech community) — my experience leans toward being more likely to futz with the middle-to-end of a list than the beginning (1st item), thus making the leading commas more likely to help, but that’s just me. Also, thanks to an awesome member of the Coding Blocks Slack, a point in my favor is that source code comparison (diff) tools will only show the one changed line instead of two, if you’ve had to add to the end of the column list.

Naming Conventions

Yes, please avoid reserved keywords, replace spaces with underscores (or use TitleCase to avoid having spaces), and use concise yet meaningful table aliases when you’re writing queries.  I still remember, when I first started working at my current company, literally gasping and cursing under my breath when I found that some databases actually had a space in the name.

Beyond that, the article goes a bit too deep in the weeds for me, especially the whole “known suffixes” thing — because isn’t that just Hungarian notation on the other end?  How about names that make it intuitive, such as IsActive for a bit flag, or LineNumber or RecordSequence for a sequential integer that’s not auto-generated (not an identity value), or @NumMonths as a parameter for a stored-proc that indicates how many months of reporting to fetch?  Common sense should rule the day, not arcane prefix/suffix conventions that will never be documented or enforced.

White Space

This whole notion of a “river” feels strange and awkward.  It’s made worse by the fact that some clause’s keywords are “too large” for the “standard” river width (which is the width of the SELECT keyword, ish), such as group by and left join).  Plus, I’ve yet to see truly excellent tooling support for this kind of style (be it VSCode, Visual Studio, SSMS, SQL Prompt, or other styling tools / auto-formatters).  Given that I still largely write my code without continuous automatic re-style-on-the-fly styling assistance, I find this hard to digest.

Side-bar: big kudos to the author for pointing me at this typography article, which challenged my long-ingrained writing preference of double-spacing between sentences.  Even now, I do it while I write this post, knowing it’s wrong — I can’t help myself!

For similar reasons, JOINs and sub-queries don’t need to be “on the other side of the river” — since I’m actually saying “there is no river”, what I mean is, don’t indent those JOINs or sub-queries excessively.  In the FROM clause, the JOINed tables are just as important as the first one, so I don’t see the need to reduce their importance by putting them so far to the right.  And please for the love of all things holy, stop putting the JOIN predicates in-line (on the same line) after the joined table — put the ON conditions to their own line and indent it!

Sub-queries are a strange animal, and I won’t dive deep on it here.  Basically, I try to style them the same as I would normally, just indented to the context of their scope; and within Javascript-style parentheses — meaning, open-paren on the preceding line (or its own line), then the body, then close-paren on its own line to finish.

Special note about the “Preferred formalities” section

BETWEEN is mostly evil.  I’m not saying you should never use it; just be very clear about why you’re using it, and only use it with discrete valued types (DATE, INT), NOT with continuous (or conceptually/nearly-continuous) value types (DATETIME, REAL/NUMERIC/DECIMAL).

The UNION operator is often misused, usually because UNION ALL is preferred (and is what you really meant anyway), but a blanket statement to “avoid it” misses the point of why it exists in the first place.  Likewise, temporary tables (#temptables) are wonderful tools when used properly and in moderation, but flagrant overuse can lead to what I call #tempocalypse (which means you’re hammering your TempDB so hard that its underlying storage system screams for mercy).

cry for help cheeto puffs
Like Cheeto Puffs, your TempDB has become bloated and full of cheeze.

Misnamed section “Create syntax”

What he really does here is expound upon table and database design principles.  This does not belong in a “Style Guide”; it probably belongs in a “Design Guide”, but because the relational database as a technology is so mature (yes, that means ‘old’) by this point, most of this feels completely unnecessary and redundant.  And again, you’re working with inherited designs over 90% of the time, where you don’t get to make these decisions, because they were made for you by your predecessors.  If you are so lucky as to be designing a relational model from scratch, look for advice from the tried-and-true architects of the trade.

I do echo and reiterate his advice to look at other data storage tech for things that are not ideally served by an RDBMS, such as EAV models, document storage, key-value storage, text search, etc.  There is no shortage of NoSQL tech to meet those needs, and they will do a much better job of it than you or I could in designing a hacked-up SQL model that somewhat does the job but falls over flat when faced with scaling up to a larger size or heavier workload.

In Conclusion

As I said at the beginning, these are opinions.  I applaud the author’s effort, and the fact that he actually got something together, made it open-source on GitHub, asked for feedback, and actually got traction with several communities discussing it.  That’s more than I can say for myself at this point!

I hope that this spurs some discussion on your database development team, and perhaps helps you work toward making a consistent style guide for your own environment.  Even if it’s just water-cooler talk, it’s always good to remember why we write code: so that others can read it.  (Sure, it’s also to make stuff happen, i.e. “make the computer do things”, but that’s a side-effect — your main goal should always be readability and clarity.)

Do you have some comments, thoughts, disagree? Leave me a comment! I’d love to hear from you.  🙂

needs-moar-drama
Drama-Cat is bored…

Movie Wednesday #2 – The Meg

The only thing deep here is the water. But it’s good-old-fashioned shark-movie fun.

It won’t always be new movies, I promise.  I could go on for pages about such classics as The Gladiator, Independence Day, Jurassic Park, Scream, Pulp Fiction, Face/Off, Lord of the Rings, and more.  And I will, eventually.  I’m going to try out a hosted WP service with my own domain soon, and then I’ll have more freedom to draw lines in the sand between the tech stuff and the fun stuff.  But for now, you’ll have to settle for reviews of new or recent movies that we go enjoy semi-regularly at our local recliner-lined pizza-serving cineplex.

Without further ado…

Things Are What They Are

Let’s make something clear right off the bat.  This is a popcorn flick.  For those who aren’t up on the movie-goer lingo, that means it’s more or less mindless fun.  It’s CGI-infused, big-screen big-sound action, with a few recognizable faces, a few up-and-comers, and a whole gaggle of extras.  Throw in a Wilhelm Scream for good measure (fair warning: TVTropes link!).  And yes, a black guy dies first.

Statham is no stranger to this sort of film.  One might even say he’s built his entire career on them.  But most of those are more the dark, gritty, pure action flicks.  Yet he’s proven he can handle himself with the lighter, more humorous side as well.

The monster, the Megalodon, is sufficiently large and in-charge.  It menaces and mangles effectively, and even outsmarts a hapless human or two.  The premise around where and how they find this beast is mildly interesting, despite the occasional disregard for high pressure physics.

bro-do-you-even-science
Because Bill Nye will have your arse…

Who Dat?!

One of our favorite things to do with movies is to call out where we know the actors from.  Here we have Dwight from The Office, and Travis from Fear TWD — we’re supposed to believe he’s Statham’s brother, which is a pretty hard sell, unless one is adopted.  Oh, and that short-haired techie chick with the annoyingly hipster name? That’s the lead singer of ‘Evermoist’ from Pitch Perfect 3!

pitch-perfect-3-evermoist-wave
Pitch Perfect 3 – the ladies’ main rivals

Where It Hits and Misses

We get some cinematic moments of shock and awe, a few tearful goodbyes of self-sacrifice, and a handful of suspenseful close-calls.  And obviously, some big meaty carnage.

Unfortunately, the dialog falls a little flat sometimes, and the humor isn’t always snappy. Now, you’re not watching this for deep character development.  The only thing deep here is the water.  But there could have been a bit more emphasis on some key elements that would make the characters more memorable or relate-able.

Comparisons Are Fair Game

It’s quite natural to compare this to Jaws, and the many myriad of mimics that it spawned.  Obviously, this film has no such aspirations, nor does it feel the need to shove this fact down the audience’s throat. Unlike, say, Sharknado, which tries way too hard to be “so bad it’s good” that it ends up looping back around to terrible again.

Furthermore, the actors understand this, which means that, while they do take their roles somewhat seriously, they allow themselves to have fun with it.  Nobody’s trying to impress the Academy here.  And frankly, nobody’s expecting you to watch more than once. Your enjoy it for what it is, and then you walk away.

someone once said in a meeting let's make a film with a tornado full of sharks
No, let’s make SEVERAL!  Best idea EVARRR!!!1

The Verdict: one solid thumb up

Lets be honest.  You’re going to watch this because you enjoy big sharks, Jason Statham, and/or ocean-themed action-adventures / creature-features.  And you won’t be disappointed.  It’s just good-old-fashioned shark-movie fun.  Enjoy your popcorn, then go about your business.

Have fun!