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: Administration Level: Medium

Question:

You are trying to truncate a table but the command is failing.

Can you give some examples of what can cause a truncate command on a table to fail?

Answer:

One common reason is that the table is referenced by a FOREIGN KEY constraint. (But you can truncate a table that has a foreign key that references itself).

2025-10-31

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 have two SQL Server instances. Each is on a separate virtual machine. Both virtual machines are on the same physical host.

You want to be able to create a transaction that spans both instances of SQL Server. What else needs to be installed?

2025-10-27

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:

Several trace flags are in use on a server you manage.

You are concerned that a behavior that you are seeing in a session is being caused by an enabled trace flag. How can you determine which trace flags are enabled for a session?

2025-10-23

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:

What are mirrored backups?

Do they increase or decrease the reliability of backups?

Answer:

Mirrored backups cause the same backup data to be written to multiple locations.

They decrease the reliability of backups, because if writing to any location fails, all backups fail.

2025-10-19

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:

What are striped backups and why would you use them?

What do they need to be effective?

Answer:

Striped backups cause stripes of the backup to be written to multiple files. This is done to increase performance.

2025-10-15

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:

You are managing a SQL Server 2022 server and have enabled Instant File Initialization (IFI).

Which files will this apply to?

Data (MDF and NDF) files only
Transaction log files only
Both data and transaction log files

Answer:

2025-10-11

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;