SQL Developer Interview Questions

Problem

A lot of online T-SQL interview questions are overly abstract and unrelated to practical applications. They frequently ignore how SQL is actually used in daily work in favor of concentrating on theoretical distinctions or basic syntax. This makes it challenging to assess a candidate’s ability to write effective, maintainable queries or solve actual problems. Check out these SQL developer interview questions.

Solution

The purpose of this article is to assess your technical expertise as well as your ability to solve real-world SQL Server development problems. We provide realistic scenarios that mimic the types of difficulties you might encounter in a professional setting rather than concentrating on discrete syntax questions. A brief schema description is included to provide context for each section, which is based on a different database domain. You will be asked how you would use Transact-SQL to approach or fix different issues.

In addition to evaluating your proficiency with SQL syntax, we also look at your ability to apply best practices, write effective code, and analyze edge cases. Feel free to talk about trade-offs, performance implications, and alternatives you would take into account in a real-world situation whenever appropriate.

Basic Querying and Data Filtering

Database Theme: CRM (Customer Relationship Management)

Tables:

• Customers (CustomerID, FirstName, LastName, Email, PhoneNumber, CreatedDate)
• Orders (OrderID, CustomerID, OrderDate, TotalAmount)

Question 1

You’re reviewing customer records in the CRM database and suspect that the same customer may have been entered multiple times. How would you identify duplicate customers based on FirstName, LastName, and Email?

Answer

--MSSQLTips.com
SELECT FirstName, LastName, Email, COUNT(*)
FROM Customers
GROUP BY FirstName, LastName, Email
HAVING COUNT(*) > 1;

Question 2

Due to a syncing issue with the web form, several customer entries have the same Email and PhoneNumber. You are asked to remove duplicates but retain one entry. How would you do that?

Answer

--MSSQLTips.com
WITH Duplicates AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Email, PhoneNumber ORDER BY CreatedDate) AS rn
  FROM Customers
)
DELETE FROM Duplicates WHERE rn > 1;

Question 3

A junior developer asks if DISTINCT is the same as GROUP BY.

--MSSQLTips.com
SELECT DISTINCT Email FROM Customers;
SELECT Email FROM Customers GROUP BY Email;

As shown below, both queries have the same execution plan.

In your opinion, what is the difference between these approaches?

Answer

It is true that the queries above give the same result and serve the same purpose; however, the GROUP BY clause is mainly used for aggregations, which cannot be done with distinct.

Question 4

You need to find customers who have never placed an order. How can you retrieve them?

Answer

--MSSQLTips.com
SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

Question 5

You are comparing the performance of filtering out customers who placed no orders using different methods. What’s the difference between NOT IN, NOT EXISTS, and LEFT JOIN … IS NULL?

Answer

• NOT EXISTS: Implemented as an anti semi join. It simply checks if a match exists and can short-circuit on the first one found. Efficient with indexes, and the subquery doesn’t always rely on statistics.
• NOT IN: Can fail if the subquery returns NULLs, since comparisons become unknown. Only safe when the column is guaranteed NOT NULL.
• LEFT JOIN … IS NULL: Performs an outer join and filters unmatched rows. Always uses join statistics and often processes more rows before applying the IS NULL filter, which can make it less efficient than NOT EXISTS.

Question 6

You tried WHERE Email <> NULL and it didn’t return results. What went wrong?

Answer

NULL cannot be compared using =, <>, etc. Use IS NULL or IS NOT NULL.

Question 7

You want to return the 5 most recent customers. How does TOP behave with and without ORDER BY?

Answer

When we use the ORDER BY clause, the query becomes deterministic.

--MSSQLTips.com
SELECT TOP 5 * FROM Customers ORDER BY CreatedDate DESC;

Without ordering, it only serves to limit the result set since the order is not guaranteed.

--MSSQLTips.com
SELECT TOP 5 * FROM Customers; 

Ranking and Window Functions

Database Theme: HR (Human Resources)

Tables:

• Employees (EmployeeID, Name, DepartmentID, Salary)
• Departments (DepartmentID, DepartmentName)

Question 8

You need to generate a report ranking employees by salary within each department. How do you do that?

Answer

--MSSQLTips.com
SELECT *, RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Question 9

You’re teaching a new hire the difference between ranking functions. How would you compare RANK(), DENSE_RANK(), and ROW_NUMBER() using salaries?

Answer

--MSSQLTips.com
SELECT Name, Salary,
  RANK() OVER(ORDER BY Salary DESC) AS R,
  DENSE_RANK() OVER(ORDER BY Salary DESC) AS DR,
  ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RN
FROM Employees;

Question 10

You are building a paginated employee directory. How do you return rows 11–20?

