This blog post summarizes the type of technical questions I would ask candidates for a Microsoft SQL Server data platform administrator and database developer role.
Hopefully this helps both candidates and managers prepare for interviews. I have no qualms in providing the brief answers because your interview, like mine, should be:
- behavioral: based on scenarios, not multiple choice answers.
- open ended: ask for an explanation, not a single word answer.
- conversational: testing how the candidate would explain this to a client or colleague.
- applicable: only ask questions relevant to your environment and in the job description.
When I was the manager of a SQL Server managed service provider and a principal consultant at a consulting company, I interviewed and hired database administrators to be consultants, remote DBAs, and database developers for our app dev internal projects.
I've divided the 30 questions into four categories.
1. Speaking generally, what is the basic relationship between a database, a table, and a column? Yes, this is a super simple question. The point is to hear how the candidate organizes their thoughts and explains the concepts. You'd be surprised how many candidates struggled with explaining even basic concepts. This appeared to be especially true of candidates who had more server admin experience than data experience.
2. What is the SQL Server transaction log and how does it work? How does it play a role in DR? This answer should hit the important notes about recovery models, log backups, RTO and RPO.
3. Tell me about the different kind of backups that can be performed on a SQL Server database, and when do you typically perform them? Continuing from a conversation about the transaction log and the database recovery model, the qualified candidate should definitely mention the transaction log, perhaps mentioning how a transaction log backup is an incremental backup, as opposed to a differential backup, or a full backup. The full backup is the start of a chain which then includes differential and transaction log backups.
4. Explain the difference between primary and foreign keys. The qualified candidate who understands the concepts should start talking about the integrity of data between tables, and perhaps given an example of the type of data that would be constrained by a foreign key. I've heard some pretty far off answers to this question that expose when a candidate has clearly never designed tables before.
5. What is an IDENTITY column and when would you use one? The qualified candidate should talk about the advantages of an auto-sequencing number as a primary key in relational table design. Bonus points for a rant about clustered keys on GUIDs being an antipattern.
Database design questions
Database development questions
21. Tell me how you'd write a query to return all the records in table A that match a common key record in table B? The answer is an INNER JOIN, something like SELECT ... FROM TableA inner join TableB on TableA.Key = TableB.Key; Answers involving a UNION or a comma join or a WHERE clause or some convoluted subquery are not what I am looking for.
Optional homework for the candidate
Legal stuff that HR wants you to know
Do not ask questions that have nothing to do with the candidate's job qualifications. In your interviews with candidates, you don't need to know, and should not ask, about:
- their family, kids, or plans for having kids,
- relationship status,
- date of their education graduation,
- race or cultural background, including the origin or meaning of their name,
- place or country of birth,
- sexual orientation or gender identity,
- political persuasion,
- or private medical history.
- You don't need to know about their disability status, only if they need an accommodation.
- You don't need to know if they are citizens or immigrants to your country, only if they can legally work in the country.
- You don't need to know how much money they made at their last job.
If you have questions about the above, ask a qualified human resources professional. Remember that if the candidate seems like someone you want to have a beer with, that has nothing to do with the job.
Questions? Your experience? Other good questions you ask? Feel free to add them in the comments below.