The Ultimate SQL Server Interview Guide: 700+ Questions & Answers (Latest)

 

🟢 Part 1: Basics & Fundamentals (For Beginners & Freshers) – ~150 Questions

1. SQL Server Introduction & Basics (25 Qs)

• What is SQL Server and its primary components (Database Engine, SSIS, SSRS, SSAS)?

• Difference between SQL (language) and SQL Server (RDBMS).

• Explain the key differences between SQL Server, MySQL, PostgreSQL, and Oracle.

• What are the different SQL Server editions (Express, Standard, Enterprise, Developer, Web)? Key limitations of each.

• Real-life scenario: When would you choose the Developer edition over Enterprise for a project?

• What is a SQL Server Instance? Difference between Default and Named Instance.

• What is the system database? Purpose of master, msdb, model, and tempdb.

• What is tempdb and what is it used for? What are common issues with tempdb?

• How do you find your SQL Server version and edition?

2. Database Design & Data Modeling Basics (30 Qs)

• What is a database schema? Give examples (e.g., dbo, sales).

• Explain Entity-Relationship (ER) Diagrams: Entities, Attributes, Relationships.

• Define Primary Key, Foreign Key, and Unique Key with examples.

• What is a Composite Key?

• Explain Clustered vs Non-Clustered Index with a real-world book index analogy.

• Basics of Normalization: Goals and problems it solves (insert/update/delete anomalies).

• Explain 1NF, 2NF, and 3NF with practical examples.

• Briefly describe 4NF and 5NF.

• What is Denormalization? When and why would you use it?

• Surrogate Key (e.g., Identity column) vs Natural Key (e.g., SSN, Email). Pros and cons.

• What is a Candidate Key?

• What is referential integrity?

3. SQL Query Fundamentals (40 Qs)

• Write basic SELECT, INSERT, UPDATE, DELETE statements.

• Using WHERE with operators (=, <>, >, <, BETWEEN, IN, LIKE with % and _).

• Using ORDER BY (ASC, DESC) and handling NULLs in ordering.

• Using DISTINCT to eliminate duplicates.

• GROUP BY clause: Why is it used? How does it work with aggregate functions?

• HAVING vs WHERE clause. Why can't you use WHERE with aggregate functions?

• Explain different types of JOINS: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.

• What is a Cartesian Product and when does it occur?

• What is a Self Join? Provide an example (e.g., employee manager hierarchy).

• What is a Subquery? Difference between a scalar and multi-valued subquery.

• Introduction to CTEs (Common Table Expressions). Basic syntax.

• Key Aggregate functions: COUNT, SUM, AVG, MIN, MAX.

• Difference between COUNT(*), COUNT(1), and COUNT(ColumnName).

• Using TOP and OFFSET-FETCH for pagination.

4. Constraints & Data Integrity (25 Qs)

• Explain NOT NULL, UNIQUE, CHECK, DEFAULT constraints.

• How do you add/drop constraints from an existing table?

• FOREIGN KEY constraints and cascading actions: ON DELETE CASCADE, ON UPDATE SET NULL.

• Pros and Cons of enforcing referential integrity in the database vs. the application.

• What is a UNIQUE constraint vs. a UNIQUE INDEX?

5. Basic Stored Procedures & Functions (30 Qs)

• What is a Stored Procedure? Benefits (performance, security, maintainability).

• Basic syntax of CREATE PROCEDURE.

• Using input and output parameters.

• What is a Function? Difference between Stored Procedures and Functions.

• Scalar Function vs Table-Valued Function (Inline vs Multi-Statement).

• Why can't a Function use INSERT/UPDATE/DELETE?

• Basic error handling with @@ERROR.

• Introduction to TRY...CATCH blocks.

---

🟡 Part 2: Intermediate SQL Server (Developer Focused) – ~200 Questions

6. Advanced SQL Query Writing & Techniques (50 Qs)

• Window Functions:

  • ROW_NUMBER(), RANK(), DENSE_RANK(). Differences and use cases.

  • NTILE() function for grouping.

  • LEAD() and LAG() for accessing data from next/previous rows.

  • Using OVER() clause with PARTITION BY and ORDER BY.

• Advanced Joins & Set Operations:

  • Using EXISTS vs IN vs JOIN. Performance implications.

  • What is a Correlated Subquery? How is it different from a regular subquery?

  • UNION vs UNION ALL. Which is faster and why?

  • INTERSECT and EXCEPT operators.

• Pivoting Data:

  • Using PIVOT and UNPIVOT operators.

  • How to pivot data dynamically when column names are not known beforehand?

