Indexes and the Microsoft SQL Server

Explore table structures on the Esco server, including heaps, clustered indexes (primary keys), non-clustered indexes with included columns, and notes on column store and Exham indices.

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 how a clustered index stores data in sorted order as the actual table, organized as a B-tree with root, intermediate, and leaf nodes, and how page splits impact storage.

Explore the non-clustered index, which preserves data order in the clustered index, uses a B-tree, and stores index pages with values and pointers to data in the clustered index.

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.

Indices improve query performance in SQL Server but require storage and can slow inserts and updates; extend existing indexes when possible, and design a limited index set for each application.

Create a filtered index by adding a column and defining a filter, yielding a significantly smaller index than the full table, and understand the need for expertise to run queries.

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.

Explore operator properties in execution plans, comparing actual and estimated plans, physical and logical operations, rows and costs, and index usage with seek predicates and constraints.

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.

Apply index optimization to execution plans by indexing the email column and the first name last name set, reducing query cost and improving response times, with guidance on included columns.

Identify and prune over indexed databases by analyzing index usage, deactivating suspicious indices, and rebuilding them to restore performance, while monitoring updates and redundancy in live systems.

Identify queries with long execution times by examining high logical reads in management studio to uncover missing indexes after removing unnecessary indices.

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.

Analyze a live customer database with a self-written index analyzer to identify duplicates and unnecessary indices, assess usage, and plan reorganization for better query performance.

Apply index knowledge from the course to speed up SQL Server queries and optimize performance. Celebrate your new understanding of indices and aim to impress your boss with measurable results.