You’ll find a lot of people, consultants and peers, liberally dispensing plenty of “best practices” on the Interwebs and in real life. Heck, I’d say that’s pretty much all I do on this blog. But when should you go with the best practice and when should you make your own road?
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.
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.
With good naming and datatyping conventions, an automated script can help you with the process of creating foreign key constraints across your database, or actually, suggest table relations where you’ve forgotten to implement them.
Probably one of the most common challenges I see when I do ETL and business intelligence work is analyzing a table (or a file) for possible primary keys. And while a bit of domain knowledge, along with a quick eye and some experience will get you really far, sometimes you may need some computational help just to be sure.
Here are some handy tricks to get you started!
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…
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.
The binary datatype of SQL Server is one of those features most developers don’t really use that often, but it turns out there’s more to binary values than just storing large, non-relational blobs.
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.
If all you have is a hammer, everything will eventually start looking like a nail. This is generally known as Maslow’s hammer and refers to the fact that you use the tools you know to solve any problem, regardless if that’s what the problem actually needs. With that said, I frequently need a way to visualize the load distribution of scheduled jobs over a day or week, but I could never be bothered to set up a web server, learn a procedural programming language or build custom visualizations in PowerBI.
So here’s how to do that without leaving Management Studio.