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

Question:

You are using Object Explorer in SQL Server Management Studio (SSMS) to browse the contents of a SQL Server. In the System Databases folder, there is a database named model. What is its purpose?

2025-12-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: Development Level: Medium

Question:

Does a stored procedure automatically provide a transaction i.e., if a statement within a stored procedure fails, are all previous data modification statements rolled back?

Answer:

No, a stored procedure does not automatically provide an implicit transaction.

2025-12-30

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:

When checkpoint writes occur, is only committed data written, or is uncommitted data written?

If only committed data, explain why.

If both, explain why.

Answer:

Checkpoint is a background process that:

• Flushes dirty pages (pages in memory that have been modified since they were read from disk) to disk.
• Records a log sequence number (LSN) in the transaction log that marks the point at which the database is known to be consistent on disk.

This means that SQL Server doesn’t flush only committed transactions — it flushes all dirty pages, whether the transactions that modified them are committed or not.

2025-12-28

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 know that the master database in SQL Server is now separate to the resource database (mssqlsystemresource).

Can you list three types of instance-level metadata that still live in the master database?

Answer:

2025-12-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: Medium

Question:

If you have successfully created a SQL Server database backup, what could cause you to not be able to restore it, on the same server or another server ?

Answer:

A successful SQL Server backup does not guarantee that it can later be restored successfully. A few important points explain why:

2025-12-26

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 writing a query that involves inner joins across five tables. Does the order of the tables within the query affect the query plan?

Answer:

No. SQL Server determines the best order for you to join the tables in.

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

Most information about each database is stored within the database.

Can you list three types of database information that live in the master database?

Answer:

Pick three of the following:

• Database IDs, names, creation date
• Compatibility level
• Collation
• Recovery model
• Status (ONLINE, RESTORING, SUSPECT)
• File locations and size metadata (from sys.master_files)
• Ownership (database owner)
• Availability Group membership
• Replication information
• Database GUIDs, resource IDs

2025-12-20

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:

If you are SELECTing from a table and the ORDER BY clause in your query is in the same order as a non-clustered index, will the non-clustered index be used?

Answer:

It might or it might not.

2025-12-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: Development Level: Intro

Question:

A developer in your team has suggested that better formatted and laid-out SQL code will perform better?

Is this true?

Answer:

Because the optimiser reduces queries down to a format that it can work with the format of your code doesn’t make a difference. Better formatted code is more readable and maintainable though, so it’s always a good idea.

2025-12-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 have created a table named dbo.Customers. The column CustomerID has been declared as

INT NOT NULL IDENTITY(1, 1)

No contraints are defined on the table.

Can the CustomerID contain a duplicate value? If so, how?

Answer:

2025-12-15