SQL Server Interview Questions and Answers

This section covers frequently asked SQL Server interview questions.

1. What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It's used for storing, retrieving, and managing data within applications.

2. What is Normalization?

Normalization is a database design technique aimed at reducing data redundancy and improving data integrity. It involves organizing data into multiple tables and defining relationships between them.

Common Normal Forms:

  • 1NF (First Normal Form)
  • 2NF (Second Normal Form)
  • 3NF (Third Normal Form)
  • BCNF (Boyce-Codd Normal Form)
  • 4NF (Fourth Normal Form)
  • 5NF (Fifth Normal Form)

3. What is De-Normalization?

De-normalization is a database optimization technique that involves adding redundant data to improve read performance (at the cost of potentially slower write performance). It's often used to simplify complex queries.

4. Function vs. Stored Procedure.

Feature Function Stored Procedure
Return Value Must return a value Can return zero or more values
Parameters Typically only input parameters Can have input and output parameters
Exception Handling Supports exception handling (try-catch) Does not directly support try-catch blocks; other error handling mechanisms are used.

5. Collation Sensitivity.

Collation defines rules for string comparisons and sorting, considering case, accents, kana (Japanese characters), and character width.

6. Standby Servers.

Standby servers provide redundancy and high availability. Types include:

  • Hot standby: The standby server runs concurrently with the primary server, mirroring data in real-time.
  • Warm standby: The standby server runs in the background, with data synchronized periodically.
  • Cold standby: The standby server is activated only if the primary server fails.

7. Clustered vs. Non-Clustered Indexes.

Index Type Data Storage Number per Table
Clustered With the data rows One
Non-clustered Separate from data rows Many

8. HAVING vs. WHERE Clauses.

Clause When Used
WHERE Filters rows *before* grouping
HAVING Filters groups *after* grouping and aggregation

9. Recursive Stored Procedures.

A recursive stored procedure calls itself, allowing for iterative processing. SQL Server supports recursion with a limit on the nesting depth.

10. Advantages of Stored Procedures.

  • Improved performance (reduced network traffic).
  • Code reusability.
  • Enhanced security.
  • Easier maintenance (changes can be made without affecting calling applications).
  • Plan caching (improves performance).
  • Modularity.

11. One-to-One Relationships.

A one-to-one relationship means that one record in a table relates to only one record in another table. This can often be implemented within a single table to simplify the database structure.

12. Hiding SQL Server Instances.

(This section describes how to hide a SQL Server instance using the SQL Server Configuration Manager.)

13. CHECK Constraints.

CHECK constraints enforce rules on the values that can be inserted into a column, ensuring data integrity.

14. SQL Server Agent.

The SQL Server Agent schedules and manages automated tasks (jobs) within a SQL Server database.

15. COALESCE in SQL Server.

COALESCE returns the first non-NULL expression in a list.

16. SQL Server TCP/IP Port.

SQL Server typically runs on port 1433. This can be changed in the SQL Server Configuration Manager.

17. Authentication Modes.

  • Windows Authentication: Uses Windows accounts for authentication.
  • SQL Server Authentication: Uses SQL Server-specific usernames and passwords for authentication.
  • Mixed Mode: Allows both Windows and SQL Server authentication.

18. SQL Server Profiler.

SQL Server Profiler is a tool for monitoring and analyzing events within a SQL Server database. It tracks various database activities and helps in performance tuning and troubleshooting.

19. SQL Server Agent (Repeated from earlier).

The SQL Server Agent is a service that manages scheduled jobs within the SQL Server database.

20. Scheduled Jobs/Tasks.

Scheduled jobs automate tasks, running them at specified times or intervals.

21. DBCC Commands.

DBCC (Database Consistency Checker) commands perform checks on database objects (tables, indexes) to verify data integrity.

22. Renaming a Database.

Use the sp_renamedb stored procedure (e.g., sp_renamedb 'old_name', 'new_name').

23. Linking SQL Server to Other Databases.

Yes, using linked servers and OLE DB providers (from Microsoft).

24. Abstract Class vs. Interface.

Feature Abstract Class Interface
Method Implementation Can have implemented and abstract methods All methods are abstract
Inheritance Single inheritance Multiple inheritance

25. Application Object vs. Session Object.

An application object maintains state across the entire application. A session object maintains state for a specific user during a single session.

26. Primary Key vs. Unique Key (NOT NULL).

Both enforce uniqueness. A primary key cannot be NULL; a unique key can have one NULL value.

27. Value Type vs. Reference Type.

Type Memory Allocation Data Storage
Value Stack Actual value
Reference Heap Memory address of the value

28. Boxing and Unboxing in .NET.

Boxing converts a value type to a reference type (object). Unboxing converts a reference type back to a value type.

29. GET vs. POST Methods.

Method Data Location Caching Security History
GET URL Possible Less secure Stored in history
POST Request body Not possible More secure Not stored in history

30. Log Shipping.

Log shipping automates database backups and their restoration to a standby server for high availability.

31. Types of Replication in SQL Server.

  • Snapshot replication: Copies the database at a point in time.
  • Transactional replication: Replicates changes as transactions occur.
  • Merge replication: Handles updates from multiple sources.

32. Third-Party SQL Server Tools.

(This section lists some third-party SQL Server tools and their functions.)

