T-SQL Tuesday #119: Change of Mind

Get up, come on get down with the SPACES!

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! ❤

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

What Marvel Movies Do I Need to Watch?

Welcome to the first post of the new year. I’ll be keeping things a little on the lighter side for now. I’m still very into my work and learning lots of share-worthy things in the data world. But for now, movies!

I also want to take a moment to appreciate those who reached out to us after our devastating loss. Thank you for your thoughts and prayers. Please continue to remember our family as we struggle to find a sense of normalcy.

So, some of my elder moviegoers asked me the question that many people have been asking over the last year or two: “What Marvel movies do I really need to watch before Infinity War?”, or more recently, “before End Game?”. More generally, which ones are worthwhile viewing to a casual non-geek, to someone who doesn’t need to obsess over every little minutiae, someone who is not by nature a “comic book movie lover”. It’s a completely fair question, and honestly it needs more.. less nerdy answers.

Hence, this post! Go read it on the new blog!

Movie Review Wednesday

So I’ve Been Thinking…

Data isn’t literally everything.  I mean it is, technically, but it’s not all super happy fun times, so we need to take a break once in a while and do something less neuron-intensive.  Thus, my new segment: movie reviews!  Because, despite what you may have read, all work and no play make Nate a dull boy.  And yes, I promised you this blog would be professional.  Mostly.  I remember specifically using that word.  So don’t wag your naggy finger at me.  If you don’t like it, you can simply avoid the tags like #offtopic and #movies.

Moved to new blog; clicky!

Movie Reviews and the Killer Database Collation

If you have a core database using a different collation than the rest of the DBs around it, BAD THINGS HAPPEN.

And we’re back!  Hi folks, thanks for being patient with my December hiatus.  The holiday season is always a little hectic but this year it felt especially sudden.  And hey, you all have better things to do than read a blog in between the home cooked meals and the family gatherings.. like sleep, shop, and go see all the new movies!

Thanks to both Pitch Perfect 3 and the latest New Year’s Rockin’ Eve, Britney’s “Toxic” is now stuck in my head, so that’s fun.

britney-stewardess-toxic
I think I’m ready now… for 2018.

Some of you may not know this, but I’m a big movie nerd.  Not like the weird “knows a bunch of obscure factoids about all the Tarantino movies” or whatever.  But I do quite enjoy the behind-the-scenes / making-of stuff — what used to be called “bonus features” on DVDs (remember those things??) — whenever the wife will tolerate sitting thru them with me.

Our genre of choice is generally horror.  Now, I’m gonna get nerdy on you for a bit; because there are several sub-types or horror, and I enjoy almost almost all of them.  Campy, creepy, fun, found-footage, gory, spooky, slasher, supernatural, tense, psychological, revenge, deconstruction, possession.  For the uninitiated, “deconstruction” is like 2012’s Cabin in the Woods — it pokes fun at the tropes while building on them in unique ways.  Those are one of my favorite kind; that one in particular is definitely in my top 10 all-time.

So to kick off this year, before diving back into the technical stuff, I’d like to give you a coupe lightning reviews of some horror movies that we’ve watched that are perhaps underrated or you may have missed.

  • The Babysitter (2017) – comedy/deconstruction. A young preteen boy, whose parents are gone a lot, has a great friendship with his older teen babysitter, but one night decides to spy on what she and her friends do after he goes to bed. And well, crap hits the fan.  Lots of fun, eye candy, and slapstick violence. 👍👍
  • Patchwork (2015) – campy/revenge. 3 girls are Frankenstein’d together and have to overcome their mental differences and physical struggles to piece together the perpetrator and hopefully exact some revenge. Superbly acted by the lead lady, plenty of violence and just enough funny bits to keep it going. 👍
  • Happy Death Day (2017) – slasher/deconstruction. Think Groundhog Day but with a college chick being killed by a masked marauder repeatedly.  She must try to find out who it is before it’s too late!  Somewhat predictable but still entertaining and engaging. 👍
  • Incarnate (2016) – possession/supernatural. A somewhat unique twist on the genre, a brain doc frees people from possession by mind-sharing & getting the person back in control of their own consciousness.  Think Inception meets Exorcist.  Very well-acted, convincingly scary demon, and nicely twisted ending. 👍👍
  • Demonic (2015) – creepy/found-footage. Bit of a misnomer, as it has nothing to do with demons; it’s about a ghost-summoning gone horribly wrong resulting in the deaths of all but 1 (ish?) member of the group that originally attempted said ritual.  Frank Grillo is always on-point.  Very engaging. 👍
  • Last Shift (2014) – gory/creepy/demon-y. Rookie cop gets stuck with the last watch in a soon-to-be-shut-down police station, chaos ensues.  Literally, this is some crazy crap; scary and bloody.  Original & vastly under-hyped, has an indie vibe but looks & feels professional-grade. 👍👍

Most of these should be stream-able.  So check ’em out!

Now on to the SQL stuff.

A not equal a
borrowed from the man himself, Pinal Dave =)

Collations are Hard

If you ever have to integrate a vendor database into your existing environment, and the vendor ‘mandates’ their DB use a certain collation (which differs from the rest of your SQL instances / databases), run away screamingSrsly.

Or convince your managers that you know better, and force it into the same collation as everything else you have to integrate with.  Good luck & godspeed.

Let me give you an example.  The ERP system is being upgraded, which of course means a new (upgraded) DB as well.  Part of this upgrade seems to involve supporting case-sensitive searching/matching against name fields.  To this end, the vendor insists that the DB should use a case-sensitive collation, namely ​Latin1_General_100_CS_AS.  Problem is, the rest of your DB environment, in which a lot of stuff touches the ERP database (via joins, linked-server queries, etc.), uses the SQL default collation of SQL_Latin1_General_CP1_CI_AS.

If you follow the vendor’s mandate recommendation, guess what’s going to happen to your queries/views/stored-procedures that touch this & other DBs?  Horrible things.  Terrible performance degradation.  Wailing a gnashing of teeth from the developers, business users, and customers.

Okay, I exaggerate.  Slightly.

But it really does hurt performance, and I don’t feel like it’s talked about enough in the data professional community.  In the next post, I’ll take this problem apart a little more and try to impart some of what I’ve learned from going through the pain of dealing with the aforementioned example.

Happy 2018!

PS: Apparently this is my 50th post!!  Go me!  :o)

50-cent-face-on-50-dollar-bill
fiddy. fiddy posts.