So you’re working for a client or employer who doesn’t let you bring your own device for security reasons. This is quite common and makes a lot of sense in several ways. But could they really read your HTTPS browser traffic?
Author: daniel
Legacy apps that don’t believe in schemas
Inspired by an actual customer scenario: what if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? There’s a quick and easy solution.
Effective permissions on SQL Server
SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.
Fun with random names
Just for laughs, we’re going to take a look at a slightly-too-complex yet elegant solution to a seemingly simple task: creating a list of 100 fictional company names for a demo database.
How good are best practices?
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?
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.
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.
Automatically guessing foreign key constraints
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.
Finding primary key candidates
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!
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…