8 Essential SQL Server Interview Questions *

Toptal sourced essential questions that the best SQL Server developers and engineers can answer. Driven from our community, we encourage experts to submit questions and offer feedback.

Hire a Top SQL Server Developer Now
Toptal logois an exclusive network of the top freelance software developers, designers, finance experts, product managers, and project managers in the world. Top companies hire Toptal freelancers for their most important projects.

Interview Questions


What would be the SQL Server command to get the position of the letter ‘o’ in the name ‘John’ from an Employee table

View answer

Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name='John'

CHARINDEX is the SQL Server Equivalent of INSTR in Oracle.


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)
ORDER BY BillingYear;

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

View answer

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)
SELECT YEAR(BillingDate) AS BillingYear, COUNT(*) AS NumberOfInvoices
ORDER BY BillingYear

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.


How does SQL Server clear up orphaned connections?

View answer

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.

Apply to Join Toptal's Development Network

and enjoy reliable, steady, remote Freelance SQL Server Developer Jobs

Apply as a Freelancer

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

View answer

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.


Can records be deleted from a View in SQL Server?

View answer

It depends.

There are two types of Views in SQL Server. One is a “simple” view that contains data from one table only, and the other is a “complex” view that contains data from multiple tables.

A delete operation can be performed on records in a simple view, but not in a complex view.


What is the difference between the Merge Join and Lookup components in SSIS in SQL Server?

View answer

The use of Merge Join and Lookup components is based on the type of data relationship between two tables, i.e. a one-to-one relationship or a one-to-many relationship.

The Merge Join is used for joining data from two or more datasources and inserting the merged data into one denormalized target datasource. The Merge Join component takes two input sources and the merged data is transfered to a single target data output.

The Lookup component is used in SSIS where there is for one input record one matched record in the target source. Both the source and target satisfy the one-to-one relation.


When should a developer use the NOLOCK hint? What problems can happen when using this hint and how can these problems be addressed?

View answer

NOLOCK improves performance by not locking rows as they are read/processed by SQL. It should be used when accessing large tables and tables with data that rarely changes.

If too many locks are created, row-level locks can be escalated to block-level then table locks. Table locks especially on core tables will pause most processing on that database instance until that SQL operation completes.

NOLOCK allows the query to access both committed and uncommitted data and may return dirty data. Dirty data can lead to data integrity issues if a system is not coded to handle this situation.

Data integrity issues can be avoided by adding a TIMESTAMP column and referencing it in UPDATE and DELETE statements. This prevents data changes from occurring if a query reads dirty data. This requires more code to determine that an update or delete did not occur and then to take the appropriate action to recover. Comparing all original values of every column in a row on every UPDATE or DELETE may be performed instead of verifying the TIMESTAMP column has not changed.


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.

View answer

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:

FROM Invoices
GROUP BY CustomerId

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:

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 (
 FROM Invoices
 GROUP BY CustomerId

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

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.

Why Toptal

Tired of interviewing candidates? Not sure what to ask to get you a top hire?

Let Toptal find the best people for you.

Hire a Top SQL Server Developer Now

Our Exclusive Network of SQL Server Developers

Looking to land a job as a SQL Server Developer?

Let Toptal find the right job for you.

Apply as a SQL Server Developer

Job Opportunities From Our Network

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

Looking for SQL Server Developers?

Looking for SQL Server Developers? Check out Toptal’s SQL Server developers.

Peter Daniels

Freelance SQL Server Developer
United StatesToptal Member Since September 18, 2023

Peter is a seasoned data platform expert specializing in the Microsoft stack, including Azure. With skills ranging from data architecture and engineering to database administration and development, his 29 years of experience, multiple certifications, and passion for learning set him apart in addressing clients' diverse data requirements. Most recently, Peter helped a growing company migrate their on-premise data and AI estate into Azure as part of an ERP migration to Microsoft Dynamics 365.

Show More

Pedro Correia

Freelance SQL Server Developer
PortugalToptal Member Since January 25, 2021

Pedro has been working with Microsoft technologies since the early days of MS-DOS and GW-BASIC when he became fascinated with DBMS and used SQL Server 6.5 for the first time. During the last 20 years, he's implemented numerous web and Windows solutions in .NET, using SQL Server on the back end. Also, he taught several official Microsoft courses where he shared his expertise in the field. In one sentence, Pedro is eager to learn, inspired by challenges, and motivated to share.

Show More

Ivan Friščić

Freelance SQL Server Developer
CroatiaToptal Member Since January 14, 2022

Ivan is a database specialist with love for all things DB going back 10+ years. He brings a decade of experience with SQL Server (administration, development, BI, and ETL) and holds an MCSE certificate. He built an automated app for trading securities integrated with the stock exchange, a complete BI sales solution for a major pharmaceuticals company, and used SSIS to integrate bidirectional data between the company application and SAP that included staff, payroll, and organizational data.

Show More

Toptal Connects the Top 3% of Freelance Talent All Over The World.

Join the Toptal community.

Learn more