• Advanced String/Date Manipulation:

  • CONCAT(), STRING_AGG(), STRING_SPLIT().

  • FORMAT(), CAST(), CONVERT(), PARSE().

  • Date functions: DATEFROMPARTS, DATEDIFF, DATEADD, EOMONTH.

7. Tricky Query Scenarios & Problem Solving (40 Qs)

• How to find the nth highest salary?

• How to find duplicate rows based on one or multiple columns?

• How to delete these duplicates, keeping only one?

• How to find employees who have the same name and salary?

• How to get a hierarchical tree structure (e.g., manager-employee)?

• How to calculate a running total?

• How to find gaps in a sequence of numbers or dates (e.g., missing days)?

• How to swap male and female values in a single column with a single update?

• How to select all customers who have purchased all products from a list?

• How to get the last generated identity value (@@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT)?

8. Transactions & Concurrency (30 Qs)

• What is a Transaction? Explain ACID properties (Atomicity, Consistency, Isolation, Durability).

• How to define explicit transactions using BEGIN TRANSACTION, COMMIT, ROLLBACK.

• What are SQL Server Transaction Isolation Levels?

  • Read Uncommitted (Dirty Reads)

  • Read Committed (Default)

  • Repeatable Read

  • Serializable

  • Snapshot

• What are the concurrency side effects: Dirty Reads, Non-Repeatable Reads, Phantom Reads?

• What is deadlocking? How can you identify and prevent it? (e.g., SET DEADLOCK_PRIORITY, access objects in the same order).

• Optimistic vs Pessimistic Concurrency.

• What is the NOLOCK hint? Why is it dangerous?

9. Triggers & Advanced Stored Procedures (30 Qs)

• What is a Trigger? AFTER (FOR) trigger vs INSTEAD OF trigger.

• How to access inserted and deleted data inside a trigger? (Using the inserted and deleted magic tables).

• Can a trigger be recursive? How do you control nested triggers?

• What are the advantages and disadvantages of using triggers? (e.g., for auditing vs performance overhead).

• How would you design a table to audit changes (who, when, what changed)?

• Stored Procedure: Using default parameters.

• Stored Procedure: Using OUTPUT parameters to return values.

• Stored Procedure: Using return codes.

10. Performance Tuning & Execution Plans (50 Qs)

• How do you identify a slow query? (Using DMVs, Query Store, Extended Events).

• What is an Execution Plan? How do you view it? (Estimated vs Actual).

• Key operators: Table Scan, Clustered Index Scan, Index Seek, Key Lookup, Sort, Hash Match.

• What is a "Key Lookup" and why is it a performance problem? How do you fix it? (Covering Indexes).

• What is Parameter Sniffing? How can it cause performance issues? How to mitigate it? (OPTION(RECOMPILE), OPTIMIZE FOR).

• What are Statistics? Why are they crucial for the query optimizer?

• How do you update statistics? (UPDATE STATISTICS).

• Introduction to Dynamic Management Views (DMVs): sys.dm_exec_query_stats, sys.dm_exec_sql_text.

• Best practices for writing efficient WHERE clauses.

---

🔵 Part 3: Advanced SQL Server (DBA & Performance Focused) – ~180 Questions

11. Advanced Indexing & Query Tuning (50 Qs)

• Index Deep Dive:

  • How is a Clustered Index physically organized?

  • What is the difference between a Unique and Non-Unique Non-Clustered Index?

  • What are Included Columns (Covering Indexes)?

  • What are Filtered Indexes? When are they useful?

  • What are Columnstore Indexes? How do they benefit Data Warehousing/OLAP?

  • What is Index Fragmentation? How do you measure it (sys.dm_db_index_physical_stats)?

  • ALTER INDEX REBUILD vs ALTER INDEX REORGANIZE. When to use which?

• Query Tuning:

  • How to force or prevent index usage with hints (WITH (INDEX=...), FORCESEEK)?

  • What are the different types of Joins (Nested Loops, Merge, Hash) and when does the optimizer choose each?

  • How to use the Query Store to force a plan, identify regressions, and analyze performance over time?

  • What are Cursors? Why are they generally bad for performance? What are better set-based alternatives?

12. Backup, Restore & Recovery (40 Qs)

• Recovery Models: Simple, Full, Bulk-Logged. Impact on backup and restore.

• Types of backups: Full, Differential, Transaction Log.

• Explain a common backup strategy (e.g., Full nightly, Differential every 4 hours, Log every 15 minutes).

• How do you perform a Point-in-Time restore?

• What is a Tail-Log backup and when is it critical?

• What is CHECKSUM and why use it during backup?

