4 Essential SQL Server Interview Questions*

Is the following a valid SQL query? Why or why not?

SELECT TOP 5 YEAR(BillingDate) AS BillingYear, COUNT(*) AS NumberOfInvoices
FROM Invoices
WHERE CustomerId = 42
GROUP BY YEAR(BillingDate)
HAVING COUNT(*) > 1
ORDER BY BillingYear;

As part of your answer, list the phrases of this statement in the order that SQL Server logically processes them.

One might think that this SQL statement is invalid since it uses the alias BillingYear in the ORDER BY phrase. Even though BillingYear cannot be used in the WHERE or GROUP BY phrases, it can be used in the ORDER BY phrase.

Listing the phrases in the logical order in which they are processed helps make clear why this is so:

FROM Invoices
WHERE CustomerId = 42
GROUP BY YEAR(BillingDate)
HAVING COUNT(*) > 1
SELECT YEAR(BillingDate) AS BillingYear, COUNT(*) AS NumberOfInvoices
ORDER BY BillingYear
TOP 5

Although the reordered statement is not valid T-SQL syntax, it is critical for candidates to be able to reorder the statement in their heads so they can work out any problems they are having when composing complex queries. Most T-SQL programmers never need to explicitly memorize this processing order. There is a logic to it that becomes very familiar with experience, so this question is designed to gauge that experience in a candidate.

Fields marked with an asterisk (*) are required
Comment submitted succesfully. Thank you.
We are going to review the comment and get back to you as soon as possible.

Considering the database schema displayed in the diagram below, write a SQL query which lists all invoices billed to the customer who has the highest number of invoices. Note that there may be more than one customer tied for most invoices, in which case the invoices for all of them should be listed.

The point here is to see if the candidate can break down a complex query into simpler parts. The first step is to find out who the customer with the highest number of invoices is, taking into account there may be a tie:

SELECT TOP 1 WITH TIES CustomerId
FROM Invoices
GROUP BY CustomerId
ORDER BY COUNT(*) DESC;

Now that we know who the “winner(s)” are, we can query for that customer(s) orders. In order to query the orders of a list of customers we know by ID, we can use the IN operator:

SELECT *
FROM Invoices
WHERE CustomerId IN (…);

So now inserting the first query as a subquery into the second one, we get:

SELECT * FROM Invoices
WHERE CustomerId IN (
 SELECT TOP 1 WITH TIES CustomerId
 FROM Invoices
 GROUP BY CustomerId
 ORDER BY COUNT(*) DESC
);

Extremely complicated queries can be broken down into subqueries in this fashion, then used as building blocks to produce the final query.

Fields marked with an asterisk (*) are required
Comment submitted succesfully. Thank you.
We are going to review the comment and get back to you as soon as possible.

How does SQL Server clear up orphaned connections?

It doesn’t. SQL Server never terminates a connection unless explicitly told to do so (i.e., by a user, by a KILL command, or by the operating system informing it that the network connection has been disconnected).

The length of time it takes for the operating system to kill a network connection can vary greatly. It largely depends on the net-lib and network protocol used.

Typically, named pipe connections over NetBEUI will time out quickly. Named pipes over IP will time out almost as quickly as well. If you’re using TCP/IP sockets, those sessions don’t time out at all by default.

More information on troubleshooting orphaned connections in SQL Server is available here.

Fields marked with an asterisk (*) are required
Comment submitted succesfully. Thank you.
We are going to review the comment and get back to you as soon as possible.

Find top SQL Server talent today. Toptal can match you with the best developers to finish your project.

Hire Toptal’s SQL Server Developers

How can SQL Server be configured to listen on other Net Libraries? How can you tell what Net Libraries are being used?

For SQL 7.0 and above, the “Server Network Utility” installed in the SQL Server program group tells you what Net Libraries are installed and allows you to configure new ones.

For SQL 6.5 and earlier, run SQL setup and choose Configure Server and then Network Support to access this functionality.

For all versions of SQL Server, you can check the SQL errorlog for “listening on” lines that tell you what Net Libraries are being used.

Fields marked with an asterisk (*) are required
Comment submitted succesfully. Thank you.
We are going to review the comment and get back to you as soon as possible.
* There is more to interviewing than tricky technical questions, so these are intended merely as a guide. Not every “A” candidate worth hiring will be able to answer them all, nor does answering them all guarantee an “A” candidate. At the end of the day, hiring remains an art, a science — and a lot of work.
Submit an interview question
Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.
All fields are required
Thanks for submitting your question.
Our editorial staff will review it shortly. Please note that submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.
Max Stepanskiy
United States
Max is a top architect, engineer, and full stack developer with extensive experience working in various industries. He has a proven ability to define and develop efficient, performant, scalable, and fault-tolerant solutions
Antonio Bello
Poland
Antonio is an experienced, self-motivated, results-driven, flexible freelance professional who specializes in turning business ideas into software solutions. He covers the entire software development life cycle, from concept to final product, using the best technologies.
Luis Angel Chamosa Aparicio
Mexico
As a senior software engineer, Luis has worked on all project phases, from gathering functional and technical requirements to designing and developing solutions. He has managed IT teams using Scrum. He can work efficiently with people under his command, as a contributing member of a team, or as a solo developer. He has 15 years of professional experience focused on innovating and creating custom applications.