SQL Server Interview Topic 5: Performance and Production Basics

This topic checks whether you understand how SQL Server behaves in real projects. A full stack developer does not need to be a DBA, but must know enough to write safe queries and discuss production issues.

🎯 Interview Goal

You should be able to explain indexes, transactions, locking, triggers, backups, monitoring, and security at a practical developer level.

Q38. What is an index in SQL Server?

Quick interview answer:

An index helps SQL Server find rows faster, like an index in a book. It is useful on columns used often in WHERE, JOIN, and ORDER BY. But too many indexes can slow down inserts and updates because SQL Server must maintain them.

Study in detail: Indexes and Performance - This article explains when indexes help and when they hurt.

Q39. What is the difference between clustered and nonclustered index?

Quick interview answer:

A clustered index controls the physical order of data in a table, so a table can have only one clustered index. A nonclustered index is a separate structure that points to the data rows, so a table can have many nonclustered indexes.

Study in detail: Indexes and Performance - This lesson explains index types with examples.

Q40. What is an execution plan?

Quick interview answer:

An execution plan shows how SQL Server plans to run a query. It can show whether SQL Server is scanning many rows or using an index. Developers use it to understand why a query is slow.

Study in detail: Monitoring and Performance - This article introduces performance checks used in real projects.

Q41. What is a transaction?

Quick interview answer:

A transaction groups multiple SQL operations into one safe unit. Either all operations succeed, or all are rolled back. For example, fee payment and fee history insert should both succeed together.

Study in detail: Transactions and Concurrency - This article explains safe data changes with practical examples.

Q42. What are ACID properties?

Quick interview answer:

ACID means Atomicity, Consistency, Isolation, and Durability. It describes how transactions keep data safe. In simple words, a transaction should be complete, valid, protected from other users, and saved permanently after commit.

Study in detail: Transactions and Concurrency - This lesson explains transaction safety in beginner language.

Q43. What is concurrency in SQL Server?

Quick interview answer:

Concurrency means multiple users or processes are working with the database at the same time. SQL Server uses locks and isolation levels to protect data. Developers should understand this when two users may update the same record.

Study in detail: Transactions and Concurrency - This article explains common multi-user issues.

Q44. What is a trigger?

Quick interview answer:

A trigger is SQL code that runs automatically when an insert, update, or delete happens on a table. It can be useful for audit logging, but it should be used carefully because hidden automatic logic can make debugging harder.

Study in detail: Triggers and Audit Logging - This lesson explains where triggers are useful and where they are risky.

Q45. Why are backup and restore important?

Quick interview answer:

Backup protects data by saving a copy of the database. Restore brings the database back from a backup if data is lost or damaged. In production, backup and restore planning is critical because data is more important than code.

Study in detail: Backup and Restore - This article explains backup basics for developers.

Q46. What should developers know about SQL Server security?

Quick interview answer:

Developers should use least privilege, avoid sharing admin accounts, protect connection strings, and use parameterized queries. Security is not only a DBA topic because insecure code can expose or damage data.

Study in detail: Security - This lesson explains practical security rules for application developers.

Practice Before Next Topic

Write or explain these scenarios:

1. Add an index for a student search screen.
2. Explain why too many indexes can slow down writes.
3. Use a transaction for fee payment and fee history.
4. Explain why a trigger might be useful for audit logging.
5. List three SQL Server security rules for developers.

💡 Beginner Interview Tip

In production questions, do not pretend to be a DBA. Answer from a developer point of view: safe queries, transactions, indexing basics, and secure access.