# 50 SQL Server Interview Questions with Answers
## Basic SQL Server Questions
1. **What is SQL Server?**
- SQL Server is a relational database management system (RDBMS) developed by Microsoft that supports transaction processing, business intelligence, and analytics applications.
2. **What are the different editions of SQL Server?**
- Enterprise, Standard, Web, Developer, and Express editions.
3. **What is the difference between clustered and non-clustered indexes?**
- A clustered index determines the physical order of data in a table (only one per table). A non-clustered index is a separate structure that points to the data (multiple allowed per table).
4. **What is a primary key?**
- A primary key is a column or set of columns that uniquely identifies each row in a table and cannot contain NULL values.
5. **What is a foreign key?**
- A foreign key is a column or set of columns that establishes a relationship between data in two tables, enforcing referential integrity.
## Intermediate SQL Server Questions
6. **What is the difference between DELETE, TRUNCATE, and DROP?**
- DELETE removes rows one at a time with logging, TRUNCATE removes all rows quickly without logging individual row deletions, DROP removes the entire table structure.
7. **What are stored procedures?**
- Stored procedures are precompiled collections of SQL statements stored in the database that can be executed as a single unit.
8. **What is the difference between CHAR and VARCHAR data types?**
- CHAR is fixed-length (pads with spaces), VARCHAR is variable-length (uses only needed space).
9. **What is a transaction in SQL Server?**
- A transaction is a sequence of operations performed as a single logical unit of work that follows ACID properties (Atomicity, Consistency, Isolation, Durability).
10. **What is the purpose of the HAVING clause?**
- HAVING filters groups after the GROUP BY clause applies, while WHERE filters rows before grouping.
11. **What is a view in SQL Server?**
- A view is a virtual table based on the result set of a SQL statement that doesn't store data itself.
12. **What is the difference between UNION and UNION ALL?**
- UNION combines results and removes duplicates, UNION ALL combines all results including duplicates.
13. **What are SQL Server Agent jobs?**
- SQL Server Agent jobs are scheduled tasks that can run T-SQL scripts, SSIS packages, or other operations at specified times.
14. **What is the purpose of the COALESCE function?**
- COALESCE returns the first non-NULL expression among its arguments.
15. **What is a CTE (Common Table Expression)?**
- A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
## Advanced SQL Server Questions
16. **What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?**
- ROW_NUMBER() assigns unique numbers to each row, RANK() leaves gaps in ranking for ties, DENSE_RANK() doesn't leave gaps in ranking for ties.
17. **What is the purpose of the PIVOT operator?**
- PIVOT rotates rows into columns, transforming unique values from one column into multiple columns in the output.
18. **What is the difference between a temp table and a table variable?**
- Temp tables are physical tables in tempdb with statistics, table variables are in memory with no statistics. Temp tables can be altered after creation, table variables cannot.
19. **What is the purpose of the OUTPUT clause?**
- The OUTPUT clause returns information about rows affected by INSERT, UPDATE, DELETE, or MERGE statements.
20. **What is a deadlock in SQL Server?**
- A deadlock occurs when two or more processes permanently block each other by each holding a resource the other needs.
21. **What is the difference between ISNULL and COALESCE?**
- ISNULL is SQL Server-specific and takes only 2 parameters, COALESCE is ANSI standard and can take multiple parameters.
22. **What is the purpose of the MERGE statement?**
- MERGE performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.
23. **What are window functions in SQL Server?**
- Window functions perform calculations across a set of table rows related to the current row (e.g., OVER clause with PARTITION BY).
24. **What is the purpose of the APPLY operator?**
- APPLY allows joining a table to a table-valued function, with CROSS APPLY (like INNER JOIN) or OUTER APPLY (like LEFT JOIN).
25. **What is the difference between a scalar function and a table-valued function?**
- Scalar functions return a single value, table-valued functions return a table data type.
## Performance Tuning Questions
26. **What is an execution plan?**
- An execution plan shows how SQL Server will execute a query, including indexes used, join types, and operation costs.
27. **What is index fragmentation and how do you fix it?**
- Fragmentation occurs when index pages are not physically contiguous. Fix with REORGANIZE (light) or REBUILD (heavy).
28. **What is the purpose of the OPTION (OPTIMIZE FOR) hint?**
- It instructs the query optimizer to use a particular parameter value when compiling the query plan.
29. **What are covering indexes?**
- Covering indexes include all columns needed for a query, eliminating the need to access the base table.
30. **What is parameter sniffing?**
- Parameter sniffing is when SQL Server creates an execution plan optimized for the first set of parameter values it sees.
31. **What is the purpose of the NOLOCK hint?**
- NOLOCK allows dirty reads by not issuing shared locks (can read uncommitted data - can cause issues).
32. **What is a wait statistic in SQL Server?**
- Wait statistics show what resources queries are waiting for (CPU, I/O, locks, etc.), helping identify bottlenecks.
33. **What is the purpose of the Database Engine Tuning Advisor?**
- It analyzes workloads and recommends indexes, indexed views, or partitions to improve performance.
34. **What is the difference between a seek and a scan in query execution?**
- A seek uses an index to find specific rows efficiently, a scan reads all rows in a table or index.
35. **What is the purpose of the STATISTICS IO option?**
- It shows I/O statistics for statements, helping identify expensive queries.
## Security and Administration Questions
36. **What are the different types of backups in SQL Server?**
- Full, differential, transaction log, file/filegroup, and copy-only backups.
37. **What is the difference between authentication and authorization?**
- Authentication verifies identity (login), authorization determines access rights (permissions).
38. **What are schemas in SQL Server?**
- Schemas are containers that group database objects for easier management and security.
39. **What is the purpose of the TRY...CATCH block?**
- It provides error handling in T-SQL by catching and handling exceptions.
40. **What is the difference between a login and a user in SQL Server?**
- A login is for server access, a user is for database access (logins are mapped to users).
41. **What is the purpose of the DBCC CHECKDB command?**
- It checks the logical and physical integrity of all objects in a database.
42. **What are SQL Server roles?**
- Roles are groups of permissions that can be assigned to users (server roles and database roles).
43. **What is the purpose of the OUTPUT clause with INSERT/UPDATE/DELETE?**
- It captures affected rows during data modification operations.
44. **What is the difference between a contained database and a regular database?**
- Contained databases store authentication information within the database rather than in master.
45. **What is Always On Availability Groups?**
- A high-availability solution that provides database-level failover with multiple readable secondaries.
## Development Questions
46. **What is the difference between a correlated subquery and a regular subquery?**
- A correlated subquery references columns from the outer query and executes once per outer row, while a regular subquery executes once.
47. **What is the purpose of the CROSS APPLY operator?**
- It joins a table with a table-valued function, returning only rows where the function produces a result (like INNER JOIN).
48. **What is the difference between RAISERROR and THROW?**
- THROW is simpler (no parameters), always severity 16, requires preceding statement to end with semicolon. RAISERROR is more flexible.
49. **What is the purpose of the SEQUENCE object?**
- SEQUENCE generates a sequence of numeric values independently of tables (unlike IDENTITY which is table-specific).
50. **What is the difference between OFFSET-FETCH and TOP?**
- OFFSET-FETCH allows paging (skip rows then take rows), TOP only limits the number of rows returned.
No comments:
Post a Comment