Microsoft SQL Server powers many business applications, from ERP and CRM systems to custom web apps. These SQL Server interview questions cover administration, T-SQL, indexing, transactions, high availability and security. They suit DBA, developer and support roles.
Basics
1. What is SQL Server?
SQL Server is Microsoft’s relational database management system. It runs on Windows and Linux, and in containers. Azure SQL Database and Azure SQL Managed Instance offer it as a managed cloud service.
2. Name the main editions.
- Enterprise: all features, for large critical systems.
- Standard: core features with limits on resources.
- Express: free, with database size and resource limits.
- Developer: free, full-featured, for development and testing only.
3. How do you check the version?
SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('Edition');4. Which authentication modes exist?
Windows authentication uses Active Directory accounts, and it is the recommended option. Mixed mode also allows SQL Server logins with passwords. Microsoft Entra ID authentication is available in Azure and in newer versions.
5. Explain the system databases.
| Database | Purpose |
|---|---|
| master | Server-wide configuration and logins |
| model | Template for new databases |
| msdb | SQL Server Agent jobs, backup history |
| tempdb | Temporary objects; recreated at every restart |
Backup and recovery
6. Describe the recovery models.
- Simple: the log truncates automatically; no point-in-time restore.
- Full: keeps all log records until log backups; allows point-in-time restore.
- Bulk-logged: like Full, but minimally logs bulk operations.
7. Which backup types exist?
Full, differential (changes since the last full backup) and transaction log backups. A common plan is a weekly full backup, daily differentials and log backups every 15 minutes.
8. Why does the transaction log keep growing?
Usually, the database uses the Full recovery model without regular log backups. Either schedule log backups or switch to Simple recovery if point-in-time restore is not needed.
Indexes and performance
9. Clustered vs non-clustered index?
A clustered index sorts and stores the table rows themselves, so a table can have only one. A non-clustered index is a separate structure with pointers to the rows, and a table can have many.
10. What is a covering index?
It includes all columns a query needs, often through the INCLUDE clause. As a result, SQL Server answers the query from the index alone.
11. How do you find slow queries?
Use Query Store, execution plans, dynamic management views such as sys.dm_exec_query_stats, and Extended Events.
12. What is parameter sniffing?
SQL Server builds a plan for the first parameter values it sees and reuses it. If later values need a different plan, performance can drop. Fixes include query hints, plan guides and newer features such as parameter-sensitive plan optimisation.
Transactions
13. Name the isolation levels.
Read Uncommitted, Read Committed (default), Repeatable Read, Serializable and Snapshot. In addition, Read Committed Snapshot Isolation (RCSI) reduces blocking by using row versions.
14. Blocking vs deadlock?
Blocking happens when one session waits for another’s lock, and it clears when the first finishes. A deadlock is a cycle where sessions wait for each other forever. SQL Server detects it and rolls back one session as the “victim”. Read more in our guide to deadlocks.
High availability and security
15. What are the high availability options?
- Always On availability groups: replicate databases to secondary replicas, with automatic failover.
- Failover cluster instances: protect the whole instance with shared storage.
- Log shipping: copies and restores log backups to a standby server.
Database mirroring is deprecated, so new designs should use availability groups.
16. How do you secure SQL Server?
Prefer Windows or Entra authentication, disable or rename the sa login, apply least privilege through roles, encrypt connections with TLS, use Transparent Data Encryption for data at rest, enable auditing and apply cumulative updates promptly.
17. What does SQL Server Agent do?
It schedules and runs jobs, such as backups, index maintenance and ETL tasks, and sends alerts when jobs fail.
For other databases, see our Oracle and MySQL interview questions.
Keep preparing
Practise each answer out loud, and run the commands in a lab rather than memorising them. Interviewers often ask follow-up questions, so make sure you understand why each answer is true.
Sources
Preparing for an interview? Browse more questions in our interview preparation hub. For businesses, we also offer security services such as VAPT, server hardening and ISO 27001 readiness.