Answer

--MSSQLTips.com
SELECT *
FROM Employees
ORDER BY Name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Joins, CTEs, and Subqueries

Database Theme: E-Commerce

Tables:

• Products (ProductID, Name, CategoryID, Price)
• Categories (CategoryID, ParentID, CategoryName)

Question 11

You have a deeply nested query filtering products by expensive categories. When would a CTE be better than a subquery?

Answer

CTEs improve readability and are reusable across the same query. They can also support recursion.

Question 12

You need to return all subcategories under a root category. How would you use a recursive CTE?

Answer

--MSSQLTips.com
WITH CategoryCTE AS (
  SELECT CategoryID, ParentID, CategoryName FROM Categories WHERE ParentID IS NULL
  UNION ALL
  SELECT c.CategoryID, c.ParentID, c.CategoryName
  FROM Categories c
  JOIN CategoryCTE p ON c.ParentID = p.CategoryID
)
SELECT * FROM CategoryCTE;

Question 13

You’re optimizing a query returning all products in a certain category. When would you use JOIN over subqueries, or vice versa?

Answer

In general, a JOIN should be used to retrieve related data and subqueries for filtering or scalar values. Still, optimizing queries depends on several factors, such as indexes, which may prefer one of those two methods.

Question 14

You want to separate logic for price filtering and category filtering. Can you use multiple CTEs?

Answer

--MSSQLTips.com
WITH Expensive AS (
  SELECT * FROM Products WHERE Price > 100
), TopCategories AS (
  SELECT * FROM Categories WHERE ParentID IS NULL
)
SELECT e.* FROM Expensive e
JOIN TopCategories c ON e.CategoryID = c.CategoryID;

Query Optimization

Database Theme: Finance

Tables:

• Transactions (TransactionID, AccountID, Amount, CreatedAt)
• Accounts (AccountID, AccountType, IsActive)

Question 15

A junior developer asked you: What is the logical query execution order, and why is it important?

Answer

SQL processes: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Knowing this order helps you understand how the SQL engine compiles and reads your query. This can help with better query writing and optimizing them.

Question 16

You see SELECT * in a stored procedure pulling financial transactions. Why should this be avoided?

Answer

SELECT * queries may malfunction or produce unexpected results if the table schema changes, such as when columns are added or removed. Additionally, since it’s unclear which columns are being used, debugging becomes more difficult. Development is slowed down, and the likelihood of bugs is increased as queries become more difficult to read and comprehend. Clearly stating the required columns enhances readability, facilitates code maintenance, and provides better documentation for future reviewers or developers.

Question 17

Performance varies when passing different parameters. What is parameter sniffing?

Answer

SQL caches execution plans based on the first parameter passed to the stored procedure. This behavior may affect the performance of later queries when other parameters are passed.

Question 18

You want SQL Server to use indexes effectively. What is SARGability? Please provide an example.

Answer

A query is SARGable if it can use indexes. As an example, the following WHERE condition is SARGable:

--MSSQLTips.com
 WHERE Amount > 100

While using functions in filtering makes the query not SARGable:

--MSSQLTips.com
WHERE DATEPART(YEAR, CreatedAt) = 2024

Question 19

You analyze a slow query using an execution plan. What do you look for first?

Answer

1. Expensive operators.
2. Table/Index Scans vs. Index Seeks.
3. Actual vs. Estimated row count.

Temporary and In-Memory Objects

Database Theme: Logistics

Tables:

• Shipments (ShipmentID, CarrierID, Status, CreatedDate)
• Carriers (CarrierID, Name, IsActive)

Question 20

You’re writing a procedure to track shipments temporarily during processing. What is the difference between #temp tables and @table variables?

Answer

• Both are stored in tempdb.
• #temp tables: Have statistics, ACID compliant, better for larger sets.
• @table variables: No statistics, scoped to the batch/proc, weaker with transactions, best for small sets.

Question 21

You want to share a temporary table across multiple sessions for a monitoring script. What’s the difference between #Temp and ##Temp tables?

Answer

• #Temp: Visible only within the current session.
• ##Temp: Global, visible to all sessions.

Question 22

You are assigning the latest shipment status to a variable. Write two queries that achieve this using SET and SELECT. What is the difference between these methods?

Answer

SET is used for one variable at a time.

-- MSSQLTips.com
SET @Status = (SELECT TOP 1 Status FROM Shipments ORDER BY CreatedDate DESC);

While SELECT assigns multiple variables and it can be used to concatenate several values.

-- MSSQLTips.com
SELECT @Status = Status FROM Shipments WHERE ShipmentID = 123;

Indexing and Partitioning

Database Theme: Healthcare

Tables:

