Lately, I’ve been asked by several folks about the interview questions that I use when I interview candidates for SQL positions. I have the interview process broken down into three processes:

Part 1: Phone screening questions.

I really like the questions that Grant Fritchey has on his blog. To get in the door for an in-person interview, the candidate needs at least 5 of these correct. The only thing I change is that I insert into the #8 slot this question (for a total of 11 questions): You have a query that joins multiple tables. What is the difference between applying a filter in the WHERE clause versus applying that filter in the JOIN conditions?

Part 2: Lab.

During the in person time, I pass my laptop to the candidate. There is a “Lab” database on it, and I have two challenges for them. The first is the “fizz-fuzz” test, modified to return 1,000 rows. The second is to create a delimited string. The candidate can ask me anything to clarify questions; otherwise they are free to search the internet and use any tools. I stay and observe – my primary reason is that I want to observe the process in how the candidate goes about solving problems. The Lab database contains three tables that are used: An “AccountData” table with 1000 accounts (1-1000), each with 1000 rows with a value from 1-1000 (for a total of 1,000,000 rows). This table is to have a comma delimited string made from it for each account, with the values in numerical order. The second table is “ResultSet”, which is what I expect the fizz-fuzz data to look like. The third table is a “Tally” table, with numbers from 1-1000. Hint: I’m looking for set-based answers here. I actually give more credit for a set-based answer that is wrong (but close) over a non-set-based answer.

Part 3. In Person Interview Questions.

The in person interview questions consist of a grueling set of questions designed to get deep into SQL and tell me what the candidate doesn’t know… and what I will need to educate the candidate in. I also will ask any question from the phone screen that the candidate answered wrong just to see if they have taken the time and effort to figure out what the correct answer is. Depending on what level of knowledge the position requires, a successful candidate may answer less than ten of these questions correctly. I start off with some soft questions to get the candidate talking. Some of these questions may not have a right answer. I’m not providing answers to these questions – if you don’t know the answer, go figure it out.

1. How do you go about expanding your knowledge of SQL Server?
2. Do you know when and where the local SQL Server User Group meets?
3. Tell me about your experience with SQL Server – when did you start, what things have you done?
4. What are some of the new features in SQL Server 2008? 2012?
5. In your experience, what are the causes of poor performance in SQL Server?
6. In your experience, what are the causes of deadlocks?
7. How can you get a deadlock graph from SQL Server?
8. What is a “wait”?
9. Why are cursors generally considered a bad idea to use in SQL Server?
10. What is a “Tally Table”?
11. What is a “read-ahead read”?
12. What are the different types of backups that can be performed?
13. How does the “WITH COPY_ONLY” option affect the different types of backups?
14. When restoring a database, what do the REDO and UNDO portions of the process do?
15. What is a VLF?
16. What conditions must be met to mark a VLF as inactive?
17. Under what conditions can dirty data pages be written to the data file?
    1. Can dirty pages with an ongoing active transaction be written to the data file?
18. What causes a transaction log to be cleared?
19. What is a CHECKPOINT?
    1. How does it perform differently based upon the recovery model that the database is in?
    2. Does a checkpoint write to the transaction log?
20. What is “Log Space Reservation”?
21. How can you break a transaction log chain?
22. What are some examples of minimally logged operations?
23. What recovery model does the database need to be in to perform minimal logging?
24. What ramifications are there when performing a minimally logged operation?
    1. Is there anything different about the next transaction log backup? If so, what?
    2. Can you restore to a point-in-time with this transaction log backup?
25. What operations in SQL Server are NOT logged?
    1. Table Variables?
    2. TRUNCATE TABLE?
26. What recovery model does the database need to be in for the TRUNCATE TABLE statement to be minimally logged?
27. What are the Pros and Cons of executing the command “BACKUP LOG WITH TRUNCATE_ONLY”?
28. What is a “Page Split”?
29. What are the Pros and Cons of shrinking database files?
30. How does fragmentation affect performance?
    1. How does fragmentation affect the size of the IO being read?
31. How are UNIQUE and PRIMARY KEY constraints enforced in SQL Server?
32. What is a heap?
33. What are the different types of indexes (beyond clustered/non-clustered)?
34. What attributes constitute a good clustering key?
35. Is the data in a clustered index actually stored on disk in strict physical order?
36. How does the choice of your clustered index key affect your non-clustered indexes?
37. What kind of indexes can you have on a table variable?
38. How does the use of a table variable affect the execution plan generated for a query?
39. How many indexes can you have on a table? Table Variable?
40. Should the Primary Key ALWAYS be a clustered index? Why or why not?
41. What is a “uniqueifier”?
42. What is the difference between an “Active/Active” and an “Active/Passive” cluster?
43. What is a “server-side trace”?
    1. How do you create a server-side trace?
    2. How is a server-side trace different from a trace created by Profiler?
44. What is “Instant File Initialization”?
    1. How does using this help SQL Server?
    2. What database files can this be used on?
45. What are the windowing functions in SQL Server?
46. How can you get an execution plan for a query on a server when you don’t want to run that query on that server (i.e. a production server)?
47. What maintenance operations do you run on databases, and how frequently?
48. In an execution plan, you can see iterators with “rebind” and “rewind”. What’s the difference between these?