Sql-Interview

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You are reviewing your development standards and notice that there is a warning about making sure you don’t nest views.

Why would this be an issue?

Answer:

Nested views hide complexity, and worse, the SQL Server optimizer might not be able to eliminate all the tables from the query that it could otherwise have done. This can lead to serious performance issues that could have been avoided.

2025-10-07

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

Why is it advisable to avoid storing user objects in the PRIMARY filegroup, and keeping it small?

Answer:

You want the PRIMARY filegroup as small as possible and well-backed up. If you cannot restore the PRIMARY filegroup, you cannot restore any other filegroups.

2025-10-03

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You regularly execute the following query:

SELECT ProductID,
       UnitPrice / OuterPrice AS PriceRatio
FROM dbo.Products;

When OuterPrice is zero, an error is returned. What is the simplest way to change the query to return NULL for PriceRatio if OuterPrice is zero, without wrapping the expression in a CASE statement?

2025-09-29

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You execute the following code:

DROP TABLE IF EXISTS dbo.Table1;
GO

CREATE TABLE dbo.Table1
(
    Column1 int NULL
);
GO

INSERT dbo.Table1 (Column1)
VALUES (1), (NULL), (NULL), (2);
GO

-- Query 1
SELECT COUNT(*), COUNT(Column1)
FROM dbo.Table1;

-- Query 2
SELECT COUNT(*), COUNT(Column1)
FROM dbo.Table1
WHERE Column1 = NULL;

What values will be returned for each query?

2025-09-25

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You are reviewing T-SQL transactional code that does not appear to be working as expected. The following code is being executed:

BEGIN TRAN;

UPDATE Table1 SET Column1 = 12 WHERE Column2 = 14;
UPDATE Table2 SET Column3 = 15 WHERE Column4 = 99;

COMMIT TRAN;

The second update (for Table2) is failing with a foreign key violation, but the update to Table1 is not being rolled back.

2025-09-21

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

You are reviewing a database design from one of your developers.

He normally works with PostgreSQL and in that language, he uses the text data type for all strings.

When creating the database in SQL Server, he has used the varchar(max) data type for all strings.

2025-09-17

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You use a tool that does static code analysis of your T-SQL code.

It identifies the following predicate as an issue:

WHERE EXISTS 
(
    SELECT * 
    FROM dbo.Products AS p 
    WHERE p.ProductID = c.ProductID
)

Is this an issue?

2025-09-13

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

A DBA at your site tells you that you need to make sure your predicates are sargable.

What is SARGability?

Assume a table has a column called OrderDate and it is of date data type. There is a single column index on the column. Can you give an example of a non-sargable predicate that uses it?

2025-09-09

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

Are there situations where temporary tables will perform better than table variables?

What could cause that?

Answer:

Temporary tables have two properties that are not available to table variables:

Indexes - with table variables, you can have primary key constraints and unique key constraints, but not other indexes
Statistics - temporary tables support rich statistics, similar to tables where table variables have very limited statistics, mostly just cardinality.

2025-09-05

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

In the following WHERE clause, both AColumn and AnotherColumn are NULL.