• How do you verify a backup without restoring it? (RESTORE VERIFYONLY).

• What is a Copy-Only backup?

• Automating backups with SQL Server Agent Jobs and Maintenance Plans.

13. High Availability & Disaster Recovery (40 Qs)

• Always On Availability Groups:

  • What is an Availability Group? How is it different from Database Mirroring?

  • Primary vs Secondary replicas. Readable secondaries.

  • Synchronous vs Asynchronous commit.

• Log Shipping: Concept of Primary and Standby server. Monitor and role change process.

• Replication: Types (Snapshot, Transactional, Merge). Use cases for each. Publisher, Distributor, Subscriber model.

• Failover Cluster Instance (FCI): Shared storage concept. Difference from Availability Groups.

14. Security & Auditing (30 Qs)

• Authentication Modes: Windows Authentication vs SQL Server Authentication.

• Principles: Logins (Server-level) vs Users (Database-level).

• Roles: Server Roles (sysadmin) vs Database Roles (db_owner) vs User-Defined Database Roles.

• Permissions: GRANT, DENY, REVOKE on securables (e.g., Tables, Procedures).

• What is Row-Level Security (RLS)? How do you implement it?

• What is Dynamic Data Masking? Provide an example.

• What is SQL Server Audit? How do you set it up?

• What is Transparent Data Encryption (TDE)? What does it encrypt and what does it not protect against?

15. Maintenance, Monitoring & Troubleshooting (20 Qs)

• What is DBCC CHECKDB? Why is it a critical part of maintenance?

• How to monitor current activity (sp_who2, sys.dm_exec_sessions, sys.dm_exec_requests)?

• How to identify and kill a blocking process?

• What are Wait Statistics? Common wait types (e.g., PAGEIOLATCH_*, CXPACKET, LCK_M_*).

• Using PerfMon counters for SQL Server (% Process Time, Page Life Expectancy, Batch Requests/sec).

---

🔴 Part 4: Expert Level (Architect & Designer Focused) – ~120 Questions

16. Advanced Database Design & Architecture (50 Qs)

• Data Warehousing Concepts:

  • OLTP vs OLAP systems. Design goals for each.

  • Star Schema vs Snowflake Schema. Pros and cons.

  • Fact Tables (Transactional, Periodic Snapshot, Accumulating Snapshot) and Dimension Tables.

  • Slowly Changing Dimensions (SCD): Detailed explanation of Type 0, 1, 2, 3, 4, 6.

• Scalability & Partitioning:

  • Table and Index Partitioning. Concept of partition switching for efficient data loads and archives.

  • What is Sharding? How can it be implemented with SQL Server?

• Development Patterns:

  • Database-per-Service vs Shared Database in a Microservices architecture.

  • How to design a multi-tenant database (Shared Database, Separate Schemas, Separate Databases).

  • What is CQRS? How can SQL Server be used in a CQRS pattern? (Read replicas for queries).

17. Advanced T-SQL & Internal Mechanisms (40 Qs)

• Advanced Error Handling:

  • Using THROW vs RAISERROR.

  • Creating custom error messages.

  • Building robust, nested error handling in complex procedures.

