In light of https://natej.dev/2021/08/13/new-beginnings/ , which you should also read if you care about personal celebrations of life and love. I also have a bit of good news to celebrate at work. I’m finally not the LONE DBA!!
Look ma, I haz underlings!
Also, if you’re at all into the collectibles market, our company is hiring. And our tech org is growing FAST. Like, blazingly fast. Come check us out and apply. We’re super nice folks. (Except this Nate the DBA character, he gets ornery.) =P
I have almost zero down time anymore so don’t expect an impressive amount of content out of me, but I am getting back into writing and teaching, so I hope to do at least a few TSQL-Tuesdays over the rest of this year.
Short post. The lesson is: parenthesis placement MATTERS! A lot.
The question is simply this. Given a day (like today, which at the time I drated this was 12/18/2020; yes, I understand it’s now 12/20, you’ll see why in a bit)… Anyway, given a day, which starts at midnight and ends at midnight the next day, in local server time (which, in my case, is PST — yeah, don’t judge me, I can’t change it, and I’ve already screamed into the void about it), tell me what starting and ending datetimes that corresponds to in another time zone — in this case, China.
DECLARE @AsOfDate datetime = '12/18/2020';
DECLARE @LessThanDate datetime = DATEADD(DAY, 1, @AsOfDate); --Ends the following midnight
DECLARE @LocalStart datetime, @LocalEnd datetime; --For storing the converted values (in the other time zone)
DECLARE @TzName varchar(50); --You could even look this up from a table!
PRINT 'local TZ name:'
SELECT @TzName = 'China Standard Time'
SET @LocalStart = CONVERT(datetime, CONVERT(datetimeoffset, @AsOfDate ) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
SET @LocalEnd = CONVERT(datetime, CONVERT(datetimeoffset, @LessThanDate) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
PRINT ('to PST, to local, INCORRECT due to misplaced parens:')
SET @LocalStart = CONVERT(datetime, CONVERT(datetimeoffset, @AsOfDate AT TIME ZONE 'Pacific Standard Time') AT TIME ZONE @TzName);
SET @LocalEnd = CONVERT(datetime, CONVERT(datetimeoffset, @LessThanDate AT TIME ZONE 'Pacific Standard Time') AT TIME ZONE @TzName);
PRINT ('to PST, to local, CORRECT!')
Hopefully this is a nice quick reminder that you can (somewhat) easily use the ‘AT TIME ZONE’ operator in TSQL to help you navigate the treacherous waters of dealing with time zones.
I literally spent hours beating my fists against the keyboard using variations on the INCORRECT methodology (with the parens in the wrong place) — do I have to reverse it and put @TzName first, then PST? Am I starting in the wrong place? WHY IS THIS SO HARD!?!? Then, like you should always do in such situations (and preferably much sooner than hours into it), I walked away. Came back to it on a Sunday afternoon, and BAM.
In fact, savvy SQL-ers will note that I don’t even need the internal CONVERT — and by omitting that, I can eliminate the whole source of my confusion in the first place (those darn parens!)–
DECLARE @AsOfDate datetime = '12/18/2020', @LocationID int = 4;
DECLARE @LessThanDate datetime = DATEADD(DAY, 1, @AsOfDate);
DECLARE @LocalStart datetime, @LocalEnd datetime;
DECLARE @TzName varchar(50);
PRINT 'local TZ name:'
SELECT @TzName = TimezoneName
WHERE LocationID = @LocationID;
SET @LocalStart = CONVERT(datetime, @AsOfDate AT TIME ZONE @TzName AT TIME ZONE 'Pacific Standard Time');
SET @LocalEnd = CONVERT(datetime, @LessThanDate AT TIME ZONE @TzName AT TIME ZONE 'Pacific Standard Time');
PRINT ('from local to PST:')
SET @LocalStart = CONVERT(datetime, @AsOfDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
SET @LocalEnd = CONVERT(datetime, @LessThanDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @TzName);
PRINT ('from PST to local:')
So, second lesson: When you’re stuck on a problem, take a breath and walk away. Come back to it later. It’ll still be there.
I talk way too fast, I want audience participation, and I always make stupid typos.
Well folks, it’s been a minute. How’s your pandemic going? Hopefully safe and healthy.
This month’s blog party is brought to you by Lisa GB, and the topic is “What (else) have you learned by Presenting?” — either giving a talk at a big conference, a SQL Saturday or meetup, or (like me, because I’m still working my way up to it) just to your own little group of coworkers.
I don’t have that much presentation experience under my belt yet. After COVID started, I briefly tried my hand at Twitch streaming, only to walk away dejected and disillusioned because nobody watched. (Probably blame the content as much as the presenter, but whatever.) I did give a few “big room” talks at work last year, which went pretty well, and I got some great feedback, including helpful constructive criticism — talk slower, keep things on-topic and relevant to the work we’re doing, etc. All good stuff.
Lately, I’ve been doing brief “TSQL Tuesday” video-meetings with very small groups of colleagues (usually only 2-5 people show up any given week). We talk about whatever database-related points of interest have come up during our day-to-day grind. Sometimes it’s completely spur-of-the-moment. At least a few times, one of my stellar new BA’s (also QA/Tester), brought something to the table that even *I* wasn’t familiar with, like the cool IIF function in TSQL, or the EXCEPT UNION EXCEPT method for comparing homogeneous data-sets and the related concept of “bucket chemistry” with large buckets of data in preliminary analysis. (Sounds like a blog post that someone should write.)
Here are the things I’ve learned. Well, besides those mentioned above.
A. I really do talk way too fast when I’m presenting. Still. Trying to work on it. B. I enjoy audience interaction much more than monologue-ing. C. Never type in demos.
And finally, I’ll leave this as a teaser for a future post. While working on some demo material for “#TempTables – Use or Abuse?”, I got super confused about temp-table scope in context of stored-procs that abort or fail. So for a moment I understood why developers insisted on putting DROP TABLE #MyTempTable at the end of every one of their procs — it’s basically a safeguard, with no legitimate downside. Anyway, as I read into things and did a bit of research, I found this gem about how temp-tables are cached, and realized that I’d been committing a particular sin (select into #temp, followed by add columns for fetching more data) for a long time without realizing its risks.
Stay safe and healthy friends!
PS: there’s been a bunch of twitter on Twitter about PASS, people resigning left and right, and I’ve no idea what’s going on, but I’ll get the popcorn, cuz it’s sure to get entertaining. =D
Tuwednesday. Blursday. It’s all the same day now, isn’t it? Really this post is just about showing off some incredibly spot-on analogies by fellow #sqlfamily bloggers.
And of course you start with the creme-de-la-creme, Mr. Ozar. We’re having our dessert first, you see. Life’s short, live a little, etc. The one-liner: the database is a walk-in fridge in the back of a restaurant; the DBA is the refrigerator technician; the developer is a chef that works in the restaurant to which that walk-in fridge belongs.
Speaking of food, Deb Melkin describes how execution-plans (query plans) are like a routine for going out and running your weekly errands. And how small changes can sometimes lead to incredibly large differences in the execution of said plans.
Jeff Ianucci has another restaurant-related metaphor. Boy, us DBAs sure are a hungry bunch, eh? And thirsty! Shane O’Neill regales an imaginary youngster in his native dialect about how databases are like whiskey cabinets. It’s true, sometimes the job does drive you to drink. Especially when a developer leaves the door to the walk-in-fridge open.
So what do I tell people when they ask me “What.. what would you say, you DO here?” Well. Let’s say the company is like an animal, right? Any kind of mammal, let’s say. Leopard, Zebra, Elephant, Platypus. Whatever you fancy. The organism’s body functions primarily because of the blood in its circulatory system. Blood delivers oxygen, transmits nutrients, and moves throughout the entire body. So, too, does DATA. It delivers valuable information, transmits decision-making-fuel, and moves throughout the organization. Without data, the company cannot function. It cannot know how many customers are coming in or leaving, how successful a marketing campaign has been, whether a product is selling, or when the next anticipated failure of a WidgetMaker9000 machine might be so they can bring in a service-tech to inspect it.
All of these “measures” are things that the company heads care about. And none of them are knowable without the data. Sure, you can wave your hand around and produce some gut-feel “SWAGs” (sophisticated wild-ass guesses), and if you’re lucky, you’ll only be wrong half the time… maybe 1/3rd if you’ve got some experience behind you. But data has the power to actually tell you those answers. All you have to do is listen.
It’s here that the analogy breaks down a bit, because we assume that, while you as an organism have very little power over where and how your bloodstream is organized & flows, you as a data professional (and as a technology-invested company, on the whole) have PLENTY of that power over your data. You need to invest in the right infrastructure, the right people, the right technology, and the right models, for your business. And much like the searing pain of heartbreak, if you discover that so much of that data-circulatory-system is wrong that it’s killing your organism (organization), “rip it out, and start again.”
Oi. Now I sound like I’m comparing DBAs to gods. Don’t worry, I’ve had enough humble-pie lately to dash that illusion to smithereens, thank you. Now go enjoy some good food and whiskey. ❤
This feature us utterly useless to me. No, worse, it’s actually a hindrance to productivity.
I feel like I’ve been ranting and bemoaning “cloud-y” stuff a lot lately. And I don’t like that, because I understand it’s “the future” and “the best way to deliver software” and blah blah blah. But hear me out.
Some things just really need to stop. Or at least be rolled out in gentle, opt-in style phases where users can choose “Yes I’d like that feature” or “Nope, hard-pass”.
And as a million developers scream at me from the glare of their screens…
But seriously. Let me give you examples. And guess what? Because I primarily use Microsoft technologies at work, that’s what I’m going to put on blast. But they’re not alone, and they’re probably not the biggest offender. (Although their marketing department STILL baffles me.)
Planner is a sort of Trello/Project/virtual-whiteboard hybrid that comes bundled with OfficeMicrosoft Office 365 (screw your new naming convention). It’s half decent. I mostly use it to prove to my bosses that “Yeah, I have a crap-ton of work in the backlog, so stop asking me if Thing X is done yet.”
The hierarchy of Planner goes like so: Plan, Board, Section, Task. Task is the “unit of work” that most Agile teams are familiar with. Many Tasks make up a Section (or lane, or column, or whatever); many Sections make up a Board, and a Board is part of a Plan (aka a Project).
Recently they pushed out an update that made it horrendously ugly for me. Why? Because they assumed that any time you put a LINK on a Task (a URL), it must have some cutesy picture or preview associated with it, and thus you should see that picture as a disproportionately large head-space on that task. But guess what all my links are? Ticketing system tickets. They don’t have a picture. Or best-case scenario, they’re all the exact same picture (of said ticketing system’s logo/favicon).
Thus, this feature us utterly useless to me. No, worse, it’s actually a hindrance to productivity. Because now my tasks take up more than twice the space, with zero added value.
Teams is MS’s answer to Slack. And in a great many users’ opinion, a poor substitute. I’m not an extremist in that camp; I can see its uses and the things it brings to the table, and I can generally use it on a day-to-day basis without getting frustrated. The video-conferencing capability is quite good, no complaints there.
So how have they done me dirty lately? Well, there’s this whole “double-click a user/avater to open a new pop-out window to chat with that user”. WHY? Why is this necessary? I already have the app open. Can you not just TAKE ME to the Chat tab to the conversation with that user?
Another thing. This isn’t a “new update” thing; this is a long-standing “Oh my lord I can’t believe they DESIGNED it this way, WHAT were they THINKING?!?!” thing. Files. Sending & receiving files. I get an picture, like a screenshot, from a user (that’s NOT a OneDrive link, because that’s a whole ‘nother can-of-worms). I click to download it. It goes.. where?
OH RIGHT. It goes to my ‘Downloads’ folder. That dumpster-fire, where everything from anywhere goes into, and nobody keeps it organized, and nobody knows how to find anything unless they’ve gotten smart enough to sort by Date Modified descending. Right, that.
But wait, the file name is even better. “MicrosoftTeams-image.png”. Oops, did you get another one? “MicrosoftTeams-image (1).png”. Another? “MicrosoftTeams-image (2).png” TOTALLY OBVIOUS what those are! Who could possibly get confused by that?!?
One last gripe. The freakin’ notifications. Do I really need to be notified every damn time someone gives one of my chats a “thumbs-up”? The notifications don’t even go away immediately! You have to literally go to the Notifications tab and CLICK ON IT, even if you were previously on that exact conversation in which the thumbs-up happened!
This last one is a bit controversial. See, in theory, every Chrome browser update is supposed to make the web more secure, by enforcing standards compliance and security practices and all that jazz. Great! Guess what? We live in the real world. People have favorites/bookmarks and desktop-shortcuts and the like. Companies run on-prem email servers hybrid-ized with cloud email providers (yes, lookin’ at you again, Office 365) and their end-users expect things to JUST WORK. Continously.
If they can’t get their email due to some strange new “certificate” error that shows up when the click their favorite Favorite, guess who they call? Nope, not Google (because what even is a phone? or a human?) — their own company helpdesk. And the hapless helpdesk tech says, “Gee, it works for me, what’s going on?” Then they spend an hour talking with their team trying to compensate for every different combination of user-level/environment/browser/network-configuration, only to end up creating a redirect in their cloud-based DNS overlay so that the user doesn’t have to think about certificates and mysterious “Oops! :(” errors anymore.
#SMH a third time
What’s the point?
I don’t know… not like I can do anything about the way these software giants conduct their development. Just… if you’re a developer and you read this, take a moment to consider your end users more. Please? Not everybody always wants all your new features. Yeah I get it, option-izing them makes things harder for you. Sorry. If you wanted an easy career you’re in the wrong field. Just consider it, at least.
Now, if you’ll excuse me, I’m going to go shake my fist at the empty air and scream into the void.
AKA “Crap that I uninstall immediately after a fresh installation of Windows 10.” It’s frankly mind-boggling how ridiculous it is that Microsoft continues to include junk like this with what is supposed to be “their best OS ever”. Granted, past versions of Windows came with their own fair share of bloatware/junk, but some of that stuff was REALLY amazing and useful, like the DVD Creator in Windows 7.
Here is my list of apps that I remove immediately with a fresh install.
Feedback Hub – because Microsoft does not actually care what you think (or I wouldn’t be writing this, would I?)
Mixed Reality Portal
Print 3D (notice a pattern yet?)
Skype – dear god please kill this already. Unless you still use it… then you’re the reason MS keeps this arcane thing around, and you should #stopit.
Tips – this may be a personal preference, but I find the built-in tips annoying and useless, so unless you’re brand-new to Windows 10, I say remove it.
Weather – because that’s what your phone is for, silly. Do you really check the weather on your PC/laptop?
Honorable mention: the ‘Game Bar’ (possibly sometimes called ‘Xbox Game Bar’), is not on this list (i.e. I leave it installed), because what it actually is, is a very useful screen-capture tool for recording a video (and/or stills) of your desktop and what you’re doing on it. So I could have, for instance, recorded a real live video of me going into the Settings page and uninstalling all these apps, and you could watch said video after I uploaded it to YouTube, instead reading all this boring text! What a world, eh?
Hit the key-combo ‘Windows+G’ to see it in action on your own PC. Hit Escape to dismiss it after panicking “omg wth did I just do to my computer?!?”.
The Tolerable (but Unnecessary)
And here are a few optional ones — I typically remove these from a “business-purpose” or “low-end” machine, but for an average end-user, they’re OK(ish).
Groove Music – it’s not a great music player, but it’ll do if you don’t know any better.
Microsoft Solitaire Collection – well, if there’s one thing everybody has ever done since the dawn of the PC, it’s play solitaire. So sure, I get it, it’s hard to let go. Fine, keep it if you must. I just don’t see the point anymore.
Movies & TV – again, if you have nothing, this is a half-decent media player (think of it like old Windows Media Player’s love-child), but if you’re seriously into video watching on the computer, you’ll probably replace it with something better.
Office – unless you’re actually planning on buying into their ecosystem with Office 365. Which I don’t disagree with – it’s a fine product, if you’re ok with paying the monthly/annual fees.
Spotify – I’m not sure if this is always here, or if it was just on the machine I was working with at the time. But again, great, if you’re a Spotify user. Otherwise, why?
In case you’re not sure how to uninstall apps — what used to be called programs, in the ‘Add & Remove Programs’ page of the Control Panel in Windows-past — simply hit Start, Settings, and go to Apps. In the ‘Apps & Features’ screen, scroll down the list of installed apps, select one, and click the ‘Uninstall’ button. On some of them, this button will be grayed-out (disabled), which means good ol’ Microsoft doesn’t let you remove it. Which is ludicrous for something like the ‘People’ app, that does god-knows-what and frankly should never have existed.
And now, if you’ll excuse me, I’m going to go shake my fist at the empty air and scream into the void. =P
Disclaimer: this was written in May 2020, as of Windows 10 version 1909. The native/built-in app list could change at any time if MS releases a major feature update. Which will cause more screaming. Welcome to OSaaS (Operating Systems as-a-Service).
Instead of relying on my home internet bandwidth, I simply RDP into my workstation that’s still physically sitting in the office.
This is like a month late, but I didn’t have anything for the latest TSQL Tuesday on Unit Testing. Kudos to those of you who are doing it… it’s REALLY HARD. Harder than it should be anyway.
So as we all started working from home last month, it dawned on me that, apparently, the way I work from home is drastically different than most everybody around me. And it got me really curious, whether this is sort of generally applicable IT workers, or whether I’m really the odd-duck!
Probably like many of you, my primary workstation at the office is a laptop with a docking station hooked up to dual monitors and a full size keyboard and mouse. It makes going to meetings easier, and in theory I’m able to take it to the occasional conference. (I say “in theory” because, as I’ll explain in a moment, it rarely happens.)
Now, apparently the ‘norm’, at least for non-IT staff, is to take their laptop home, maybe with a dock & an extra monitor or two, fire it up, connect to the VPN, and off you go. Some people need printers and/or scanners, which makes it more complicated. But for now let’s focus on the typical knowledge worker that just needs a PC. And of course, if their workstation at the office is not a laptop, IT is tasked to provide them with one. Which, during this Coronavirus outbreak, has caused its own subset of issues with supply & demand at the big-box tech retailers.
Contrast that to me, and hopefully maybe some of you. I have the good fortune of owning a plethora of computing devices, most of which are at least suitable for running VPN & RDP (Remote Desktop). All of the, say, resources which I need access to and that I manage and manipulate on a regular basis, are in a data center, a big building with tons of racks of servers and gear and super-high-speed connections. And the office, of course, has a much higher-speed/bandwidth connection TO that data center than anybody does from their home. Some of you may see where I’m going with this.
So instead of relying on my home internet bandwidth to keep up with all the bandwidth-intensive tasks that constitute my workday — including, as my fellow DBAs will attest, dozens upon dozens of queries against the SQL Servers in SSMS — I simply RDP into my workstation at the office. Yes, my work laptop, which is still sitting in its dock on my desk in an empty (mostly) building. It’s the closest thing to actually being there.
Here’s what that allows me to do. All my network resources are just as quick and responsive as they are at the office. My code repositories and my Visual Studio windows are instantly available. My queries return results in milliseconds because they’re only traveling from the data center to the office. The only thing traveling the longer and slower distance to my house is the image of the screen from the work laptop. Make sense?
This is the same principle that drives some of these new “cloud gaming” services like Google Stadia or NVidia GeForce Now. The only thing you as the player really need is the continuous moving picture of the game, not all the hard work behind-the-scenes that it takes the GPU to render it. (Of course, the thing they haven’t really solved for is the input latency and the turnaround time of the player’s action being replicated on the screen and leading to the next action in the chain.)
Okay, I get it, it’s not for everyone
I understand some workers need more than that to do their jobs. And some folks don’t have the handful of spare PCs that I do. So I get it. Not everybody can or should work this way. But I was quite shocked that, out of all the folks I work with, I was the only who thought this was THE way to work remotely. Or at least the best way.
Surely, most people with a.. what we might call “gray collar” jobs — we work at a desk all day but we aren’t Wall Street and we don’t wear suits, so.. your typical middle class occupations, yeah? — anyway. We all have a workstation at the office. And surely we have at least one computer at home, even if it’s another laptop. Right? But no, apparently, the “tablet revolution” was a thing, and people don’t have big ol’ desktops or even traditional laptops at home much anymore.
Not only that, even if you could afford to invest in one FOR this season of mass-remote-work, you’d have had a bitch of time getting ahold of one, because everybody and their mother decided now was the time to buy. And not without good reason. Most of that purchasing was actually done BY the small-medium enterprise business market, because their IT departments had to buy a bunch of new laptops for their non-IT colleagues to take home for work!
I guess there’s not much of a big important point or lesson here.
Is there ever?
If anything, I would encourage people to TRY working from home this way, if they have the resources to do so. It significantly lessens the burden on your IT folks, as long as your home PC that you’re using to VPN is secure. That’s really the biggest variable in this equation, and in some cases, it’s why they actually prefer to issue you a company-imaged/company-secured device instead. But, thankfully, with a few network and firewall tweaks, they can lock down the VPN traffic so that it doesn’t allow your home device to talk to anything BUT your own workstation — or at least, any computer in the “workstations” group. So yes, there are some trade-offs. But again, especially if your employer has had trouble keeping up with the surge in demand for “loaner laptops”, maybe suggest you try this way and see if it can work for you.
Yes, this game will forever live on in my memory as the pinnacle of 3D gaming from my childhood. The mid-1900s saw some killer game releases. Top shooters included Doom, Quake, Goldeneye, Wolfenstein, Half-Life, Unreal. But NONE of these could hold a candle to the mind-bending stomach-churning six-degrees-of-freedom true 3D shooter that was Descent. Bonus, since it didn’t involve killing humans (and thus, no gore) — evil robots were the enemy here — it was perfectly acceptable to my parents for a young 9-year-old Nate’s innocent eyeballs. The sheer rush of adrenaline as the ‘bots tried to ambush as you barely escaped with evasive maneuvers and turned around to blast them to bits… Pure nostalgic gold.
“But what the blazes are you going on about”, you may ask. Excellent question. Please see YouTube. It’s not the absolute greatest representation of the true 6DoF potential, but you’ll get the idea. And if you get a little queasy, a little motion-sick, that’s perfectly normal. Anybody who’s never played one of these before is likely to need some.. perspective.
Open-Source Developers are Awesome
Here’s why I love developers. Gather a few of ’em together around something they’re passionate about, and watch magic happen. Descent is 25 years old this year. It was made for DOS and Windows 95, as well as a few consoles. Heck, I even have the original CDs from the “Definitive Edition” pack (re-released a few years later). But there’s no way in heck they would run on modern computers with modern operating systems.
Enter open source. Thankfully, the Descent 1 & 2 source code was released to the public at some point. That’s like Christmas Day to developers — anybody with any programming skill could now peek and tweak at the code, even rewrite it from scratch. Two separate projects — called “source ports” — spawned from that seed: DXX-Rebirth, and D2X-XL. The former is simpler and more true-to-form, retaining as much of the original gameplay look & feel as possible, while still enabling it to run on modern systems and adding a few nice conveniences for the 21st century player. The latter is more of a “let’s see how far we can take this” philosophy, in that the author has consistently added many changes and enhancements to the core game mechanics and graphics that, while some players find appealing, I personally take it as “noisy”. But due to its popularity, there are even a number of levels (aka ‘missions’) that will only work with this version. And don’t get me wrong, the work is impressive, by any developer’s standards.
A Legacy Lives On
As with most legendary hit games, a dedicated “mapping & modding” community sprouted up around it. To this day,DescentBB forums are active, and a few members are even still making levels. I even tried my hand at it a couple times. I remember almost begging my parents to buy the re-packaged game box because it included the “Descent Mission Builder” software that let you make your own levels. Hours upon hours spent manipulating cubes and flying through tunnels to test. But nothing compared to the fun of playing through the true masterpieces of level-design produced by the most prolific builders of the day — they pushed the game so far beyond what the creators originally imagined, yet likely dared dream of.
OMG I Must Play!
Then have I got a page for you! Includes download links and step-by-step instructions for Windows users. Bonus, it’ll soon include my own hand-picked custom-levels pack (so you don’t have to sort through the piles of crap that accumulated from half-baked “level contests” and “archive servers” over the decades).
A Challenger Appears
Overload, a spiritual successor to the Descent series, and involving the very founders of Parallax, was released in 2018 after a successful Kickstarter campaign. I haven’t played it yet, but from the footage and reviews I’ve seen, it’s right at home in this game-hall-of-fame.
So what are you waiting for? Catch me on Twitch and watch me play, or godownload all the goodies and check it out yourself!
See you in the mines!
Favorite tagline of many hot-shot pilots and level-designers.
Yes, I wrote a loop to find a gap in an identity-value sequence. Sue me. (Or don’t, that’d be cool too.)
I’ve done some things I’m not proud of. We all do, in IT, typically when we’re under-the-gun for a deadline or when the systems and frameworks in which we work have some sort of nuance or limitation that we just cannot get around, past, or over. And so we hack. We write code we’re not happy with. We even write code that we despise with every fiber of our well-intentioned being. But it has to be done. Because there’s no other choice.
With that somewhat depressing introduction, I give you my latest “dirty deed, done dirt cheap” (or for a reasonable salary, but still very reprehensible for anybody who values clean code and clean architecture). Yes, this is an actual stored-procedure signature that I wrote. Thank gods it’s not integrated into any software; it just happened to serve a niche purpose at the time. But still! I felt ICKY.
Description: Search for an available ID value in Category starting
at the low end (i.e. find a gap), because it's aesthetically pleasing
to have Root level Categories use low ID values. Yes, really.
But mostly b/c they need to be the same on DEV & PROD environments.
CREATE OR ALTER PROCEDURE dbo.[GetNextAvailableRootCategoryID]
@StartAtID int = 10 /*
'1' is not used but we don't feel like starting THAT low
(that'd be like "starting over" or "starting from scratch")
You never get a checkbook with the first check# being '1', right?
Wait, what's a checkbook?
Ask your parents. Or your grandparents. */
, @MaxID int = 1000 /*
Throw an error if we can't find any gaps before this point */
--and you can imagine how that went...
--something about a loop, a couple IF/ELSE's, a RAISERROR or two,
--and a RETURN @val in a B-tree(eeee).
I know, I know. You’re saying “wow that’s really not that bad!”. Or “wow, you REALLY couldn’t do something better about the constraints to make it less terrible?” Again, time, priorities, and energy. What’s 10 minutes in the big scheme of things, even if the code is silly and perpetuates the bad decisions that came before it? Should the overlaying systems actually depend on matching ID values? NO!!! Obviously not. But was that even on the software development roadmap for the next quarter? Also no.
So, what’ve you got? Show me your dirty deeds done in SQL. It’s okay, nobody’s judging! Except yourself, if you’ve any skin in the game. We’re all our own worst critics. But if we can’t laugh at ourselves sometimes, what the hell are we doing here? =)
Be humble, but be confident in your own expertise. Realize that you will sometimes be the teacher, and sometimes the student.
Howdy folks! It’s been a while. My last post was in October of last decade (har har)! I’ve been busy with otherprojects, work, and life in general. 🙂
Now, this month’s invitation is hosted by John Shaulis. I’m always excited to see someone in the #SQLcommunity that I’ve never heard of before. Looks like he’s familiar with the same WordPress templates as I am. Heh. I like it.
Side-note, I’ve been planning to buy my own domain and remove the ads here for a better reading experience (and more professional feel), so hang in there… it’s happening soon!
And now without further ado.
Everybody reading this knows what impostor syndrome is. I won’t bore you with that. Fact is, if you’re in tech, you either have it or you know someone who does. If neither, well you’re probably a narcissist surrounded by other narcissists and you might wanna look for another job. Heh heh.
I’ll share a personal and ongoing example of where I’m constantly feeling “not good enough at my job”. It’s about server migrations. You know, where you take a running production SQL Server instance, and you have to move it to new hardware/infrastructure. Maybe it’s a physical (bare metal) cluster to a virtualized environment. Maybe it’s just one VM to another VM on a newer platform. It typically involves taking a maintenance window and convincing business stakeholders that the “downtime” is worthwhile.
Now surely we’ve all heard of DBATools by now, right? (If not, go there right now and check ’em out!) They were BUILT to do migrations. Yet I’ve not been able to successfully use them as the whole and ONLY toolset for that purpose. Let me clarify: I’ve used pieces of the framework (such as the copy logins / users command, and others) to HELP me in the migration project, but never been able to simply fire off Start-DbaMigration and go get coffee and watch as things magically successfully fall into place.
Even when I’ve built a beautiful check-list, lined up all my scripts, scheduled things via Agent Jobs, double-checked names and permissions and networks and drive letters and shares. Still, something inevitably goes wrong. Maybe it’s just that a database refuses to go into READ_ONLY mode when I ask it to. Maybe it’s that a whole series of jobs get lost because they were in the wrong category. Maybe user permissions don’t come over on this database because I didn’t sacrifice a chicken on the night of the blood-moon while the wind blew north-east.
Surely, by this point in my 10+ years as a database professional, 4 years at this particular company and role, I would be able to do this with my eyes closed. With no hiccups or misfires. Right? RIGHT??
Wrong. Murphy’s Law is a real thing. More than that, things CHANGE. The environment is always evolving to fit the business needs. The technology is always just a little ahead of my own knowledge-base. And every SQL instance in this environment is, unfortunately, still, a ‘pet‘. Not a ‘cattle‘ (cow?). They’re each carefully and fearfully constructed to suit a specific set of application and business needs, each with their own nuances and barely-documented dependencies.
Plus, it’s not like we’re doing these migrations very often. Anything you don’t do on a constant basis, at least a few times per week, tends to sink back toward the bottom of the mental stack, and you forget the details and gotchas that were involved because your brain needs to keep more important and relevant things near the top, for what you’re working on “right now”. This is normal, at least in my mind.
DevOps stole the cookie from the cookie jar!
Just hire a DevOps Engineer to solve all your problems. Then everything will be all sunshine and rainbows and unicorns. All your servers will be cattle, replaceable little containers that are allowed to fail because there’s always another one waiting to spin-up and replace it. Not like your DATA is important or anything. It’s only the lifeblood of the entire company.
Not really sure where I was going with this. I love DevOps, don’t get me wrong, and it does have a part to play in making the traditional RDBMS platform more agile, but the devilish details and difficulties therein are ALWAYS overlooked/brushed-off in popular discourse, and it’s got me a little jaded.
I mostly wanted to finish the song I’d started with the title-blocks. =P
But srsly. Instead of feeling guilty about your impostor syndrome, just own it. Be humble, but be confident in your own expertise. Realize that you will sometimes be the teacher, and sometimes the student. And hopefully more often the latter, because there’s exponentially more to learn every year we evolve on this crazy spinning rock we call Earth.