Top 9 SQL Server Developer Interview Questions

The questions you may wish to ask or that will be asked during a SQL Server Developer interview will vary. Deanna Dicken shares the top 9 interview questions she asks when interviewing a potential SQL Server developer.
Introduction

In my many years in Information Technology, I have performed so many SQL Server interviews that I have long since lost count. I thought I would share with you some of the questions I find important when interviewing a potential SQL Server developer.
What is Referential Integrity?

Let's start with the basics. Regardless of the RDBMS they are targeting, I'd really like them to understand that beyond all else we need to ensure referential integrity. To ensure it, we have to understand it. I am listening for an answer along the lines of ensuring the relationships between parent and child tables and that the primary keys and foreign keys facilitate this integrity.
What is the difference between a clustered and a non-clustered index?

This is one of my favorite questions for sorting out the developers that have an understanding beyond your basic T-SQL to more of an understanding of performance. I'm looking for them to tell me that a clustered index represents the physical order of the rows as they are stored in the database. A non-clustered index is a logical ordering of the rows.

Both types of indexes facilitate faster lookups and joins.

As a follow-up question, should they get this right, ask them to tell you how many clustered indexes you can have on a table. The answer should be one. Rows can only be physically stored with one ordering.
What are DML triggers and what types of triggers can you have on a SQL Server table?

A trigger, as the name implies, is a collection of SQL statements triggered to execute by an insert, update, or delete action on a table that affects its contents. INSTEAD OF and AFTER can be specified for each trigger. When INSTEAD OF is specified, the trigger takes the place of the triggering action. So if you had an INSTEAD OF UPDATE trigger, the code inside the trigger would be executed instead of the original update. AFTER triggers are the same as the FOR used in earlier versions of SQL Server. This just means the trigger will execute following the original action. An AFTER UPDATE trigger will allow the update to complete and then the update trigger will execute. For more information, see DML Trigger Planning Guidelines.
What is a four-part name?

This question can help you get a sense for whether the candidate ever crosses database boundaries with his queries. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Prod instance, the four part name would look like this: Prod.AdventureWorks.HumanResources.Employee.
What is a Linked Server?

Another question that can tell you if the candidate has experience with distributed databases is "What is a Linked Server?" A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.
How do you handle an error from within a stored procedure?

This one's a little open-ended. One, it depends on which version of SQL Server they work with. In SQL Server 2005 Microsoft introduced TRY…CATCH error handling. The code that could error is placed within a TRY block. If an error does occur, the code within the CATCH block executes. Several functions are available to assist in gleaning information about the error. See TRY…CATCH in Books Online for more information.

Prior to this, @@Error could be checked for an error code and then an error thrown using RAISERROR. Some may answer that they return the error in an output parameter or by using PRINT. RAISERROR can still be used to throw an error. As a matter of fact, a RAISERROR called from the TRY block with a severity between 11 and 19 will cause execution to jump to the CATCH block.
How do you auto number rows in SQL Server?

Sometimes I have to rephrase this one to get the answer. What I'm looking for here is the candidate's knowledge of identity columns. Identity columns are used to automatically increment a column in a table from a seed (or starting) value and by a defined increment. If no seed is specified and no increment is specified, the first entry is 1 and the subsequent entries are incremented by 1. If either the seed or the increment is specified, other must be specified.
What is a left outer join? Give an example.

The answers I get on this one blow me away. I can't believe how many candidates I've interviewed that do not grasp this concept. Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1

What is a cursor and why would you use it?

The candidate will probably be able to tell you cursors are used to perform row-by-row processing on a result set. The candidate might tell you they use them for batch type processing. In their answer though I am secretly hoping the candidate will elaborate on performance issues with cursors and how it is preferred to use loops or set-based processing instead of cursors. If not, the follow up question here is "Is there another way to process the result set row-by-row without using a cursor?" To see more of my thoughts on cursors, see What Every DBA Ought to Know About SQL Server Cursors (and Their Alternatives).
Conclusion

The questions you may wish to ask or that will be asked of you will vary (like your mileage). Questions should be tailored to the job for which the candidate is applying. For instance, if you don't use triggers in your workplace, does it really matter if the candidate has a working knowledge of SQL Server triggers?