Indexes and the Microsoft SQL Server

Learn to explore SQL Server indexing by building clustered and non-clustered indexes, comparing heaps, and evaluating predicates, density, and leftmost column ordering for efficient queries.

Explore how heaps are implemented on the escrowed server, including data pages and the index allocation map, and why table scans on heaps provide poor performance and should be avoided.

Understand included columns in non-clustered indexes and how they carry payload data. Attached columns are not part of the index and can change without resorting.

Examine how data insertion causes page splits and fragmentation that affect clustered and non-clustered indices, and how reorganize or rebuild via Management Studio mitigates this when fragmentation reaches 10 percent.

See how the Microsoft SQL Server optimizer builds execution plans for queries, uses statistics and cardinality to decide index usage, and stores costed plans in the plan cache.

Explore operators in execution plans, including filter, scan, and sort operators, and join types like merge join, nested loop, and hash match, with notes on missing indexes.

Learn how statistics power cardinality estimation and affect query plans, and how outdated index statistics mislead the optimizer into suboptimal executions; manual updates with Espey underscore updates are possible.

Merge and prune SQL Server indices by checking containment and field order to remove redundant ones. Balance fewer columns with order relevance and included columns to align with query needs.