Do something else, become better at what you love

Ok, time for a short “professional development” post to balance out all of the technical stuff.

For a couple of years, I worked as a spin instructor on the side. It was initially something I wanted to try for the challenge, it proved to have a lot of other benefits to my life besides the obvious health impact.

Performance tuning, IRL edition.

I picked up a huge amount of tricks and patterns along the way. Some of them may appear a little abstract, like working with diverse groups of people, dealing with motivations and meeting expectations, getting comfortable with public speaking and managing nervousness. Others are much more tangible, like microphone skills.

Then again, there are plenty of superpowers that will probably never be very relevant to my (current) line of work, like counting in a beat of a song.

But I digress: Having that extracurricular activity has an immense potential to improve your ability to do other things that you love or get paid for. You don’t have to go and apply to your local gym, and it doesn’t even have to be very expensive – just pick anything that challenges you.

You’ll find that even though the specific superpowers from your hobby may not apply at all to your work, there will be unexpected, derived skills and understandings, that will help you grow as a human and as a professional.

I’m sure there’s a management book on the topic, but this is my short version of it. Now, go and do awesome things!

How to use switching to make metadata changes online

Metadata changes, like modifying a clustered index, or many types of column changes, will create locks in SQL Server that will block users from working with that table until the change is completed. In many cases, those locks will extend to the system objects, so you won’t even be able to expand the “Tables” or “Views” nodes in Management Studio.

I want to show you how you can perform those changes using a copy of the table, then instantly switching the table with the copy. The secret is partition switching, and contrary to popular belief, you won’t need Enterprise Edition, or even partitions, to do it.

Continue reading

Aggregating date and time values

Date and time values are not entirely intuitive to aggregate into averages in T-SQL, although the business case does arguably exist. Suppose, for instance, that you have a production log with a “duration” column (in the “time” datatype), and you want to find the totalt or average duration for a certain group of items.

It’s possible, but I would still call it a workaround.

Continue reading

Anonymize your dev databases!

I’ve been working on a little gadget for a while now, and today I finally got around to completing it and so now I’ve published it for everyone to try out. It’s a web API (wait, wait, don’t go away – it’s for database people!) that creates a randomized list of names, addresses, etc.

In this post, I’ll show you how easy it is to use this service to anonymize a development or test database so you don’t have all that personally identifiable information floating around.

Continue reading

Scan hundreds of SQL Saturday raffle tickets. Fast.

If you’re an organizer, sponsor and/or an exhibitor at a SQL Saturday event, you often collect raffle tickets from attendees. In exchange for giving you their contact information, they have the opportunity to win cool raffle prizes.

Here’s the thing, though. Raffle tickets can be a bit painful to scan on your mobile phone. You point the phone at the QR code, then click the URL that opens up the browser, after which the SQL Saturday web service takes another second or two (or three) to load. This is fine when you have just a few tickets, but it can be mindnumbing if you have hundreds.

What can I say, I’m lazy.

Continue reading

How to get an automated alert when your Azure cost takes off

I wrote this quick-and-dirty script to let me know if I happen to forget to turn off a P15 instance, or if I configure a service with a super-expensive performance tier without realizing. Maxing out your free Azure credits may be depressing enough, but emptying your credit card could really put you in the hurt locker.

So, here’s a Powershell script that warns me before any of this happens. It uses the Azure Consumption API to check how much money we’ve racked up on a subscription so far, and if any single instance exceeds, say, 50% of that total cost, it sends a notification to a Slack channel.

Continue reading

CAST/CONVERT makes expressions nullable

I came upon this issue when I was building some views to support legacy integrations to an app that I was refactoring. The view is supposed to have exactly the same column definitions as a table in the old database that I am redesigning, so to make SSIS packages and other integrations run smoothly, I want the view’s columns to have the same datatypes, nullability, etc.

But there are some gotchas to watch out for with CAST and CONVERT.

Continue reading

How to set up a beautiful Powershell Core terminal on Mac OS

I just recently had the opportunity to sit with Aaron Nelson and go through some really cool Powershell features, and I’m certainly going to spend time getting to know Powershell a lot better. If you didn’t know, Powershell isn’t exclusive to Windows anymore – you can actually run a basic set of Powershell features, called Powershell Core, on Mac OS and Linux as well.

But there’s a problem.

Continue reading