SQL Server: The complete course on Indexes (4 h of class)

Explore how a covering index speeds queries by avoiding lookups. Compare the clustered index on age and SSN with another index, and limit columns to improve execution plans.

consolidate redundant indexes by grouping last name, first name, and middle initial into a single covering index, then test with execution plans to optimize storage usage.

Explore how a unique index and unique constraint affect performance, including cluster index concepts, execution plans, and avoiding duplicate indexes in SQL Server.

Learn how duplicate indexes harm performance and maintenance in SQL Server, and discover best practices to avoid creating unnecessary indexes while improving query speed.

Explore how over indexing harms performance, inflates maintenance time and disk usage, and why monitoring indexes and avoiding unnecessary indexes is essential for transactional workloads.

Discover how forcing clustered or nonclustered indexes changes performance, switching between scans and seeks. Learn to create covering indexes to speed up queries in SQL Server.

Deactivate a cluster index on the primary to boost insert performance, as shown by a reduction from 12 minutes 20 seconds to 1 minute 2 seconds; test scenarios before reactivating.

Analyze how a clustered index on a newid column impacts performance by inspecting fragmentation, page density, and index statistics to inform optimal indexing strategies.

Demonstrate how a filtered index targets only relevant values to shrink index size and speed queries on targeted ranges.

Demonstrates creating and using filtered indexes, and shows how execution plans choose or miss these indexes. Explore forcing index usage, comparing covering indexes, and recognizing anomalies in query plans.

Choose the clustered index to optimize performance on high-volume data, and schedule nightly maintenance to rebuild fragmentation; remember a unique constraint can create a clustered index, so keep it narrow.

See how the columnstore index boosts performance on very large fact tables in a data warehouse, using compression and batch mode processing to store data efficiently in memory.

The lecture demonstrates creating an indexed view in SQL Server by adding a clustered index first, then a nonclustered index, to boost queries over large aggregated data.

Understand how fragmentation affects index scans, disk space, and page splits, and learn when to reorganize or rebuild indexes, considering fill factor and buffer pool impact.

Demonstrate how inserting data in a SQL Server index causes page splits and fragmentation, altering contiguity from base level through intermediate to leaf pages.

Learn to enable extended events, set up a session, and monitor page splits on a clustered index by inserting test data and querying results to observe split behavior.

Set the fillfactor to reserve free space on index pages, typically around 70 percent, to prevent page splits during inserts and when creating or reorganizing indexes.

Learn how to manage SQL Server indexes by choosing between reorganize and rebuild, assessing fragmentation, updating statistics, and applying online or offline maintenance plans.

Learn how to rebuild SQL Server indexes with abort_after_wait to manage blocking and timeouts, including blockers, transaction handling, and rollback during peak hours.

Discover what sargable means on SQL Server and how index seeks versus scans affect query performance and the execution plan, with tips to rewrite expressions for better efficiency.

Consolidate duplicates and indexes to save space and time, use a single index that covers multiple needs, and apply clustered and covering indexes for critical queries.