SQL Server Q&A Topic 1: SQL Server Fundamentals

SQL Server Interview Questions & Answers

Topic 1: SQL Server Fundamentals

This section covers fundamental SQL Server concepts that every developer, database administrator and database professional should understand before moving into architecture, performance tuning, high availability and advanced troubleshooting topics.

1. What is SQL Server?

Answer:
SQL Server is Microsoft's relational database management system (RDBMS). It is used to store, manage, secure and retrieve structured data for applications and enterprise systems.

SQL Server supports relational databases, transactions, indexing, stored procedures, security, backup and recovery, high availability, performance tuning and business intelligence workloads.

2. What is the difference between SQL Server and SSMS?

Answer:
SQL Server is the database engine responsible for storing and processing data.

SQL Server Management Studio (SSMS) is a graphical management tool used by DBAs and developers to connect to SQL Server, execute T-SQL queries, manage databases, configure security and perform administrative activities.

In simple terms:

• SQL Server = Database Engine
• SSMS = Management Tool

3. What is a SQL Server instance?

Answer:
A SQL Server instance is an installation of the SQL Server Database Engine that operates independently with its own configuration, system databases, logins, databases and memory settings.

A server can host a default instance and multiple named instances.

Example:

SERVER01
SERVER01\SQLPROD
SERVER01\SQLTEST

4. What are the SQL Server system databases?

Answer:
SQL Server contains several system databases that are required for instance and database operations.

• master - Stores instance-level configuration, logins and information about databases.
• model - Acts as the template used when new databases are created.
• msdb - Stores SQL Server Agent jobs, schedules, backup history and other operational information.
• tempdb - Provides temporary workspace for SQL Server operations.
• Resource - A hidden, read-only database containing system objects included with SQL Server.

5. What is tempdb?

Answer:
tempdb is a SQL Server system database used as temporary workspace by the Database Engine.

It is commonly used for:

• Temporary tables
• Table variables
• Sorting and hashing operations
• Query spills
• Worktables
• Row versioning
• Snapshot-based isolation
• Index creation and maintenance operations

tempdb is recreated whenever the SQL Server service starts, so objects stored inside it do not survive a SQL Server restart.

6. What is the difference between MDF, NDF and LDF files?

Answer:

• MDF - The primary data file of a SQL Server database.
• NDF - A secondary data file. A database can have multiple secondary data files.
• LDF - The transaction log file that records database transactions and changes required for recovery.

A SQL Server database must have at least one data file and one transaction log file.

7. What is a filegroup?

Answer:
A filegroup is a logical container that groups one or more SQL Server data files.

Filegroups can be used to organize database objects and support administrative strategies such as placing specific tables or indexes in defined storage groups and implementing piecemeal backup and restore strategies.

Every database contains a PRIMARY filegroup, and additional user-defined filegroups can be created when required.

8. What is a transaction?

Answer:
A transaction is a logical unit of work containing one or more database operations that should be completed together.

A transaction can either be committed successfully or rolled back when an operation fails.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountID = 101;

UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountID = 202;

COMMIT TRANSACTION;

Transactions are fundamental to maintaining data consistency and integrity.

9. Explain ACID properties.

Answer:
ACID describes four important properties that make database transactions reliable.

• Atomicity - The transaction completes entirely or none of its changes are committed.
• Consistency - A transaction moves the database from one valid state to another while respecting defined rules and constraints.
• Isolation - Concurrent transactions are controlled so that their interactions provide the consistency guarantees required by the selected isolation level.
• Durability - Once a transaction is committed, SQL Server provides mechanisms to recover the committed changes after failures.

10. What is a heap?

Answer:
A heap is a SQL Server table that does not have a clustered index.

The table's data pages are therefore not organized through a clustered index B-tree.

Heaps are not automatically bad, but they need to be chosen for the right workload. Tables that experience updates can develop forwarded records, and some access patterns may require more page reads than a well-designed clustered index.

Interview Quick Recap

• SQL Server is the database platform; SSMS is a management tool.
• An instance is an independent installation of the SQL Server Database Engine.
• Key system databases include master, model, msdb and tempdb.
• Data is stored in data files while transactional information is recorded in log files.
• Filegroups logically organize data files.
• Transactions provide reliable units of database work.
• ACID stands for Atomicity, Consistency, Isolation and Durability.
• A heap is a table without a clustered index.

Next Topic:
Topic 2: SQL Server Architecture & Internals

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning