Understanding relational databases remains essential for senior .NET engineers regardless of ORM usage. Key concepts: indexes (B-tree structures that trade write overhead for read speed), transactions (ACID guarantees: Atomicity, Consistency, Isolation, Durability), and query execution plans (the database's optimised strategy for executing your query — essential for diagnosing slow queries). The most important index knowledge: a clustered index determines physical row order (one per table, default is primary key); a non-clustered index is a separate B-tree with pointers to the data rows.

Query performance: avoid SELECT * (loads unnecessary columns, prevents index-only scans), understand SARGable predicates (Search ARGument ABLE — predicates that can use indexes: column = @val is SARGable; YEAR(column) = 2024 is not, because wrapping a column in a function prevents index use), and know when covering indexes eliminate key lookups. Pagination with OFFSET/FETCH degrades at high offsets — keyset pagination (WHERE Id > lastId) is O(1) regardless of page depth.

Isolation levels control how transactions see concurrent changes: READ UNCOMMITTED (dirty reads — sees uncommitted data), READ COMMITTED (default SQL Server — no dirty reads, but non-repeatable reads possible), REPEATABLE READ (same row reads return same value, but phantom inserts possible), SERIALIZABLE (fully isolated — no phantoms, maximum blocking), SNAPSHOT (SQL Server: optimistic — reads from a point-in-time snapshot, writers don't block readers). Most applications run at READ COMMITTED or SNAPSHOT to balance correctness and performance.