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.