Data Management

SQL Server database developer interview questions and answers

Development hiring managers and potential interviewees may find these open-ended SQL Server proficiency interview Q&As useful.

The first installment of this TechRepublic series focused on C# developer interview questions. It generated a lively discussion on the merits of such questions and the different approaches to measuring technical aptitude during an interview process.

This week we take a look at SQL Server. These questions (which all apply to SQL Server 2008) are not meant to be the only way to ascertain a candidate's technical expertise during an interview, but rather one piece of the puzzle that may spawn more thorough discussions. The depth of the questions may vary according to the level of the open position; for instance, you would expect more from a senior level candidate than from a junior level candidate.

Note: This content is also available as a downloadable PDF.

What are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What are DBCC commands?

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases -- maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC -- Check disk allocation consistency.

DBCC OPENTRAN -- Display information about recent transactions.

DBCC HELP -- Display Help for DBCC commands.

What is the difference between truncate and delete?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data -- you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)

AS

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned -- often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

The list of possible questions is endless. I am sure these questions will spawn debate and discussion.

Keep your engineering skills up to date by signing up for TechRepublic's free Software Engineer newsletter, delivered each Tuesday.

About

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

14 comments
minham
minham

i have applied for the post of Scientific Officer (IT) (Database Developer). 

Test will be MCQs based. Give me the suggestions how to prepare?

NishantSonuley
NishantSonuley

Hi, 

I found these questions and answers very informative. I am really thankful for this help.

JoydipSql
JoydipSql

I am on the horns of dilemma as to find no answer that a faculty must know the the development(he or she must undergo software development).Without which the person does not consider to be a good faculty.It is true.kindly help.

Joydip Roychowdhury(An IT Professional) 

dlovep
dlovep

Developer aren't suppose to know every detail specification of the system they use, right ? you need to know types of variables, but do you need to know the exact different between a single, a double and a decimal ? (not in bytes but in number )

jneville.work
jneville.work

IMHO very few of these questions would help you recruit the best candidate for the role. When I've sat on an interview panel as the technical representative (along with someone from HR and someone from management) I've always concentrated on the "why" of the technology, above the "what". A candidate with a good memory will be able to recite BOL entries for CTE, View, DBCC etc But only candidates who have actually *used* them would be able to tell you why you would want to; what problem was it that caused Microsoft to spend time building it into SQL Server. For example, after hearing the reply to the question "what is a CTE?" follow up with "under what circumstances would it be better to use a CTE over a subquery?" The answer to the second question would be more helpful in separating wheat from chaff.

MyopicOne
MyopicOne

I would expect any DBA that worked for me to know almost all of these, as I did. Even a developmental DBA.

Tony Hopkinson
Tony Hopkinson

will be on the DBMS, then I'd say if you could get someone who can answer all of them and understand what the answers mean, then tech wise you are on a winner. Thing is development jobs like that aren't that common. Usual devloper jobs SQL will get to 20%, if there's heavy use of ORMs it could get be zero. So anyone out there who wants to use this list as a measureof suitability for a role, shouldn't have to go beyond basic admin scripting in terms of programming, otherwise you'll be looking for somone who could get a pass on the previous C# quiz (or equivalent in an other programming language) and this one, and rest assured that's not common.

Pete6677
Pete6677

Some of these questions are not really appropriate for a developer interview. I would not expect a developer to know about DMVs and DBCC commands. In most shops, you would not want a developer performing DBCC tasks on a server. Developers are usually not involved with server administration and thus don't really need to know about the Agent, other than to be aware that it exists. The problem with relying on stock questions like this for a technical interview is that you will often weed out good people by grilling them on specific tasks that may not even pertain to the job. The people who remain in consideration will likely be those who spend their free time memorizing answers to interview questions, and these people generally make horrible employees. Good employees spend time performing useful work and thus don't have a lot of extra time to memorize trivia. Unless the particular skill or technology is truly necessary for the job you are interviewing for, don't have it on your list of requirements and don't expect all applicants to know it.

Tony Hopkinson
Tony Hopkinson

1.00 - ((1.0 / 3) * 3) != 0.00 Now you need to know don't you... If we wanted to make things simpler, then outlawing single and double would the way forward, not ignorance

Tony Hopkinson
Tony Hopkinson

A good few DBA's are "just" admins, the ones who can do the sql and design side are so data centric, you end up with a lovely schema that isn't even close to the back end needs of the application. I knew 'em but I've been a sql server dba as well. I'm way too out of date now. Database developer is a very rare animal, compared to DBA or Developer with a "bit" of SQL knowledge.

Tony Hopkinson
Tony Hopkinson

No deliberate attempt to get up someones nose and I can't even spot an unconscious one. GIve me a clue...

Editor's Picks