Databases

Flashcards

Relational databases through a .NET developer’s eyes: queries and procedures, indexes and plans, transactions and locking, EF Core, how PostgreSQL differs, schema design and ETL. The main dialect is T-SQL (SQL Server); where PostgreSQL behaves differently, it is called out.

Before this category: Collections and LINQ (for the EF Core topic).

SQL and T-SQLJOINs, grouping and logical query order, window functions, CTEs, procedures and functions, set-based thinking.

Indexes and query plansClustered and nonclustered indexes, covering indexes, SARGability, execution plans, parameter sniffing.

Transactions and lockingACID, isolation levels, RCSI and SNAPSHOT, locks and deadlocks, optimistic concurrency.

EF CoreDbContext and migrations, change tracking, loading related data and N+1, performance, EF vs Dapper.

PostgreSQL vs SQL ServerDialect differences, MVCC and VACUUM, index types, JSONB, EXPLAIN ANALYZE.

Design and operationsNormalization, keys, ETL and SSIS, reporting, partitioning, zero-downtime schema changes.