Making Interview Questions Better

I’ve written before about technical interview questions, especially of the tricky Brainteaser Interview Questions, and I wanted to take a moment to recount some recent experiences hiring a couple of DBAs.

We’ve had a technical phone interview for some time, written by a colleague, but I think mostly trawled from an internet search of database technical questions.  This undoubtedly has some value, with questions on technical details on the difference, say, between a Primary Key and a Unique Key, or perhaps what ACID stands for.  The problem I find is that day-to-day, a term like ‘ACID’ is almost never referred to, because it is simply (and in some fundamental way) is just expected to be possible and true of a database such as Sql Server – or we might even say prohibited by developers not using transactions.  We needed more structure for the face-to-face bit, and I was fearful about hiring someone who could not even do the DBA equivalent of the FizzBuzz programming exercise.

So for the last year or more, we’ve standardised our face-to-face interview questions to focus on three areas:

  • A practical Normalisation question;
  • Simple SQL queries;
  • Performance Investigation.

In each case, I have taken a practical example of real-world situation from our code-base as the inspiration… and it has been fascinating how helpful the approach has been to better understand the capabilities of the candidate.  I hope it also introduces the interviewee to practical examples of what the role entails. Continue reading

Padding with Zeroes in Sql Server

The problem: Take an integer column with a value like 123 and output it as four digits, prefixing with zero if necessary, i.e. 123 gets output as 0123. You get situations like this if, for example, you store e.g. the last four digits of a card number as an integer-type, and then try and output it. As some cards will end 0xxx this means you have to be prepared to recreate the leading zeroes when displaying the value later. Here are two approaches to solve this problem.

Continue reading

More Keys is More Secure. Right?

Happy Little KeyLast week, we had a heated discussion at work about encryption. We want to encrypt some data in our database, and I proposed that we go with a single private-key encryption mechanism (ignore which exact one for the moment), and my colleagues were pretty-much unanimously suggesting a ‘key per row’ approach. In this post I am going to attempt to explain the rough background, and why I felt their mechanism might not be best.

Continue reading

Data Model Changes and Replication

Replicating Data Model ChangesJust a little reminder note today on the simplest way to make data-model changes in SQL Server 2005 when the database is replicated via transactional replication. This is from my own personal experience of replication and very-much muddling my way through learning how to use it effectively. Otherwise, this posts presumes you are familiar with Replication Monitor and so on.

Continue reading

One Second to Midnight – DateTimes in Sql Server 2005

One Second to MidnightToday I am enthused to write about the DateTime and SmallDateTime datatypes in SQL Server 2005 (and possibly this also applies to 2008, although that has additional date and time types). I am driven to write this because I have seen a number of issues relating to their use in queries and one in particular that is a real annoyance to me – even if I have to admit that it is completely and utterly pedantic (most of the time).

Continue reading

The Basic Lookup Table

‘Lookup Tables’ are commonly created in relational data models and databases as part of the normalisation process. For example, instead of having address rows in an address table that continually repeat the words ‘HOME’ or ‘WORK’ to indicate if this is a customer’s home or work address, we might introduce an ‘AddressType’ table, with a primary key of ‘AddressTypeId’. Our AddressType table may look like this:

AddressTypeId    Name
1                Home
2                Work

Continue reading

My DB Table / Column Naming Conventions

I recently got around to researching a topic on the internet that had intrigued me, that being the question: “Should Table Names be in the singular, or plural?” For example, should you call a table Employee… or Employees? For as long as I can remember, my impression has always veered towards the singular. I don’t believe this is because I am a programmer, concentrating on row-based operations rather than set-based (because, as much as anything else, I actually started learning SQL with Oracle as pure SQL… not in the context of learning it with a third-generation programming language).

Continue reading

Generating Data

I’ve worked on a couple of projects recently where the design of the underlying data-model has, I think, surprised my colleagues. In both cases, there were elements to the design where I have chosen to persist something to the database which I think they may have tried to avoid saving that information; so I wanted to jot down some reasons why it’s a good idea (in these cases anyway).

Continue reading