SQL Server Indexing for Developers and DBAs

Zenler Player

Your course is loading. Hang tight.

Who is this course for? Preview
Who is Greg? Preview
Why focus on indexing? Preview
What will I learn in this course? Preview
Configuring your lab environment

Why index at all?
Most common index types
Specialty index types
Introduction to PopkornKraze

Files, extents, and pages
What is a heap?
Tables as heaps
What is a clustered index?
Tables with clustered indexes
Non-clustered indexes
Non-clustered indexes over heaps
Non-clustered indexes over clustered indexes
Lab 2: Table and index structures
Lab 2: Answers
Quiz 2: Table and index structures

Primary keys
Default structure for primary keys
Unique constraints
Foreign keys
Lab 3: Indexing for constraints
Lab 3: Answers
Quiz 3: Indexing for constraints

Lookups
Cost of lookups
Covering indexes
Included columns
Notes on included columns
Clustering key as an included column
Indexes for queries that join multiple tables
Lab 4: Covering queries with indexes
Lab 4: Answers
Quiz 4: Covering queries with indexes

Choosing a clustering key
Integers and big integers
Identity columns
Sequences
String values
Bits (boolean values)
Filtered indexes
Unique identifiers (GUIDs)
Lab 5: Clustering keys and data types
Lab 5: Answers
Quiz 5: Clustering keys and data types

Index selectivity and density
Column order within an index
Ascending vs descending
Index uniqueness
Index depth
Statistics
Index fragmentation
Heap fragmentation
FILLFACTOR and PAD_INDEX
Lab 6: Index characteristics
Lab 6: Answers
Quiz 6: Index characteristics

Reasonable starting point
Tracing and analyzing workloads
Missing indexes - DTA
Missing indexes - DMVs
Indexed views
Lab 7: Index design strategies
Lab 7: Answers
Quiz 7: Index design strategies

Unused indexes
Duplicate and subset indexes
Merge covering indexes
Lab 8: Rationalizing indexes
Lab 8: Answers
Quiz 8: Rationalizing indexes

Summary and further steps