Video summary

SQL Indexes (Visually Explained) | Clustered vs Nonclustered | #SQL Course 35

Main summary

Key takeaways

Technology

What the video covers (SQL index optimization)

  • Goal: Improve database query performance by using indexes (especially on large tables).
  • Key premise: An index is a data structure that helps the database quickly locate rows instead of scanning everything.

Indexes: types and design tradeoffs

The speaker divides index concepts into three categories:

  1. By structure (logical organization):

    • Clustered index
    • Nonclustered index

  2. By storage layout (physical organization):

    • Row-store index
    • Column-store index

  3. By uniqueness/function:

    • Unique index
    • Filtered index
  • Tradeoff emphasized: Some indexes optimize reads, while others increase cost on INSERT/UPDATE operations.

Deep dive: Heap vs Clustered vs Nonclustered (main technical analysis)

1) No index → Heap

  • SQL Server stores table data in data files made of fixed-size pages (stated as 8 KB).
  • A page includes:
    • Page header (metadata like file/page id, etc.)
    • Data region (rows)
    • Offset array (helps locate row start positions within the page)
  • Without a clustered index, the table becomes a heap:
    • Rows are stored randomly / unsorted across pages.
    • Inserts are fast.
    • Reads are slow because SQL may do a full table scan: page-by-page, row-by-row.

2) Clustered index

  • Creating a clustered index causes SQL Server to physically sort and rearrange table rows by the clustered key.
  • Structure described:
    • A B-tree (also referred to as “P-tree” in subtitles)
    • Leaf level: contains the actual data pages (sorted rows)
    • Index/intermediate levels: contain key + pointer to the next level
  • Query behavior (example: searching for ID = 14):
    • SQL traverses the tree with only a few “jumps” (root → intermediate → leaf).
    • It typically needs to read fewer data pages than a heap.

When clustered index is recommended (speaker’s rules):

  • Prefer columns with:
    • Uniqueness (e.g., primary keys)
    • Stable values (not frequently updated)
  • Best for range queries (e.g., values between 1 and 20).

3) Nonclustered index

  • Creating a nonclustered index does not reorder the base table pages.
  • Structure described:
    • Also uses a B-tree
    • Leaf level: contains index entries, not the actual row data
    • Each leaf entry uses a row identifier (RID), composed of:
      • File/page number
      • Row offset within the page
  • Query behavior:
    • SQL traverses tree levels to find the matching key entry
    • Then reads the actual row from the base table using the RID.
  • Compared to clustered:
    • Clustered tends to be faster for reads
    • Nonclustered tends to be better/less harmful for writes (since it doesn’t require physical re-sorting of rows)

When nonclustered index is recommended:

  • Columns used in:
    • Search conditions (exact matches)
    • JOINs (especially when not on the primary key)

Side-by-side differences explicitly called out

  • Number of clustered indexes: only one per table (because data can only be sorted in one physical order).
  • Number of nonclustered indexes: many (multiple allowed).
  • Read performance:
    • Clustered usually faster than nonclustered (fewer layers to reach the data)
  • Write performance:
    • Clustered can be slower due to maintaining sorted physical order
    • Nonclustered is less disruptive (stores pointers/RIDs)

Practical SQL Server examples & “tutorial” style steps

Where indexes are shown (SQL Server UI)

  • Uses Object Explorer
  • Expands tables (example: SalesDB.customers)
  • Shows an Indexes folder
  • Notes that SQL Server may create a clustered index automatically for a PRIMARY KEY.

Creating tables for demonstration

  • Creates a new table from an existing one:
    • SELECT * FROM sales.customers INTO ... (named similarly to TB customers / later DB customers)
  • Shows that the new table is a heap (no indexes), and a query triggers a full scan.

Creating/dropping clustered index

  • Syntax concept:
    • CREATE CLUSTERED INDEX index_name ON table_name (column)
  • Demonstrates:
    • Creating a clustered index on customer_id
    • Attempting to create a second clustered index results in an error (“cannot create more than one clustered index”).
    • To change clustered key:
      • DROP INDEX ...
      • then create the correct clustered index.

Creating nonclustered indexes

  • Syntax concept:
    • CREATE NONCLUSTERED INDEX ... (nonclustered is default if not specified)
  • Demonstrates creating:
    • Nonclustered index on last_name
    • Another nonclustered index on first_name
  • Notes that nonclustered indexes can be multiple and don’t require dropping each time.

Composite indexes + key rule: order matters

  • Composite index: multiple columns in one index.
  • Emphasized rule: index column order should match how queries filter.
  • Also emphasized Leftmost prefix rule:
    • SQL can use the composite index if the query uses the leftmost leading columns.
    • If the query skips a leftmost column (e.g., searching by later columns only), the composite index may not be used.

Main speakers / sources

  • Primary speaker: “bar” (host), a person who “leads data projects in big companies like Mercedes-Benz.”

Original video