Top 15 SQL Server DBA Interview Questions

In a world of data, a functioning database is a prerogative for this world to go around. The people who ensure that are called database administrators (DBAs). In doing so, they use Relational Database Management Systems (RDBMSs). With SQL Server being among the most popular RDMSs, its importance in database management becomes evident.

This importance is reflected in SQL Server DBAs' being among the most sought-after data experts. If you want to become one, this article will help you prepare for the job interview.

I will talk about SQL Server and how DBAs use it to administer databases. Then, I’ll explore some SQL Server DBA interview questions that will help you land such an important data role.

Importance of SQL Server in Database Management

I have a feeling that I’ve used this picture several times already in our blog articles. This time, I’m using it to show that SQL Server is among the three most popular database systems.

And it’s been in the top three for more than ten years.

This lasting popularity is due to SQL Server’s combination of performance, scalability, security, and integration capabilities.

Here’s the overview of the key aspects that underscore the importance of SQL Server in database management.

The Role and Responsibilities of a SQL Server DBA

SQL Server being such a cool, but also sophisticated and complex tool, requires someone skillful to use it in database administration. In the broadest sense, this is the role of a SQL Server DBA: use SQL Server to manage and administer databases.

This is something you already know, I gather. What you don’t know is what a DBA actually does with SQL Server. Quite a lot, it seems.

Fundamental SQL Server DBA Interview Questions

The interview questions for the SQL Server DBA position include both coding and non-coding questions. Let’s have a look at five fundamental ones.

1. Non-Coding Question: Explain the Different Types of SQL Server Backups.

In SQL Server, there are eight types of backups:

1. Full Backup
2. Differential Backup
3. Partial Backup
4. Tail-Log Backup
5. Copy-Only Backup
6. Transaction Log Backup

You can define them the following way and mention these characteristics.

2. Non-Coding Question: DELETE and TRUNCATE

The question every SQL Server DBA must be able to answer is this one by Southwest Airlines and British Airways.

Link to the question: https://platform.stratascratch.com/technical/2084-delete-and-truncate

As a DBA, you’ll use DELETE and TRUNCATE frequently. Do you know the difference between them?

TRUNCATE is a Data Definition Language (DDL). This means it is more focused on a schema or table structure.

DELETE is a Data Manipulation Language (DML) that is more focused on manipulating (deleting) the entries of a schema or table.

In practice, the difference is that TRUNCATE will remove all table rows, while DELETE can be used to remove specific table rows with the inclusion of the WHERE clause.

TRUNCATE is faster than DELETE because it doesn't log entries for each deleted row in the transaction log.

3. Non-Coding Question: Denormalized Database

This SQL Server DBA interview question by Spotify and Stitch Fix wants you to explain what a denormalized database is and its advantages.

Link to the question: https://platform.stratascratch.com/technical/2404-denormalized-database

Denormalization is the process of combining data from several tables into a single table that can be searched rapidly. It focuses on achieving quicker query execution by creating redundancy. Because denormalization data is incorporated into the same database, the number of tables used to hold that data grows.

Denormalization causes some memory waste and does preserve data integrity. It is utilized when joins are too complex, and queries are run often on the tables.

It has two main advantages:

1. Performance is improved because retrieving data is faster.
2. Queries performed are simpler because fewer joins are made.

4. Coding Question: Primary Key Violation

One of the fundamental tasks of a DBA is implementing and checking constraints. The primary key is one such constraint, and before importing data into the database, you have to check if it is violating the primary key constraint.

This SQL Server DBA interview question from Amazon and Apple gives you this task.

Link to the question: https://platform.stratascratch.com/coding/2107-primary-key-violation?code_type=5

The question provides one table named dim_customer.

We want to list the customer IDs that violate the primary key constraint and also the number of times these IDs appear in the data.

The solution is first to list the customer IDs and use COUNT(*) to count how many times they appear in the table.

The data is then grouped by the same IDs. Finally, the data needs to be filtered using the HAVING clause. This will keep only those IDs that appear more than once, i.e., those that violate the primary key constraint.

SELECT cust_id,
       COUNT(*) AS n_occurrences
FROM dim_customer
GROUP BY cust_id
HAVING COUNT(*) > 1;

The code will output three customer IDs.

5. Coding Question: Unique Employee Logins

This Meta/Facebook interview question tests some basic SQL querying every SQL Server DBA needs to know.

Link to the question: https://platform.stratascratch.com/coding/2156-unique-employee-logins?code_type=5

