SQL Interview Questions

Tim Chapman has an article on TechRepublic describing questions you might ask a SQL Server database developer applicant. I think a more accurate title for the article would be "How to Make Sure You Hire a Mediocre SQL Server Database Developer." The questions Mr. Chapman suggests can be answered by anyone who has remained awake for a few of their relational database theory classes or have skimmed one book. And if you want to ensure that your prospects have, at least, heard of relational databases, by all means use these questions.

On the other hand ... well, let me give you a few examples of my interview questions:

• What is the difference between a table variable and a temp table, their intrinsic and situational drawbacks, and some of their uses? What happens if you do a "select into" a temp table without defining the table ahead of time?
• What constraints are there on calling stored procedures from user defined functions?
• If you're doing a left outer join against a large table with 0:1 cardinality (hey, what is cardinality?), how might you rewrite the operation to be more efficient?
• With regard to indices, what's one of the benefits of using integer primary keys as opposed to guids?
• What are some of the techniques for managing record change audit trails and their drawbacks/benefits?
• Suppose you're considering changing the parameter list on a stored procedure. How might you determine if any other stored procedures call the one you're wanting to change?
• What should you worry about if you're using more than one SQL connection in the same operation? Does the operating system matter?
• What's a two-phase commit? When is it used? What does it mean to have a transaction "in doubt"?
• You have a procedure that you've been told is running too long. What steps would you go through to track down and resolve the issue?

And these are just the questions for SQL 2k. The ones for 2k5 get even more fun.