• Patients (PatientID, Name, Gender, Active)
• Visits (VisitID, PatientID, VisitDate, DepartmentID, DiagnosisCode)

Question 23

You are improving the performance of queries that retrieve visit history. What’s the difference between included and key columns in non-clustered indexes?

Answer

• Key columns are used to sort/search the index.
• Included columns are added to the index leaf for covering queries without affecting sort.

Question 24

You want to create an index on active patients only. What type of index should you use?

Answer

Filtered indexes should be used because they reduce index size and improve performance when most queries filter on a condition.

--MSSQLtips.com
CREATE NONCLUSTERED INDEX IX_ActivePatients ON Patients(Name)
WHERE Active = 1;

Question 25

You want to analyze visits in a specific year stored on a particular partition. How do you limit a query to a filegroup or partition?

Answer

Use partitioning on VisitDate, and include the partition key in the WHERE clause:

--MSSQLTips.com
SELECT * FROM Visits WHERE VisitDate >= '2024-01-01' AND VisitDate < '2025-01-01';

Or, use the partition function. For example, assume there is a partition function named pfVisitsByYear on the VisitDate column.

--MSSQLTips.com
SELECT *
FROM Visits
WHERE $PARTITION.pfVisitsByYear(VisitDate) = $PARTITION.pfVisitsByYear('2024-01-01');

Question 26

You’re designing a BI-friendly table with millions of records. What is a columnstore index, and why use it?

Answer

A columnstore index stores data by columns instead of rows, making it highly efficient for analytics and reporting queries that scan large amounts of data but only need a few columns. Also, it is more performant on aggregations and allows better compression.

Question 27

You created an index for a specific query. With time, you note that the query performance is degrading. What may degrade an index performance?

Answer

The most common reason is index fragmentation, which occurs when data modifications (INSERT, UPDATE, DELETE) cause the index pages to become logically out of order or partially filled.

Error Handling

Database Theme: Retail

Tables:

• Products (ProductID, Name, Price)
• Inventory (ProductID, Quantity)

Question 28

You want to gracefully handle errors during inventory updates. We need to decrease the quantity by 1 for a specific product. In case of an error, we should insert a record into an ErrorLog table (Message, Date). How would you use TRY…CATCH?

Answer

--MSSQLTips.com
BEGIN TRY
  UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductID = @id;
END TRY
BEGIN CATCH
  INSERT INTO ErrorLog VALUES (ERROR_MESSAGE(), GETDATE());
END CATCH

Functions and Stored Procedures

Database Theme: Education

Tables:

• Students (StudentID, Name, GPA)
• Enrollments (EnrollmentID, StudentID, CourseID)

Question 29

Your colleague created a scalar UDF to calculate honors level, but queries slowed down. Why can scalar UDFs hurt performance, and how are inlined TVFs better? Please provide a code.

--MSSQLTips.com
CREATE FUNCTION GetHonor(@GPA FLOAT)
RETURNS VARCHAR(20)
AS
BEGIN
  RETURN CASE WHEN @GPA >= 3.5 THEN 'Honors' ELSE 'Regular' END;
END

Answer

Scalar UDFs are executed row-by-row and don’t optimize with the main query (improved with UDF inlining in SQL 2019 with some limitations). Inlined TVFs are expanded in the plan, can use indexes, and perform better.

--MSSQLTips.com
CREATE FUNCTION dbo.fn_StudentHonor()
RETURNS TABLE
AS
RETURN (
  SELECT StudentID, Name,
         CASE WHEN GPA >= 3.5 THEN 'Honors' ELSE 'Regular' END AS Status
  FROM Students
);

Question 30

How would you create a stored procedure that accepts a @CourseID parameter and returns all students enrolled in that course, including their StudentID, Name, and GPA?

Answer

--MSSQLTips.com
CREATE PROCEDURE GetStudentsByCourse
  @CourseID INT
AS
BEGIN
  SELECT s.StudentID, s.Name, s.GPA
  FROM Students s
  INNER JOIN Enrollments e ON s.StudentID = e.StudentID
  WHERE e.CourseID = @CourseID;
END

Next Steps

• 50 SSIS Interview Questions to Prepare for Your Next Interview
• What Does an SQL Developer Do? Role, Salary, and Skills

Hadi Fadlallah

Hadi Fadlallah is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He’s one of Stackoverflow.com’s top ETL and SQL Server Integration Services contributors. Also, he is a university lecturer and writes scientific and technical articles on several blogs. On the academic level, he holds a Ph.D. degree in data science focusing on context-aware big data quality and two master’s degrees in computer science and business computing.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2025 | Author of the Year Contender – 2023 | Rookie of the Year – 2022