Imagine that you’re investigating some database performance issues. One of the steps might be to find out which users accessed the database in a particular period of time.

You have a table worker_logins to work with.

How do you solve this task? You’re looking for unique worker IDs, so using the DISTINCT clause is necessary.

After that, you should use WHERE to return the data where the login timestamp is between December 13 and December 19, 2021, inclusive. For the filtering to work, you additionally need to convert the login_timestamp column to date using the CONVERT() or CAST() function.

SELECT DISTINCT worker_id
FROM worker_logins
WHERE CAST(login_timestamp AS DATE) BETWEEN '2021-12-13' AND '2021-12-19';

Advanced SQL Server Administration Questions

Interviewing for the more senior DBA positions implies you can answer the advanced SQL Server DBA interview questions.

Here are some examples of the non-coding and coding questions you might encounter.

6. Non-Coding Question: How Do You Manage Large Databases and Ensure Their Performance?

Managing and ensuring the performance of large databases requires a strategic approach. In your answer, you should lay it out in general terms.

Here are the strategies your answer should include.

1. Database Design & Architecture
2. Performance Tuning
3. Resource Management
4. Scalability & High Availability
5. Monitoring & Proactive Management
6. Data Management

These strategies involve the following activities.

7. Non-Coding Question: Explain How to Configure and Optimize SQL Server Instance Settings.

As an SQL Server DBA, one of your main tasks will be to configure and optimize SQL Server instance settings. Of course, you’re expected to explain how you’d do it to the interviewer.

Here’s what steps your answer should include.

1. Memory Management
2. Processor Utilization
3. tempdb Configuration
4. Networking
5. I/O Configuration
6. Query Execution Settings
7. Monitoring & Alerts
8. Maintenance Plans

8. Non-Coding Question: Describe How You Would Handle a Database Migration or Upgrade.

Migrating and upgrading databases is yet another task that SQL Server DBAs should perform. You should structure your approach around these steps.

1. Planning & Assessment
2. Preparation
3. Test Migration
4. Execution
5. Validation & Optimization
6. Go-Live & Support
7. Backup & Contingency Plan

Here’s what each step encompasses.

9. Coding Question: Rules To Determine Grades

This SQL Server DBA interview question by the City of Los Angeles tests your coding skills in the context of data management.

Link to the question: https://platform.stratascratch.com/coding/9700-rules-to-determine-grades?code_type=5

Let’s solve it, but also add something so it becomes more relatable for DBAs. Imagine you realized there’s a need to create a new dictionary table in the database. Let’s call this new table grade_dictionary. It will contain the grade, its highest and lowest scores, and the rule description.

How would you use the table los_angeles_restaurant_health_inspections to achieve this?

The basis of your query is a regular SELECT statement. It uses the MIN() and MAX() aggregate functions to determine each grade's lowest and highest scores.

Then, the CONCAT() function concatenates those scores with the string values so the grade rule is in the desired format.

The output is grouped and ordered by grade.

SELECT grade, 
       MIN(score) AS min_score, 
       MAX(score) AS max_score,
       CONCAT('Score > ', MIN(score) -1,' AND',' Score <= ', MAX(score),' => Grade = ', grade) AS grade_rule
FROM los_angeles_restaurant_health_inspections
GROUP BY grade
ORDER BY grade ASC; 

The code returns the grades, their minimum and maximum scores, and grade rule.

To create a table from this output, you only need to change the SELECT query to the SELECT INTO query.

SELECT grade, 
       MIN(score) AS min_score, 
       MAX(score) AS max_score,
       CONCAT('Score > ', MIN(score) -1,' AND',' Score <= ', MAX(score),' => Grade = ', grade) AS grade_rule
INTO grade_dictionary
FROM los_angeles_restaurant_health_inspections
GROUP BY grade
ORDER BY grade ASC;

10. Coding Question: Recent Refinance Submissions

The question by MetLife and CreditKarma wants you to return the total loan balance on each user’s most recent ‘Refinance’ submission.

Link to the question: https://platform.stratascratch.com/coding/2003-recent-refinance-submissions?code_type=5

For it to be more DBA life-like example, we can frame the question in the following way. Some database users need daily information on the submissions and the total balance of each user's latest ‘Refinance’ submission. Your colleagues are not tech-savvy; you don’t want them to mess with your database, but you also want to help them. So, you decide to create a view they can run daily and get the necessary info.

