Featured Database Articles

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.

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?