33. Advantages of Third-Party Tools.

(This section lists advantages of using third-party tools over native SQL Server functionality.)

34. Collation Sensitivity (Repeated from earlier).

Case sensitivity, accent sensitivity, kana sensitivity, width sensitivity.

35. Hotfixes and Patches.

Hotfixes are small updates addressing specific issues in SQL Server.

36. Hotfixes and Patches in SQL Server.

Hotfixes address specific, often critical issues in SQL Server, frequently focusing on security vulnerabilities. A patch is a broader term encompassing hotfixes and other updates.

37. Common Trace Flags.

Trace flags in SQL Server modify server behavior. Some examples:

  • Deadlock information (1204, 1205, 1222)
  • Network file details (1807)
  • Connection logging (4013)
  • Disabling startup stored procedures (4022)
  • Disabling locking hints (8755)

38. Determining Active and Passive Nodes.

In a SQL Server high-availability environment, use SQL Server Management Studio or the Failover Cluster Manager to identify the active and passive nodes (the active node is the one currently handling requests).

39. FLOOR() Function.

The FLOOR() function rounds a number down to the nearest integer.

40. SIGN() Function.

SIGN() returns -1 if the input is negative, 0 if it's zero, and 1 if it's positive.

41. Subqueries in SQL Server.

A subquery (inner query) is a query nested within another query. It can be used in various parts of a query (SELECT, FROM, WHERE clauses).

42. Deleting a Table in SQL Server.

(This section details steps to delete a table using SQL Server Management Studio or T-SQL.)

43. Encryption Mechanisms in SQL Server.

SQL Server offers various encryption methods:

  • Transact-SQL functions
  • Asymmetric keys
  • Symmetric keys
  • Certificates
  • Transparent Data Encryption (TDE)

44. Magic Tables.

Magic tables are temporary tables automatically created and managed by SQL Server to store changes made through triggers. They are used internally for capturing changes before they are committed to the main tables.

45. CDC (Change Data Capture).

CDC in SQL Server captures data changes (inserts, updates, deletes) and makes this information available for other systems. It tracks the modifications made to your database tables so that this data can be used for replication, reporting, or other integration scenarios.

46. Types of Database Relationships.

  • One-to-one
  • One-to-many
  • Many-to-many

47. Abstract Class vs. Interface.

Feature Abstract Class Interface
Method Implementation Can have both abstract and concrete methods All methods are implicitly abstract
Inheritance Single inheritance Multiple inheritance

48. Application Object vs. Session Object.

(This section would compare application objects and session objects regarding their scope and usage.)

49. Primary Key vs. Unique Key (NOT NULL).

Both enforce uniqueness. A primary key cannot be NULL; a unique key allows one NULL value.

50. Value Type vs. Reference Type.

Type Memory Location
Value Type Stack
Reference Type Heap

51. Boxing and Unboxing.

Boxing converts a value type to a reference type (Object). Unboxing converts back to a value type. This is a feature of .NET and other languages with similar type systems.

52. GET vs. POST Methods (Repeated from earlier).

The differences between GET and POST methods in terms of data transmission, security, caching, and history are already described in the previous section.

53. Log Shipping.

Log shipping is a method for backing up and restoring database transaction logs to a standby server for high availability and disaster recovery.

54. Types of Replication (Repeated from earlier).

Snapshot replication, transactional replication, and merge replication.

55. Third-Party SQL Server Tools

Third-party SQL Server tools are software solutions developed by independent vendors to enhance the functionality of SQL Server. These tools provide additional features that may not be available in the native SQL Server suite, helping with tasks such as performance tuning, data migration, backup, and reporting. Some popular third-party tools include:

  • Redgate SQL Toolbelt - A set of tools for database development, deployment, and versioning.
  • ApexSQL - A collection of tools for SQL Server management, reporting, and auditing.
  • SQL Sentry - Provides performance monitoring and tuning for SQL Server.
  • dbForge Studio - A comprehensive IDE for SQL Server that includes database design, query building, and reporting tools.
  • Toad for SQL Server - A powerful tool for SQL Server development and administration, focusing on database management and optimization.

56. Advantages of Third-Party Tools

While SQL Server comes with many built-in features, third-party tools offer a range of advantages that can enhance the user experience, improve efficiency, and provide more specialized capabilities. Some key benefits include:

  • Enhanced Functionality: Many third-party tools offer features that are not available in SQL Server, such as advanced reporting, in-depth performance analysis, and automated database maintenance.
  • User-Friendly Interfaces: These tools often come with intuitive, graphical interfaces that make complex tasks easier to perform, saving time and reducing the potential for errors.
  • Performance Monitoring: Third-party tools provide better insights into system performance, allowing DBAs to quickly identify and resolve issues, improving uptime and performance.
  • Backup and Recovery: Several third-party tools offer advanced backup solutions that support automated backups, point-in-time recovery, and better disaster recovery management.
  • Improved Security: Many third-party tools include enhanced security features such as auditing, encryption, and role-based access controls that go beyond the native SQL Server capabilities.
  • Ease of Migration: Third-party tools often include features that simplify data migration and integration with other platforms, which can be a complex task within SQL Server alone.

57. Collation Sensitivity (Repeated from earlier).

Case, accent, kana, and width sensitivity.