• Temporary Objects:

  • Detailed differences between Temp Tables (#temp), Table Variables (@table), and CTEs.

  • Scope and lifetime of each. Which one creates statistics?

• Dynamic SQL:

  • EXEC vs sp_executesql. Why is sp_executesql better for parameterization and plan reuse?

  • How to avoid SQL Injection in Dynamic SQL? (Use parameters, don't concatenate user input).

• Set-based vs Iterative (Cursor-based) programming: Advanced examples of converting loops to set-based logic.

18. Integration, Cloud & Big Data (30 Qs)

• SSIS (SQL Server Integration Services): Basic concepts of Control Flow and Data Flow. ETL vs ELT.

• Linked Servers: How to query data from a remote SQL Server or other data source (e.g., Oracle, Excel)?

• Azure SQL Database: Key differences from SQL Server (e.g., no server-level features, different backup model).

• PolyBase: Concept of querying external data in Hadoop or Azure Blob Storage using T-SQL.

• Azure Synapse Analytics: How does it relate to SQL Server for big data scenarios?

---

🟣 Part 5: Real-Life Scenarios & Case Studies – ~50 Questions

19. Real-World Problem Solving & System Design (50 Qs)

• Scenario: "Our orders table has 500 million rows and is slow. How would you approach performance tuning?" (Cover indexing, partitioning, archiving).

• Scenario: "Design a database for a new social media app." (Discuss tables, relationships, how to handle likes, posts, followers, and scale considerations).

• Scenario: "How would you design a system to maintain a full audit history of every change to the Products table?" (Discuss triggers, CDC, temporal tables).

• Scenario: "A user reports a wrong result from a report. How do you debug it?" (Trace back from report to source data, check ETL logic, check for data corruption).

• Scenario: "The application is slow. How do you determine if the problem is the database or the application?" (Use DMVs to check for waits, blocks, and query performance).

• Scenario: "Write a query to find the last login date for every user, but only if they have logged in within the last 90 days."

• Scenario: "Design a query to calculate employee tenure, handling cases where the end date is NULL (current employee)."

• Scenario: "How would you safely deploy a schema change (e.g., adding a NOT NULL column) to a large production table?"

🔷 Part 6: Expert Query Craftsmanship & Tricky Puzzles – ~120 Questions

20. Advanced & Obscure SQL Query Techniques (40 Qs)

• How to perform a conditional aggregate (e.g., SUM only specific rows within a GROUP BY)?

• How to use a CASE statement inside an aggregate function?

• How to transpose a set of rows into a single comma-separated string without STRING_AGG (using FOR XML PATH)?

• How to calculate a moving average or a rolling sum for the last N days?

• How to find all parents in a hierarchy from a specific child node (recursive CTE upwards)?

• How to implement a "running difference" or inventory balance calculation?

• How to select every nth row from a table?

• How to generate a calendar or date range on the fly for a given period?

• How to query for overlapping date ranges (e.g., find active subscriptions during a period)?

• How to use CROSS APPLY to simplify complex calculations on a per-row basis?

21. Complex Data Manipulation & "Tricky" Scenarios (50 Qs)

• Deletion Scenarios:

  • How to delete duplicate rows but keep the one with the latest CreatedDate?

  • How to delete all rows from a table that have a corresponding match in another table?

  • How to safely archive and delete millions of rows in batches to avoid transaction log explosion?

• Update Scenarios:

  • How to perform a "swap" of values between two rows or columns?

  • How to update a table based on a complex join to another table?

  • How to increment a counter for each row in a group, resetting for each new group?

• Insertion Scenarios:

  • How to insert the output of a stored procedure into a table?

  • How to use INSERT with EXEC to capture dynamic SQL results?

  • How to conditionally insert or update based on existence (the "UPSERT" pattern) using MERGE (and its pitfalls) or IF EXISTS?

• Selection Scenarios:

  • How to find all customers who have never placed an order?

  • How to find the single most recent record for each group in a table (e.g., latest login per user)?

  • How to pivot an unknown number of columns (Dynamic Pivot)?

22. SQL Functions Deep Dive: UDFs & Beyond (30 Qs)

• What are the performance implications of Scalar UDFs? How do they inhibit parallelism?

• How can an Inline Table-Valued Function (iTVF) be rewritten to avoid UDF performance pitfalls?

• What are the limitations of Multi-Statement Table-Valued Functions (mTVFs)?

• How to use deterministic vs non-deterministic functions in computed columns or indexes?

• How to create a custom aggregate function using SQLCLR? (And why you often shouldn't).

• How to debug a function, given you cannot use PRINT statements inside it?

---

🔷 Part 7: Programmatic SQL & Automation – ~130 Questions

23. Advanced Stored Procedures & Parameter Mastery (40 Qs)

• How to use OUTPUT parameters to return multiple scalar values from a procedure.

• How to use the OUTPUT clause to return inserted/updated/deleted data from a DML operation inside a proc.

• How to handle optional parameters for flexible WHERE clauses (e.g., WHERE Name = @Name OR @Name IS NULL). What are the performance pitfalls of this approach?

• What is the best way to pass a list of values (e.g., '1,5,7,10') to a stored procedure? (Compare to Table-Valued Parameters).

• How to use EXECUTE AS to control security context within a proc.

• How to use RESULT SETS UNDEFINED with EXEC for dynamic SQL.

24. Robust Error Handling & Transaction Management (40 Qs)

• How to use TRY...CATCH effectively within a stored procedure.

• How to use XACT_STATE() to check for a doomed transaction.

• How to build a reusable error logging mechanism that captures ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), etc.

• How to implement custom error codes and severity levels using THROW.

• How to handle errors in nested stored procedure calls and maintain transaction integrity.

• What is the difference between @@TRANCOUNT and XACT_STATE()?

• How to implement retry logic for deadlock victims.

25. Trigger Strategies & Alternatives (30 Qs)

• How to write a trigger that updates a "LastModified" timestamp on any change.

• How to use triggers to enforce complex multi-table business rules that cannot be done with constraints.

• What are the performance implications of triggers on bulk insert operations?

• How to disable triggers for a specific session or operation.

• What are the alternatives to triggers? (e.g., Change Data Capture (CDC), Temporal Tables, application logic).

• How to avoid trigger recursion.

26. Automation & Interaction: SQL Agent, Email, and OS (20 Qs)

• How to create a SQL Server Agent Job to run a stored procedure on a schedule.

• How to configure Database Mail and send an email from within a T-SQL script or procedure.

• How to use xp_cmdshell to execute an operating system command (and why it's a security risk).

• How to use the SQLCMD mode in SSMS for scripting.

---

🔷 Part 8: Performance Mastery & Deep Internals – ~130 Questions

27. Query Tuning & Execution Plan Analysis (50 Qs)

• How to identify and fix a "Key Lookup" (RID Lookup) operator.

• How to use "Wait Statistics" (DMVs like sys.dm_os_wait_stats) to identify system bottlenecks.

• How to use sys.dm_exec_query_stats to find the most CPU-intensive queries in the plan cache.

• How to force a query to recompile to avoid parameter sniffing.

• How to use Query Store to force a specific execution plan.

• How to interpret "Estimated Number of Rows" vs "Actual Number of Rows" and what a large discrepancy means.

• How to use Extended Events for lightweight query profiling instead of the deprecated SQL Profiler.

28. Advanced Indexing & Partitioning Strategies (50 Qs)

• How to design a filtered index for a specific query subset (e.g., WHERE IsActive = 1).

• When should you use an INCLUDE clause in a non-clustered index?

• How to use the Database Engine Tuning Advisor (DTA) and interpret its recommendations.

• What is an indexed view? How do you create one and what are the restrictions?

• Table Partitioning:

  • How to create a partition function and partition scheme based on a datetime column.

  • How to implement a sliding window scenario: switching out a partition of old data to a staging table and switching in a new empty partition.

  • How does partitioning affect indexing (aligned vs non-aligned indexes)?

29. Managing Bulk Data Operations & tempdb (30 Qs)

• How to perform a minimal-logged bulk insert (using bcp, BULK INSERT, or SSIS with the right table lock and recovery model settings).

• What are the best practices for configuring and placing tempdb? (Multiple data files, trace flags).

• How to identify tempdb contention using DMVs.

• What operations in SQL Server make heavy use of tempdb? (Temp tables, version store, sorts, hashes, DBCC CHECKDB).

• How to monitor tempdb space usage.

---

🔷 Part 9: High Availability, DR, & Cloud – ~80 Questions

30. Always On Availability Groups (40 Qs)

• Explain the difference between Synchronous and Asynchronous commit modes and their impact on performance and data loss.

• How to configure a read-only routing list for an Availability Group listener.

• How to perform a planned failover and a forced failover (with possible data loss).

• How to monitor AG synchronization health (sys.dm_hadr_database_replica_states, last_commit_time).

• What are the common reasons for a synchronous replica to go into a "NOT SYNCHRONIZED" state?

31. Hybrid & Cloud Scenarios (40 Qs)

• What is the difference between SQL Server on an Azure VM, Azure SQL Database, and Azure SQL Managed Instance?

• How to configure a backup to URL (Azure Blob Storage).

• How to use Azure Data Studio for cross-platform database management.

• What is the process for migrating an on-premises database to Azure SQL DB using the Data Migration Assistant (DMA)?

• How do high availability and disaster recovery concepts differ in Azure PaaS services compared to traditional IaaS/on-prem?

---

🔷 Part 10: The Architect's Toolbox – ~60 Questions

32. Monitoring, Logging, & Forensics (30 Qs)

• How to use SQL Server Profiler (or Extended Events) to trace a deadlock and save the deadlock graph.

• How to read the SQL Server Error Log for critical events and startup messages.

• How to configure and use Extended Events for continuous monitoring of specific events (e.g., sql_statement_completed).

• How to use Dynamic Management Views (DMVs) to get a real-time view of current queries, locks, and waits.

33. Security Hardening & Compliance (30 Qs)

• How to implement Row-Level Security (RLS) with a predicate function.

• How to use Always Encrypted (with and without secure enclaves).

• How to audit sensitive data access using SQL Server Audit.

• How to manage certificates for TDE or backing up encryption keys.

• How to check for and remediate common security vulnerabilities (e.g., SQL Injection vulnerabilities in dynamic SQL, excessive permissions).

🌟 Explore More Free Resources

📘 Free Job Preparation Zone 🎯 Visit Free Learning Zone 🗄️ Complete SQL Server 2025 Guide

🚀 Keep learning, keep growing — from basics to advanced SQL mastery!