Data Management optimize

Questions to ask a SQL Server database developer applicant

Tim Chapman presents his standard set of baseline technical questions he asks SQL Server database developers during an interview. These questions will help you weed out less-qualified candidates.

It is very difficult in today's job market to find well-qualified database developers. As a colleague of mine once said, "SQL development is a language that is easy to learn, but very difficult to master."

When I interview SQL Server database developer candidates, I use a standard set of baseline technical questions. The following questions are ones that I find can really help weed out less-qualified candidates. They are listed in order from easiest to more difficult. When you get to the question about primary and foreign keys, it starts to get challenging because the answers can be more difficult to explain and articulate, especially in an interview setting.

Can you give me an overview of some of the database objects available for use in SQL Server 2000?

You are looking for objects such as: tables, views, user-defined functions, and stored procedures; it's even better if they mention additional objects such as triggers. It's not a good sign if an applicant cannot answer this basic question.

What is an index? What types of indexes are available in SQL Server 2000?

Any experienced database developer should be able to answer this question with ease. Some of the less-experienced developers will be able to answer it, but with a little less clarity.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

In its most simple terms, an index is a data structure used to provide quick access to data in a database table or view. In SQL Server, they come in two flavors: clustered and non-clustered. Clustered indexes store the data at the leaf level of the index. This means that whichever field(s) in your table are included in the clustered index, they will be stored in an orderly fashion in the table. Because of this sorting, you can only have one clustered index per table. Non-clustered indexes contain a row identifier at the leaf level of the index. This row identifier is a pointer to a location of the data on the disk. This allows you to have more than one non-clustered index per table.

What does NULL mean?

The value NULL is a very tricky subject in the database world, so don't be surprised if several applicants trip up on this question.

