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: Intro

Question:

You issue the following query against the dbo.Customers table:

SELECT * FROM dbo.Customers;

The table has a clustered index on the CustomerID column.

When you have not specified an ORDER BY clause, which order will the rows be returned in?

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

Question:

A developer in your team has observed that your database servers are very I/O bound. He thinks the I/O is simply too slow.

What he’s suggesting is that you replace all use of temporary tables with table variables. His argument is that they are memory-based instead of disk-based and that this should help to reduce the I/O load. Your applications make heavy use of temporary tables, and most have large numbers of rows.

2025-11-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: 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