Question 1
Difficulty: medium
Can you walk me through how you would design a database schema for a new application from scratch?
Sample answer
I usually start by getting very clear on the business process the application needs to support, because a good schema is really a reflection of real usage, not just a list of tables. I begin by identifying the core entities, their relationships, and the main queries the system must handle. From there, I define primary keys, foreign keys, constraints, and normalization rules to reduce duplication and protect data integrity. I also think early about performance needs, like which columns will be searched or joined often, so I can plan indexes without over-indexing. If the system is expected to grow, I consider partitioning, archival strategy, and whether certain data should be separated for scaling or security reasons. Before implementation, I like to validate the design with sample queries and edge cases. That helps me catch issues like ambiguous relationships or missing constraints before they become production problems.
Question 2
Difficulty: hard
How do you troubleshoot a slow SQL query in production?
Sample answer
My first step is to understand the actual workload and not just the query text. I look at the execution plan, the runtime metrics, and the data volume involved, because a query that looks fine on paper can behave badly at scale. I check whether the optimizer is using indexes effectively, whether there are scans that should be seeks, and whether joins are being done in the most efficient order. I also look for common issues like non-sargable predicates, functions on indexed columns, implicit conversions, and missing statistics. If the query is part of a larger transaction, I examine locking and blocking as well. When I make a change, I prefer to test one adjustment at a time so I can measure the impact clearly. In production, I balance speed with safety, so I’d rather make a targeted improvement that is easy to verify than a broad change that could create new issues.
Question 3
Difficulty: medium
Describe a time when you had to balance normalization with performance requirements.
Sample answer
In one project, I worked on a reporting system where the operational data was highly normalized, but the reporting queries were becoming too expensive because they needed many joins. The initial design was excellent for data integrity, but it wasn’t ideal for the read-heavy reporting layer. Instead of denormalizing everything, I worked with the team to create a reporting-friendly structure that pulled together the fields most commonly used in analytics while keeping the transactional tables normalized. We also added the right indexes and scheduled refresh logic so the reporting layer stayed current without affecting the main system too much. That approach gave us the best of both worlds: clean data management in the core application and faster query performance for reporting. What I learned from that experience is that normalization is not a religion; it’s a design choice that has to fit the business use case and access patterns.
Question 4
Difficulty: hard
How do you ensure data integrity when multiple applications are reading and writing to the same database?
Sample answer
I rely on a combination of database constraints, transaction design, and careful application coordination. At the database level, I make sure primary keys, foreign keys, unique constraints, and check constraints are in place so the system itself enforces the most important rules. For write operations, I use transactions to keep related changes atomic and consistent, and I pay attention to isolation levels so I understand how the system behaves under concurrency. If multiple applications touch the same data, I also want a clear ownership model for each table or business process, because shared writes without boundaries usually create trouble later. Where needed, I add audit columns or change tracking so we can trace what happened and when. I also like to test high-concurrency scenarios before release, because integrity problems often show up only when real users and integrations start hitting the system at the same time.
Question 5
Difficulty: hard
Tell me about a time you had to fix a production database issue under pressure.
Sample answer
A production incident I dealt with involved a sudden spike in deadlocks during a peak business window. The application team was seeing failed transactions, and the pressure was high because users were actively trying to complete orders. I first confirmed whether the issue was isolated or systemic by reviewing the deadlock graphs and recent deployment changes. Once I identified the conflicting access patterns, I worked with the developers to adjust the query order and reduce the time locks were held. I also recommended a short-term mitigation by lowering the scope of one transaction and temporarily reducing contention on the busiest table. After the immediate issue was stabilized, I helped test a longer-term fix in staging and monitored the production environment closely afterward. That situation reinforced for me how important it is to stay calm, isolate the root cause quickly, and fix the underlying pattern rather than just masking the symptoms.
Question 6
Difficulty: medium
What is your approach to writing and optimizing stored procedures?
Sample answer
I treat stored procedures as reusable, maintainable pieces of database logic, so I focus on clarity first and optimization second, but I never ignore either. I write them with predictable inputs and outputs, avoid unnecessary complexity, and make sure error handling is explicit. From a performance standpoint, I pay close attention to query plans, parameter sniffing, temp table usage, and whether the procedure is doing work row by row when it could be set-based. I also try to keep business logic appropriately placed; if logic belongs in the database because it must be close to the data and reused by multiple systems, I’m comfortable putting it there, but I don’t put everything in procedures by default. Before deployment, I test the procedure with realistic data volumes and parameter combinations, because a procedure that performs well for one case can still fail badly under another. My goal is consistency, not just a one-time fast result.
Question 7
Difficulty: hard
How do you handle schema changes when an application is already in production?
Sample answer
I approach schema changes very carefully because production databases usually support live users, integrations, and reporting jobs at the same time. My first step is to understand backward compatibility requirements, because the safest changes are ones that can be deployed in stages. If possible, I use an expand-and-contract strategy: add new columns or tables first, update the application to use them, and only remove old structures after the system has fully transitioned. I also review the impact on indexes, constraints, and any dependent views, stored procedures, or ETL jobs. For larger changes, I prefer to test them on production-like data and estimate the time and locking impact before applying them. Communication matters too, so I coordinate with developers, QA, and operations to ensure the deployment window and rollback plan are clear. A good schema change should improve the system without surprising the people who rely on it.
Question 8
Difficulty: medium
How do you decide when to use indexes, and how do you avoid over-indexing?
Sample answer
I use indexes based on actual access patterns, not just because a column looks important. I look at which queries are used most often, which filters and joins they rely on, and whether the table is read-heavy or write-heavy. An index can make reads much faster, but it also adds overhead on inserts, updates, and deletes, so I try to make sure every index earns its cost. I usually start with the most common and most expensive queries, then evaluate whether a single-column index, composite index, or covering index is the best fit. I also check whether the leading column order matches the query patterns, because the wrong order can make an index far less useful. Periodically, I review unused or duplicate indexes and remove them if they are not providing value. To me, good indexing is about precision. The goal is not to have the most indexes; it’s to have the right ones.
Question 9
Difficulty: easy
How do you work with developers and business analysts to gather database requirements?
Sample answer
I try to translate business needs into data requirements as early as possible, before people start coding around assumptions. With business analysts, I focus on the lifecycle of the data: where it comes from, how it changes, how long it needs to be retained, and what reports or transactions depend on it. With developers, I get into technical details like expected query patterns, concurrency, validation rules, and integration points. I find it helpful to ask for real use cases rather than abstract requirements, because examples often reveal edge cases that would otherwise be missed. I also like to review sample data and mock workflows, since those can expose issues in relationships, cardinality, or permissions. Throughout the process, I keep communication practical and iterative rather than waiting until a final review. In my experience, the best database designs come from close collaboration, where everyone understands not just what the system needs to store, but why and how it will be used.
Question 10
Difficulty: medium
What steps do you take to secure sensitive data in a database?
Sample answer
I start with classification, because you can’t protect sensitive data well if you haven’t identified it clearly. Once I know what needs protection, I apply the principle of least privilege so users and applications only get the access they actually need. I also look at encryption options, both in transit and at rest, depending on the sensitivity of the data and compliance requirements. For highly sensitive fields, I consider masking, tokenization, or separating data into restricted tables or schemas. Audit logging is also important because security is not just about prevention; it’s about being able to see and investigate access when needed. I pay attention to backup security too, since backups often contain the same sensitive information as the live system. Finally, I work closely with application and infrastructure teams so security is built into the design instead of added after a problem appears. That usually leads to stronger protection and fewer surprises later.
