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.
- How do you go about expanding your knowledge of SQL Server?
- Do you know when and where the local SQL Server User Group meets?
- Tell me about your experience with SQL Server – when did you start, what things have you done?
- What are some of the new features in SQL Server 2008? 2012?
- In your experience, what are the causes of poor performance in SQL Server?
- In your experience, what are the causes of deadlocks?
- How can you get a deadlock graph from SQL Server?
- What is a “wait”?
- Why are cursors generally considered a bad idea to use in SQL Server?
- What is a “Tally Table”?
- What is a “read-ahead read”?
- What are the different types of backups that can be performed?
- How does the “WITH COPY_ONLY” option affect the different types of backups?
- When restoring a database, what do the REDO and UNDO portions of the process do?
- What is a VLF?
- What conditions must be met to mark a VLF as inactive?
- Under what conditions can dirty data pages be written to the data file?
- Can dirty pages with an ongoing active transaction be written to the data file?
- What causes a transaction log to be cleared?
- What is a CHECKPOINT?
- How does it perform differently based upon the recovery model that the database is in?
- Does a checkpoint write to the transaction log?
- What is “Log Space Reservation”?
- How can you break a transaction log chain?
- What are some examples of minimally logged operations?
- What recovery model does the database need to be in to perform minimal logging?
- What ramifications are there when performing a minimally logged operation?
- Is there anything different about the next transaction log backup? If so, what?
- Can you restore to a point-in-time with this transaction log backup?
- What operations in SQL Server are NOT logged?
- Table Variables?
- TRUNCATE TABLE?
- What recovery model does the database need to be in for the TRUNCATE TABLE statement to be minimally logged?
- What are the Pros and Cons of executing the command “BACKUP LOG WITH TRUNCATE_ONLY”?
- What is a “Page Split”?
- What are the Pros and Cons of shrinking database files?
- How does fragmentation affect performance?
- How does fragmentation affect the size of the IO being read?
- How are UNIQUE and PRIMARY KEY constraints enforced in SQL Server?
- What is a heap?
- What are the different types of indexes (beyond clustered/non-clustered)?
- What attributes constitute a good clustering key?
- Is the data in a clustered index actually stored on disk in strict physical order?
- How does the choice of your clustered index key affect your non-clustered indexes?
- What kind of indexes can you have on a table variable?
- How does the use of a table variable affect the execution plan generated for a query?
- How many indexes can you have on a table? Table Variable?
- Should the Primary Key ALWAYS be a clustered index? Why or why not?
- What is a “uniqueifier”?
- What is the difference between an “Active/Active” and an “Active/Passive” cluster?
- What is a “server-side trace”?
- How do you create a server-side trace?
- How is a server-side trace different from a trace created by Profiler?
- What is “Instant File Initialization”?
- How does using this help SQL Server?
- What database files can this be used on?
- What are the windowing functions in SQL Server?
- 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)?
- What maintenance operations do you run on databases, and how frequently?
- In an execution plan, you can see iterators with “rebind” and “rewind”. What’s the difference between these?
This is a well prepared interview process although it might be worth clarifying what kind of positions it is applicable to. I would say it is more DBA? but also applicable to many development position not so much for BI / testing roles etc.
Here is blog post that is more on SQL BI side (little of DBA questions…. I might borrow some of your questions 😉
//www.katieandemil.com/sql-server-interview-questions-and-answers-pdf-download
Regards
Emil
Reply
Hi Emil,
Yes, this is for a SQL DBA position, though many parts would be good for a SQL Developer position also.
Reply
Oh man! This has scared me half to death! 🙂
I have been studying SQL for the past year with the ultimate aim to getting some kind of job in the field, but these questions show just how little I know.
At my current rate, I think I’ll be studying for another 5 – 10 years before I have any hope of an SQL based job.
Oh well …. 🙂
Reply
For someone starting off, I’d be looking for some knowledge and a willingness to learn. If you know most of the questions from the phone interview, and at the in-person you show that you have investigated and learned those that you missed, then you’d be in a good position for landing a junior position.
Reply
These questions are not for someone at your level, but even if you were asked questions like these that you don’t know the answer to, you would score a lot of points by saying you don’t know and talking about how you would go about finding the answer. A big part about being a DBA is about having processes to figure things out.
Reply
Absolutely right, and I should have mentioned this in my reply.
Reply
I would enjoy this interview very much. Good questions.
Reply
Thanks Robert
I think I’d enjoy your explanations to your answers – I can see tangents coming up and me learning new things from an interview with you.
Reply
Great post because of the breadth of coverage. And I like the lab work idea. Points for wrong set-based versus right anything else is a great idea.
Have you developed anything similar for interviewing potential SSIS developers?
@Steve,
There is a lot of experience behind those questions. I’ve been using SQL Server since v4.2.1 and I learn something new every day. You’ll learn more quickly as you work. Books are wonderful tools but they cannot replace a mouse and keyboard.
Reply
Steve,
Don’t be discouraged from these interview questions. Like Wayne said they aim for DBA roles. There are plenty of other SQL jobs where most people would only answer 1/10 of these questions (= 4-5 answers maybe correct).
Options you’ve got are hybrid roles which are Data Analyst with SQL, Tester with SQL, Junior Development, some or web + SQL and .net + SQL. There plenty of different roles where is knowledge is not used (on certain levels).
If you are after motivation to study then do a plan and I recommend studying towards 70-461 querying with sql server 2012. If you don’t have time to study try our webpage where we will be uploading a few links over the next several weeks and hopefully within 2-3 months we will complete the preparations tutorials
//www.katieandemil.com/katie-and-emil-tutorial-to-70-461-querying-microsoft-sql-server-2012
Reply
@Steve,
Don’t be discouraged and do remember that in certain situations it is just fine to say you don’t know the answer. Sometimes knowing where to look to find the answer(s) is acceptable. More than once I’ve had to respond to a question that I did not know the answer to. I admit I did not know the answer but add that the good news is that I do know how to read!
Reply
I love posts like these because I use them to gauge my SQL Server knowledge level. I’m pleased to say there were only 7 of these I didn’t know the answer to. Looking up those answers now.. @Steve – as others have said, don’t be discouraged. Experience is a great teacher, but you can learn tons by following blogs and reading SQL forums. You may not remember all the details, but at least you can say “I remember reading something about how you can do that.. Let me go google it..” (That’s my answer for #46, by the way.. Read something recently about statistics only database copies or something..)
Reply
I had the privilege of going through this process with Wayne a couple of weeks ago. I was humbled by the things I thought I knew, and also discouraged by the fact I knew how to answer most of them, but being put on the spot to do a lab is overwhelming. I did some research on the topics after my debacle of an interview and now know I have room to grow and plan on tackling a topic each week.
Thanks Wayne and Andy
Reply
I like your positive approach to this Garry. I think that you have a good plan – I try to learn, reinforce, or verify (by doing) something every day. Obviously you ran into an updated lab, which was based upon the needed skills that we need. Good luck in the future!
Reply