23/02/2018
If you're worried about interviewing for a SQL Server job, here's some advice from the former head of an IT department on what matters (along with examples from the three critical areas where you need to have answers ready).
As an instructor for Learning Tree's SQL Server courses, I've worked with many students who were taking the course because they were preparing for a new SQL Server-related job. These days, getting those jobs often involves an interview that includes technical questions about SQL Server. As a project lead at several major corporations, the head of an IT department for a large, multi-national heavy equipment manufacturer, and a consultant for multiple clients, I've had lots of experience hiring people. I can tell you the kind of questions I asked when hiring or vetting job candidates and what answers made some candidates stand out from the crowd.
The First Question: Why Are You Doing That?
To begin with, I won't waste your time with a question like "What is a stored procedure?" A question like that is equivalent to a driving test that begins with "Can you point to the steering wheel?" Instead, I'm going to ask you "When would you consider using a stored procedure?" What I'll be looking for in your answer is something like:
Well, I recognize there are costs around administering stored procedures and in splitting business logic between application code and T-SQL code. But there are benefits to using stored procedures when we want to standardize some process that's used in multiple places. And stored procedures improve performance in scenarios where an application is making multiple accesses to the database. Using a stored procedure can reduce those multiple trips to a single one. Those are the places where I'd use one. And, of course, the organization may mandate using stored procedures for security or regulatory reasons.
An answer like that not only tells me that you know what a stored procedure is, it also tells me that I can count on you to use a stored procedure whenever I would. I like that.
But even if you don't give the reasons that I just listed, an answer that reflects some basis for choosing stored procedures over other solutions will still impress me. Mostly, I just want to know that you've thought about when to use a stored procedure and recognize the costs and benefits of using one. If you don't give my reasons, that's not a big deal because, if I hire you, all I have to do is tell you, "By the way, around here, we use stored procedures based on these reasons...".
Your Interview Strategy: Think about when and why you use any piece of SQL Server technology (and, also, when and why you don't use it). If you don't have a reason -- if you're just doing something because someone told you to -- research the topic so you have a reason ready in your interview. When asked about any SQL Server technology, find a way to integrate those reasons into your answer.
Second Question: Debugging
If I hire you, the reality is that most of the time you'll be extending, modifying, or enhancing existing applications. That means I need you to be able to figure out how my existing applications work. Furthermore, when things go wrong, I'll need you to be look at the existing environment, diagnose what's going wrong, propose a solution, implement it, and be able to tell me if it worked.
I'll test for this by giving you some buggy or poorly performing code and asking you to tell me what's going wrong. I'll expect you to start working through the problem, explaining your thought process as you do.
As a simple example of this part of the interview, I might ask you why this SQL statement is generating a 'data mismatch' error:
Select FName As FirstName, LName As LastName, Salary * 12 As AnnualPay
From Employees As emp
Inner Join PayScales As ps
On emp.PayCode = ps.PayCode
Where EmpId = 12;
It's great, of course, if you give the same answer I would (in this case, that EmpId is probably a varchar column and can't be compared to a numeric literal -- though, I suppose, it might also be the same problem with the Salary column). But what I'm really interested in is where you look first, what questions you ask in exploring the problem, and how you zero in on your answer. Really, I want to know what process you follow when solving a problem. If you just start blindly suggesting answers that might make the problem "go away," I'm going to be unhappy.
Your Interview Strategy: Pay attention to how you solve different kinds of problems so that you can describe (and improve) your process. Practice "thinking out loud" so that you can explain to someone else how you get to the right answer. You might also want to consider exploring other people's debugging strategies to further improve your own (here's mine).
Third Question: Building Maintainable Solutions
Finally, I won't want to hire you if you're going to implement solutions that my organization can't live with. That means two things: Solving the right problem and providing a "maintainable" solution.
To test for this, I'm going to give you a business problem and ask you to develop a solution. I'm going to be looking for you to ask questions that clarify what the user wants because I want you to solve the correct problem (e.g. "Do you want only the customers with no orders or all the customers but highlight the customers with the no orders?"). In addition, I don't want to see see some ninja-level solution that no one, other than you, will ever understand.
For example, I might ask you to build an SQL statement that would show the hierarchy of managers from that employee to the head of the organization, I'd expect you to ask if I need the query to produce the hierarchy for all employees, only employees that meet some criteria, or for a single employee. I'd also expect you to ask what data should be displayed for each employee/manager combination.
In the solution, you might use a recursive Common Table Expression (CTE) though there's a stored procedure solution that would also work. More importantly for your job prospects, though, if a recursive CTE was your solution then I would expect you to recognize that recursive CTEs aren't a common piece of SQL technology. In fact, I wouldn't be surprised if (a) you had to search Stack Overflow for an example to follow when writing the SQL statement and (b) your first solution wasn't perfect. CTEs being so rare, though, I would also expect you to put some comments in the solution to explain what was going on in that CTE or, at the very least, ask me if I wanted comments added to your sample solution.
My ideal answer, using a CTE, would look something like this:
--A CTE to recursively search up the "chain of command" for an employee
With Managers (mgrId, empId, Level)
As (
--This branch of the CTE terminates the search when we get to the company
-- CEO(indicated by null in the mrgId column for the CEO - this won't work
-- if the CEO's mgrId column isn't set to null)
Select emp.mgrId, emp.empId, 0 AS Level
From Employees As emp
Where mgrId Is Null
Union All
--This branch of the CTE will find the employee that the current
-- employee works for, held in the mgrId column)
Select emp.mgrId, mgr.empId, Level + 1
From Employees AS emp
Inner Join Employees As mgrs
On emp.mgrId = mgrs.empId
)
--Use the CTE in a query with the employees table
Select mgrId, empId, Level
From Employees As emp
Inner Join Managers As mgrs
On emp.mgrId = mgrs.empId
Where emp.empId = @empId;
Your Interview Strategy: As I said, I won't expect your solution to be flawless so don't worry about that (the interview period won't give you enough time to test/debug your solution). Do, however, get in the habit of making sure you understand the specification before you start building a solution. And don't try to show how clever you are by building a complex solution! Instead, concentrate on a solution that any developer could enhance. And, by the way, make sure you tell me that's what you're striving for (that will impress the heck out of me).
Final Advice
My last piece of advice is, if you want the best preparation for your interview, take one of Learning Tree's SQL query courses (you'll write and debug lots of SQL code and get expert advice on all of the available SQL Server options from a live instructor who'll answer all of your questions). However, when you go to your current boss to ask to be sent on the course, don't say that it's because you want to improve your ability to get some other job. That just leads to all sorts of awkward questions. And you probably won't get to take the course, either.
Related Training:
SQL Server Training