The value NULL means UNKNOWN; it does not mean '' (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL. You cannot compare any value with an UNKNOWN value and logically expect to get an answer. You must use the IS NULL operator instead.

What is a primary key? What is a foreign key?

A primary key is the field(s) in a table that uniquely defines the row in the table; the values in the primary key are always unique. A foreign key is a constraint that establishes a relationship between two tables. This relationship typically involves the primary key field(s) from one table with an adjoining set of field(s) in another table (although it could be the same table). The adjoining field(s) is the foreign key.

What are triggers? What are the different types of triggers in SQL Server 2000?

It's very beneficial for a potential database developer to know the types of triggers available, and how to implement them.

A trigger is a specialized type of stored procedure that is bound to a table or view in SQL Server 2000. In SQL Server 2000, there are INSTEAD-OF triggers and AFTER triggers. INSTEAD-OF triggers are procedures that execute in place of a Data Manipulation Language (DML) statement on a table. For example, if I have an INSTEAD-OF-UPDATE trigger on TableA, and I execute an update statement on that table, the code in the INSTEAD-OF-UPDATE trigger will execute instead of the update statement that I executed.

An AFTER trigger executes after a DML statement has taken place in the database. These types of triggers are very handy for auditing data changes that have occurred in your database tables.

How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

This relationship related question has two potential answers. The first answer (and the one that you want to hear) is the use of foreign key constraints. A foreign key constraint is used to maintain referential integrity. It is used to ensure that a field in a table will only hold values that are already defined in another field in a different (or the same) table. That field is the candidate key (usually a primary key of the other table).

The other option is the use of triggers. Triggers can be used to ensure the same effect of constraints in a roundabout way, but it is much more difficult to set up and maintain, and the performance is typically worse. Because of this, Microsoft recommends that developers use foreign key constraints instead of triggers for maintaining referential integrity.

What is a performance consideration of having too many indexes on a production online transaction processing (OLTP) table?

You are looking for the applicant to make some reference regarding data manipulations. The more indexes on a table, the more time it takes for the database engine to update, insert, or delete data, as the indexes all have to be maintained as the data manipulation occurs.

What can be used to ensure that a field in a table only accepts a certain range of values?

This question can be answered a couple of different ways, but only one answer is a "good" one. The answer you want to hear is a Check constraint, which is defined on a database table that limits the values entered into that column. These constraints are relatively easy to create, and they are the recommended type for enforcing domain integrity in SQL Server.

Triggers can also be used to restrict the values accepted in a field in a database table, but this solution requires the trigger to be defined on the table, which can hinder performance in certain situations. For this reason, Microsoft recommends Check constraints over all other methods for restricting domain integrity.

What is the difference between a return parameter and an OUTPUT parameter?

If the applicant is able to answer this question correctly, the odds are good that they have some experience working with stored procedures.

A return parameter is always returned by a stored procedure, and it is meant to indicate the success or failure of the stored procedure. The return parameter is always an INT data type.

An OUTPUT parameter is designated specifically by the developer, and it can return other types of data, such as characters and numeric values. (There are some limitations on the data types that can be used as output parameters.) You can use multiple OUTPUT parameters in a stored procedure, whereas you can only use one return parameter.

What is a correlated sub-query? How can these queries be useful?

The more seasoned developer will be able to accurately describe this type of query.

A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from the outside query, creating a situation similar to a loop. You can find a more detailed description as to how these special types of queries work in this article.

Closing thoughts on the interview process

These questions are just a starting point to determining a SQL Server database developer's qualifications. Based on how well they perform in the discussion, I may ask the candidate to take my TSQL programming test, which is usually a set of 10-12 database queries based on different scenarios.

You will need to decide for yourself what the skill set should be of the developer you wish to hire. Then, you will need to use your own experience, judgment, and feel for applicants as you interview them.

What questions do you routinely ask when you interview database developer candidates? Let me know by posting to the article discussion.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

25 comments
unaiseek
unaiseek

I am using vb.net with sql client. i have on problm. i created one table with fields author1, author2, author3. Now i want to combine these three fields items into new table field Author. all datas in author1,2,3 are will be listed one by one under author field.

aanshi.rocks
aanshi.rocks

from this table i have to take those records that have common gridcode and then from these records i have to store the records having maximum area in another table... gridcode state area 13013 rajasthan 50000 13013 gujarat 45000 13014 rajasthan 25000 13014 gujarat 25000

rsell01
rsell01

I'm interviewing for a Jr SQL Developer job next week. Do you think i will see question like this or something a little easier? I really enjoyed reading this. Thanks!!

m4rk.gm4il
m4rk.gm4il

I may sound out of topic but i cant find a topic related to this one. it would be nice if someone could provide this article in techrepublic. thanks!

Robert Tomlin
Robert Tomlin

I think instead of just a definition of a trigger I might ask "when would one use a trigger?" The answer given would imply the definition is known (or not) and give some insight in to their actual knowledge of triggers.

oisleach
oisleach

I would expect questions about what is an inner join, outer join , and the efficiency related to them

gsquared
gsquared

I'd hand them a computer with a badly designed local database on it and ask them to identify some problems with it. Make it something with 3 or 4 tables, maybe a customers table and a contacts table and an orders table. Name the tables Table01, Table02, Table03, give the columns similar names (Col01, etc.), use a trigger on the orders table to make sure the customer ID is in Table01 in Col00, instead of a foreign key. Have dozens of triggers instead of check constraints. Have all the columns be Char(100). Maybe 10 or 15 rows of data per table. Have a blank password for the sa login. Have a stored procedure that has a char(8000) input parameter, which uses "exec (@param)" as the only line of code in the proc; name the proc MainProc. If the applicant takes one look at the database, shudders in horror and asks nervously if this is what your real databases look like, odds are good that he's going to be okay as a DBA. If he instead says something like, "Well, you should add some indexes so it's faster", politely tell him you'll call him and that he shouldn't call you. Come up with a few variations, one of which violates every standard practice you can think of, one which is merely bad without being an abomination, another that has some simple-to-fix-but-obvious problems, and another which is a little bit tricky to spot the problems in and which generally follows best practices (maybe index too many columns per table and otherwise have a good database). If you take the time to weed people out based on which scenarios they can spot and handle, you'll do well. Again, keep the databases simple, with only a few tables and a couple of stored procedures/views/UDFs, so they can be analyzed quickly and easily. Asking simple questions like a list of database objects is okay if what you're looking for is someone who skimmed through SQL for Dummies and memorized a few basics, but doesn't know Varchar(100) from Text. Having a laptop with some messed up databases on it as a test is much more thorough and will also help you spot the guys who, like many in IT, aren't excellent at being interviewed, but who actually know what they're doing.

Tony Hopkinson
Tony Hopkinson

There's a few ways of doing this and you haven't said how the table is to be keyed Quick one Select Author1 as Author from Authors Union Select Author2 from Authors Union Select Author3 Author from Authors Above assumes each author occurs only once in the entire table Given you probably have book / document ID in there and not all books have three authors. Select DocumentID, Author1 as Author from Authors Where Author1 is not Null Union Select DocumentID, Author2 as Author from Authors Where Author2 is not Null Union Select DocumentID, Author3 as Author from Authors Where Author3 is not Null Or some such is likey to work. Classic newbie DB design error by the way, always start normalised. If you create you new table, then prefix the above with Insert into NewAuthors, you are off and running HtHs

ken
ken

At the top of every C# file there are typically items such as "using System;". Name as many different libraries as you can remember.... What you want to note is that the ones that roll off the tongue are those they constantly use/have to add. As they slow down, it becomes occasional... If you are looking for someone to do heavy XML work and they exclude System.Xml.Schema; System.Xml.Xpath; System.Xml.Schema.Xsl; -- be warned. If they claim to have used SQL, then they should be able to name System.Data.SqlClient at least. This simple question reveals a lot about their past experience and familarity (One part of the decision to hire). If the person has only been doing MAINTENANCE, they will often be unable to name any of them -- Why? because they do not have to add them to the files they are working on --- they are already there.

Tony Hopkinson
Tony Hopkinson

stuff. They key thing though is to tie it in to what they are going to be developing. I ask questions that test the limits of their application of the language. It's not so much a pass or fail test, but tests where the knowledge and their experience tie in Like what is a transaction, if you don't know that you've never done any serious database development. If they don't cover it in their answer then but they got it right, ask them when they would use it. Pointer manipulation, unions, structs, pre and post decrement are good ones for C. If you haven't done these and can't describe situations where they've come in handy or that they've caused problems then they've turned up after reading the "Idiots guide to C" In other words your tests should be specific to your environment.

michael_rempel
michael_rempel

The further a DBA wants to run from triggers the better I like it. Of course that presumes Deny all standard SQL to start with, and Stored Procs are the only access method allowed. Funny how T-SQL that doesnt need to recompile runs fast, has few if any bugs or lock contentions, and zero possibility of SQL injection attacks. It may be a more rigid design, but templated developement using a tool like ERWin makes this sort of thing maintainable, auditable, predictable and fast. The key to avoiding lock contentions is simple, use a standard join path to navigate through the database, and always use that same path whenever possible. It may look ugly, but a few slow ugly queries in the system, esp if they are compiled and maintained in a stored proc are well worth it. People often say that thousands of tables and tens of thousands of stored procs will make this kind of thing impossible. On the other hand I have done it successfully a few times over, and it can be done. For example I did a thing for US nation wide real estate a few years back that has zero lock contentions, and zero recompiles in the whole system. It has audits, security, and lots of esoteric niceties in it. My point being look for developers with both creative energies, and a fierce commitment to investigating and using best practices. A developer who hates innovation for the sake of recreation, but loves it for the sake of application to the problem is a rare and valuable treasure. If he takes specifications seriously and wants to document when and where use cases are implemented, has a passion for reading, understanding, questioning, and validating those use cases, then putting the exact specifications into his code chances are he wont be a developer much longer, that guy is on his way to becoming an architect. Michael Rempel

Tony Hopkinson
Tony Hopkinson

I was asking for what people knew about optimisation, the ones who said not a lot and didn't BS me, did understand indexes, using joins instead of sub queries etc. They just thought that was common sense. It's amazing how many times you see massive levels of nested triggers and things like calling mail directly from them not tied to "er it's a bit slow isn't it".

stickler
stickler

Well in Kentucky it's someone you marry...

Tony Hopkinson
Tony Hopkinson

particularly in multi user scenarios, if I was limited to one question, it would be What is a transaction ? For senior developer, what is transisolation.

chapman.tim
chapman.tim

So...your question is the only one that should be asked a potential db developer?

Dr Dij
Dr Dij

sure, you should weed out people who don't have a clue so you don't spend money or waste time figuring out if they fit. But there are commercial IT testing places, like brainbench. They do most of their biz with employers looking to verify skills. As an applicant you can also take their tests to see how you do in an area.

matthew.mark.ctr
matthew.mark.ctr

In addition, they must understand how to set up a relational database. And this is not only for architects. Anyone doing this work must understand basic design strategies. In smaller companies, you will find yourself as the architect, dba and developer. They must understand why they might consider using an index and what to watch out for in over use of indexes. I've seen this happen a lot, where data manipulation is made to tables without thought to unique values and proper indexing. This alone can bring your database to a halt. Basically, columns you join on or use in your 'where' clause, generally, should be indexed. It's through using indexes one sees the value!!!!

chapman.tim
chapman.tim

The article you're commenting about is regarding questions to ask potential database developers, not potential DBAs (that will likely be my next article). Also, if you notice at the end of this article, I mention that if the candidate performs well I will ask them to take my programming test, which is essentially what you are talking about here.

Tony Hopkinson
Tony Hopkinson

Backup and recovery strategies Strategies for performance optimistation both in SQL and in terms of the low level file organisation. Security considerations. Most imporantly you can't BS them without exposing yourself badly. Communication wise I'll help people out at interviews by restating the question, if I think I've communicated badly. I don't expect jargon and exact syntax, but I do expect the concepts and they should be able to get across their understanding with examples, if nothing else. To be honest, I'd rather have someone less techy and more talky. The key thing when looking for a dba is to distinguish between someone who's familiar with SQl and some one who can design and maintain a database. It's amazing how many sql 'experts' there are out there who write subqueries instead of joins, like two pass sorts, think unions are bad things to join that will annoy management ans alias is a tv show with a nice looking bint in it.

chapman.tim
chapman.tim

This article was only in regards to the sit-down portion of the interview. If you design a test for them (which you definetly should), incorporate the questions you mentioned above into that portion. I agree with you...NEVER hire someone just based on asking them technical questions.

stickler
stickler

Sorry about that, but considering some of the conversations I've had recently, you have to wonder...

JohnnySacks
JohnnySacks

Excellent questions but should add a couple others: Any developer above entry level should be able to handle cursors blindfolded. What code elements are required to utilize a generic boiler plate cursor loop? In a stored procedure based back end, how would you provide the identity of an inserted row to the front end or mid tier application? Any complicated application will require the use of temp tables. What are temp tables, why are they used, how are they created? What is a transaction? Front end developers managing transactions is a recipe for disaster. I'm maintaining a GUI app that inserts into serveral tables on one button click written by a guy who must have read the 'Learn T-SQL In 15 Minutes' book. Logic goes something like this: 1. Insert table 1, get identity 2. Insert table 2, get identity If fails, delete table 1 3. Insert table 3, get identity If fails, delete table 2 then delete table 1 ... repeat and expand as needed Referential integrity is important as a concept but would most likely be something set up at the architecture level that a developer just needs to know is there.

dfair
dfair

I've got to disagree about cursors and temp tables. I'll give you that complicated applications might need temp tables, but most likely derived tables will work (they are faster). Cursors though, are a different story. If someone knew off the top of their head all the syntax of declaring and using a cursor, I actually wouldn't hire them! Cursors should never be used except as a last resort. And even then, there is probably a better way.

Tony Hopkinson
Tony Hopkinson

A lot of places don't have DBAs. Many software houses don't want to tie them themselves to stored procs which they see as proprietary. Personally I think you should tie the integrity of the data into the DBMS, it's what they were invented for in the first place. The facility to do that though relies on specific architectural or business constraints. I've worn both hats, database and application developer for a long time now, given a free reign I prefer to put the functionality in the 'best' place, I'm not always given that option though. I have seen your example many times. One of my favourites was . Load up all details lines into memory Allow user to add, chnage and delete them inside the app. Delete all existing detail lines from the db Insert new ones No transaction round the delete and insert. Help desk, 'I got an error adding a new line and now all my data has gone' No sh** Sherlock :D It's a falling down point in this test, enough SQL knowledge to write a query is not enough to make you a database developer or a database application developer. I'd rather have someone who struggles with a Having clause but understands referential integrity and transactions any day.