Prioritizing rows in a union

I just remembered a pretty common data challenge the other day. Suppose you have a number of tables, all with similar information in them. You want to union their contents, but you need to prioritize them, so you want to choose all the rows from table A, then rows from table B that are not included in A, then rows from C that are not included in A or B, and so on.

This is a pretty common use case in data cleansing or data warehousing applications. There are a few different ways to go about this, some more obvious than others.

Continue reading

Accounting basics for tech people

Just like the tech business, accounting uses loads of acronyms and seemingly undecipherable names, but the basics are actually really simple. I’m not saying that accounting in and of itself is simple, but rather that the concept is quite understandable and convenient if you approach it correctly.

The goal of this post is to provide a basic understanding of the basic principles of accounting, which a lot of tech people (particularly in the database business) will at some point encounter.

Continue reading

Please don’t feed auditors and lawyers


Remember that time when you accidentally truncated a table in production? Or when you forgot the WHERE clause in your UPDATE statement? You’re not really a seasoned professional if you haven’t. There’s even a very apt name for that moment in time when the realization hits you: The oh-no second.

But what if there was some type of control to prevent this from happening? Like more restrictive controls, perhaps some type of peer-review process before you clicked “go”? Or even…

Continue reading

Speaking at the Group By conference!

This past Friday, I had the great privilege of speaking at the on-line Group By conference. Group By is a community-driven conference where anyone can submit an abstract. Site visitors will then rate sessions as well as help you build and improve your abstract.

My presentation was about various tips and tricks in SQL Server Management Studio, some of which I’ve already covered in previous articles on this blog.

Continue reading

Have you tried sp_ctrl3?

I frequently need to look up object definitions when I’m developing or query tuning. You could use Object Explorer in SSMS, but that takes a lot of time and clicking. Then there’s the Alt+F1 shortcut, which will trigger the sp_help stored procedure. That however, comes with a lot of annoying built-in limitations, so a few years ago I started building and maintaining a “better Alt+F1” of sorts.

I decided to call it “Ctrl+3“. But I suppose you could assign it to any keyboard shortcut you want.

Continue reading