Let’s start by writing the solution from the subquery. It returns the unique combinations of the loan and user ID, loan date, and the latest loan date by user and loan type. This last information is calculated by using the MAX() window function.

Additionally, WHERE is used to output only the info for the refinance loans.

SELECT DISTINCT id,  
                user_id, 
                created_at,
                MAX(created_at) OVER (PARTITION BY user_id, type) AS most_recent
FROM loans
WHERE type = 'Refinance';

This is what the code will return.

Now, we write the main query. It joins the subquery and the table submissions on the loan ID. It returns the user ID and the loan balance but uses WHERE to return only the most recent refinance submissions.

SELECT ref.user_id, 
       balance
FROM
  (SELECT DISTINCT id,  
                   user_id, 
                   created_at,
                   MAX(created_at) OVER (PARTITION BY user_id, type) AS most_recent
   FROM loans
   WHERE type = 'Refinance') AS ref
INNER JOIN submissions AS s ON ref.id = s.loan_id
WHERE most_recent = created_at;

This solves the original problem.

Now, to solve the DBA problem of creating a view for your colleagues, you need to use the above query in a CREATE VIEW statement like this.

CREATE VIEW refinance_view AS
SELECT ref.user_id, 
       balance
FROM
  (SELECT DISTINCT id,  
                   user_id, 
                   created_at,
                   MAX(created_at) OVER (PARTITION BY user_id, type) AS most_recent
   FROM loans
   WHERE type = 'Refinance') AS ref
INNER JOIN submissions AS s ON ref.id = s.loan_id
WHERE most_recent = created_at;

Troubleshooting and Problem-Solving Questions

SQL Server DBAs will be solving problems and troubleshooting database daily. Some of the examples of these tasks are given in the interview questions that follow.

11. Non-Coding Question: How Would You Diagnose and Resolve a Deadlock Situation?

To diagnose a deadlock, you need to take the following steps.

1. Collecting & Analyzing Deadlock Information
2. Reviewing Query Execution Plans
3. Checking System & Application Logs

Here’s what that means.

After you diagnose a deadlock, you can attempt to resolve it using these steps.

1. Query Optimization
2. Adjusting Transaction Isolation Levels
3. Minimizing Lock Footprint
4. Using Locking Hints Sparingly
5. Implementing Retry Logic
6. Reviewing Schema Design
7. Monitoring & Adjusting

This is what it means in practice.

12. Non-Coding Question: How Do You Handle Database Corruption?

Here’s how you can approach answering this SQL Server DBA interview question and handling the corrupted database.

Steps to include:

1. Identifying Corruption
2. Assessing the Damage
3. Planning Recovery
4. Performing Recovery
5. Post-Recovery Steps
6. Preventing Future Corruption

Here are the explanations.

13. Non-Coding Question: Explain How You Would Respond to a Security Breach.

Responding to a security breach in a SQL Server environment could take the following outline.

Here’s how you can structure your response plan:

1. Initial Response
2. Investigation
3. Eradication & Recovery
4. Post-Breach Actions
5. Legal & Regulatory Compliance

Here’s a detailed explanation of each phase.

14. Non-Coding Question: What Steps Would You Take if You Notice a Sudden Drop in Database Performance?

You could answer this question by explaining the following steps you would take in the SQL Server environment.

1. Initial Assessment
2. Identifying the Scope
3. Analyzing Specific Areas
4. System-Level Checks
5. Addressing Identified Issues
6. Preventive Measures
7. Documentation and Review
   

Here’s each step explanation.

15. Non-Coding Question: How Would You Approach Resolving Performance Issues Caused by tempdb Contention in the SQL Server?

The tempdb contention often occurs due to heavy use of tempdb for temporary tables, table variables, version stores, and internal worktables for operations like sorting and hashing.

Here are the steps that should be taken to approach this issue.

1. Identifying tempdb Contention
2. Mitigating Contention
3. Query & Application Optimization
4. Monitoring & Maintenance
5. Reviewing tempdb Configuration

Each step consists of several tasks.

Conclusion

The questions that I covered are only a framework of how your SQL Server DBA skills will be gauged at the interview. There will be questions of different difficulty and, most often, will include both coding and non-coding questions.

You can practice both on our StrataScratch platform. The coding questions have a code editor where you can choose T-SQL, an SQL dialect used in MS SQL Server. The non-coding questions we covered belong to the Technical category, but you’ll also find some interesting questions in other sections. We already covered many of those in our blog, for example, in an article about SQL interview questions.