TR-C cheat sheet

A transaction is a unit of work that is atomic, consistent, isolated, and durable (ACID); each property maps to a specific guarantee the system must enforce.

Atomicity is implemented by undo/rollback of partial changes; durability is implemented by writing a redo log to stable storage before commit is acknowledged.

Isolation levels are defined by which read anomalies they permit: read uncommitted allows dirty reads, read committed allows non-repeatable reads, repeatable read allows phantoms, serializable allows none.

A dirty read observes uncommitted data from another transaction; a non-repeatable read sees the same row change value between two reads; a phantom sees new rows matching a predicate.

Write-ahead logging rule: the log record for a change must reach stable storage before the changed data page does.

Two-phase locking has a growing phase (locks acquired, none released) and a shrinking phase (locks released, none acquired); strict 2PL holds all exclusive locks until commit.

Lost update occurs when two transactions read the same value and both write based on it; optimistic concurrency prevents it by validating a version or timestamp at commit.

Deadlock requires a wait-for cycle; prevention orders resources consistently, while detection builds a wait-for graph and aborts a victim.

Normalization decision rule: 1NF removes repeating groups, 2NF removes partial dependencies on part of a composite key, 3NF removes transitive dependencies on non-key attributes.

Denormalization is chosen when read latency or join cost dominates and controlled redundancy with a defined refresh mechanism is acceptable.

A star schema places a central fact table (measures plus foreign keys) around denormalized dimension tables; a snowflake schema normalizes dimensions into hierarchies.

Fact table grain must be declared before adding measures; every measure must be true at that grain or it will be double-counted on aggregation.

Slowly changing dimension Type 1 overwrites history, Type 2 adds a new row with validity dates and a current flag, Type 3 adds a prior-value column.

Cardinality drives index and join design: a one-to-many relationship puts the foreign key on the many side; many-to-many requires a junction table with a composite key.

Capacity planning estimate: storage = rows x average row width x growth factor, plus index overhead, plus log and backup copies.

Partitioning strategy decision: range partitioning suits time-series purge and archival, hash partitioning spreads write load evenly, list partitioning suits low-cardinality categorical filters.

A clustered index defines physical row order and exists once per table; a nonclustered index stores a separate key structure with a pointer or included columns.

Composite index column order matters: equality predicates first, then range predicates, then sort columns; a query skipping the leading column generally cannot seek.

A covering index includes every column the query references, eliminating key lookups; INCLUDE columns add payload without widening the seek key.

Parameterization separates SQL text from values, enabling plan reuse and preventing injection; ad-hoc literal SQL causes plan cache bloat.

A foreign key enforces referential integrity and requires a matching index on the referencing side to avoid lock escalation and slow parent deletes.

Connection pooling reuses physical connections; pool exhaustion occurs when connections are leaked or held during long operations, not when max pool size is merely reached.

Isolation level is set per session or per transaction; enabling read-committed snapshot changes readers to use row versioning in tempdb instead of shared locks.

Bulk load performance improves with minimal logging, larger batch commits, and disabling nonclustered indexes during load, then rebuilding them.

Recovery models: full requires log backups for point-in-time recovery, bulk-logged minimally logs bulk operations, simple truncates the log after checkpoint.

A full backup alone restores only to the backup time; point-in-time recovery requires a full backup plus a continuous chain of log backups.

The transaction log can only be truncated by a log backup (full/bulk-logged) or a checkpoint (simple); a growing log indicates a broken backup chain or an open long transaction.

RTO is the maximum acceptable downtime; RPO is the maximum acceptable data loss measured in time.

A differential backup captures all extents changed since the last full backup; a restore needs the latest full plus the latest differential plus subsequent logs.

Failover clustering provides instance-level high availability with shared storage; availability groups replicate databases and support readable secondaries.

Job scheduling must avoid overlap: a job that runs longer than its interval creates concurrent executions unless a guard prevents re-entry.

Index maintenance decision: rebuild when fragmentation is high and the index is large, reorganize for moderate fragmentation, and skip tiny indexes where page count is low.

Blocking is a lock wait resolved by the holder finishing; deadlock is a cycle resolved by the engine aborting a victim, reported as error 1205 in SQL Server.

A long-running transaction holds locks and prevents log truncation; identify it by session, open transaction time, and log space used.

Wait statistics classify bottlenecks: PAGEIOLATCH waits indicate storage latency, LCK waits indicate blocking, CXPACKET or parallelism waits indicate skew or missing indexes.

A missing index suggestion is advisory; validate by testing, since the suggested index may duplicate an existing one or add write overhead.

Parameter sniffing causes a cached plan tuned for atypical values; remedies include recompile hints, OPTIMIZE FOR, or plan guides.

Query Store or the plan cache reveals plan regressions; compare estimated versus actual row counts to find stale statistics.

Statistics should be updated after large data changes; ascending-key columns benefit from filtered or higher-sample statistics to avoid underestimation.

Backup verification requires a restore test, not just a successful backup job; CHECKDB or equivalent consistency checks detect corruption that backups would otherwise preserve.

Authentication proves identity; authorization grants permissions. Roles bundle permissions and are granted to principals rather than to individuals.

Least privilege means granting the minimum permission at the narrowest scope: object-level over schema-level over database-level over server-level.

SQL injection is prevented by parameterized queries and least-privilege accounts, not by input escaping or client-side validation alone.

Data at rest uses TDE or disk encryption; data in transit uses TLS with certificate validation; column-level encryption or masking handles sensitive fields.

Dynamic data masking hides values in results but does not encrypt stored data; users with unmask permission still see plaintext.

Row-level security filters rows by predicate at query time and applies to all access paths, including ad-hoc queries by privileged users unless exempted.

Auditing records who did what and when; it must be tamper-resistant and retained per policy, and it must capture privileged operations such as permission changes.

Separation of duties requires that the person who changes production data is not the same person who approves or audits the change.