Entry MS SQL Interview Questions

MS SQL Server is Microsoft’s enterprise relational database system used in finance, e-commerce, SaaS, ERPs, CRMs, and large web applications. It provides secure, consistent, and high-performance data storage.

A database stores all data objects. A table contains structured data. A row represents one record. A column represents a specific data field with a defined type.

A primary key uniquely identifies each row. It ensures uniqueness, improves indexing, and maintains reliable data integrity.

A foreign key creates a relationship between tables and prevents orphaned or invalid data by enforcing referential integrity.

Constraints like primary key, foreign key, unique, check, not null, and default rules ensure accurate, valid, and reliable data.

Normalization organizes data to eliminate redundancy, improve consistency, and reduce anomalies during insert, update, or delete operations.

1NF: No repeating groups; atomic values.
2NF: Remove partial dependencies.
3NF: Remove transitive dependencies.

Denormalization introduces controlled redundancy to improve read performance, especially in reporting and analytics systems.

An index speeds up data searches by avoiding full table scans. Too many indexes slow down inserts/updates due to maintenance.

Clustered index: Defines physical row order.
Non-clustered index: A separate structure pointing to actual rows.

A view is a virtual table created from a query. Used for simplicity, security, reusability, and hiding complex queries.

A stored procedure is a precompiled SQL code block used for business logic, improving performance and reducing network overhead.

UDFs return calculated values based on parameters. Used for reusable logic, validation, and transformations.

A transaction ensures a group of SQL operations is treated as a single unit—either all succeed or all fail (ACID compliance).

Atomicity: All-or-nothing.
Consistency: Valid states only.
Isolation: No interference between transactions.
Durability: Data persists after commit.

A deadlock occurs when transactions block each other while waiting for resources. SQL Server resolves it by killing one transaction.

Locking controls concurrent access using shared (read), exclusive (write), and update locks to maintain data consistency.

A schema is a logical container grouping tables, views, and procedures for better organization and security.

DELETE: Row-by-row removal, fully logged.
TRUNCATE: Deallocates pages, extremely fast, minimal logging.

An execution plan shows how SQL Server executes a query—joins, indexes, scans, seeks. It is vital for tuning slow queries.

Indexing improves data retrieval speed by avoiding full table scans.

Indexes act like optimized search maps that help SQL Server locate rows faster.

However, too many indexes slow down write performance due to maintenance overhead.

SQL Server chooses access methods based on filters, available indexes, and cost estimates.

Table Scan: Reads all rows when no useful index exists.

Index Scan: Reads all index entries for broad filters.

Index Seek: Most efficient; jumps directly to matching rows.

A composite index includes multiple columns, used when queries filter or sort using those column combinations.

The order of columns is important because SQL Server can only efficiently use leading key columns.

Bookmark lookup occurs when SQL uses a non-clustered index but must fetch extra columns from the base table.

This becomes slow when many rows match, increasing I/O and reducing performance.

A covering index contains all columns needed for a query.

SQL Server can return results from the index alone without touching the table.

This greatly improves performance for read-heavy queries.

Statistics describe data distribution in columns.

They help the optimizer estimate row counts and choose efficient execution plans.

Outdated statistics produce inaccurate row estimates.

SQL Server may choose inefficient join types or scans, causing slow performance.

Regular statistic updates maintain optimal plans.

SQL Server caches execution plans for reuse.

Parameter sniffing occurs when a cached plan works well for one parameter but poorly for another due to data skew.

An execution plan is SQL Server’s strategy for running a query.

The optimizer evaluates multiple plan options and picks the lowest-cost one.

Estimated plans show predicted row counts before execution.

Actual plans include real runtime row counts and performance metrics.

Cardinality estimation predicts how many rows a query will process.

Accurate estimates are critical for choosing efficient join types and memory usage.

Fragmentation occurs when index pages become disordered, slowing scans and seeks.

Fill factor controls how much free space to leave during index creation to reduce fragmentation.

The optimizer evaluates many query strategies using statistics and metadata.

It selects the lowest estimated cost plan.

A filtered index stores only rows matching a condition.

It reduces index size and improves seek performance for selective queries.

Hints override optimizer decisions by forcing specific behavior.

They should be used only when the optimizer consistently chooses suboptimal plans.

A hotspot occurs when many concurrent operations target the same physical index location.

This causes locking and contention, reducing performance.

Query Store tracks query history, execution plans, and performance trends.

It helps diagnose regressions and enforce stable plans.

Seek predicates allow precise navigation through an index.

Residual predicates apply filters after the seek when the index does not fully cover the query.

SQL Server uses hash match for large, unsorted datasets where hashing is cheaper than repeated seeks.

Poor row estimates may also cause SQL to choose hashing, sometimes leading to spills.