Sql-Interview

SQL Interview: 69 When tempdb gets used

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 monitoring a SQL Server and you notice that tempdb is being heavily accessed. You know that your applications make very little use of temporary tables.

Can you give examples of other things that might be using tempdb.

2025-08-08

SQL Interview: 68 Converting to local time zones

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 working with an Azure SQL Database. When you display dates and times, you notice that they do not match your local time zone of CEST.

Can you change the server settings to Central European Standard Time (CEST)?

2025-08-04

SQL Interview: 67 Backing up transaction log after DB failure

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:

A storage failure occurs and during recovery, a data file from your database was lost.

Transaction log backups are taken every 5 minutes.

Periodically, when data is being loaded into the database, Bulk Logged mode is used. At the time of the failure, the database was still in bulk logged mode.

2025-07-31

SQL Interview: 66 Using FORCE ORDER

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 code and in one query, you find the following:

OPTION (FORCE ORDER)

What is the point of this code, and is it likely to be helpful?

Answer:

FORCE ORDER tells SQL Server to join the tables that are involved in a query, in the order that they are written in the query.

2025-07-27

SQL Interview: 65 Auditing user actions in T-SQL

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

Question:

You have code in stored procedures that records which users have called the procedures. The stored procedures are defined as:

WITH EXECUTE AS OWNER

You find that when you log the caller of the procedure, you get the owner of the procedure, rather than the caller.

2025-07-23

SQL Interview: 64 Disabling and reenabling constraints

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 have a table called Sales.Transactions that has an associated foreign key constraint. The constraint checks that CustomerID values correctly reference the CustomerID primary key of the Sales.Customers table.

You need to bulk load some transaction data and decide to disable the constraint during the load, hoping it will increase performance of the load.

2025-07-19

SQL Interview: 63 Keys contained in certificates

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

Question:

Public key encryption is based on combinations of private keys and public keys.

When you install a certificate on a SQL Server, to be used for client connection encryption, which key or keys are contained in the certificate?

2025-07-15

SQL Interview: 62 Selecting rows for a date

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 have a table of transactions. You need to select all the transactions for a particular date, based on the TransactionDateTime column. It holds datetime data type value. The date is stored in the @RequiredDate variable.

2025-07-11

SQL Interview: 61 Effects of AUTO_CLOSE

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 reviewing database settings for a server.

You notice that one database has AUTO_CLOSE configured. Your model database does not have it enabled.

What is the most likely cause for that setting to be enabled, and what effect does enabling it have?

2025-07-07

SQL Interview: 60 Rowversion vs Timestamp

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 code from an application.

You notice in the definition of some tables, the timestamp data type has been used. In other tables, the rowversion data type has been used for similar purposes.

2025-07-03