Index Structures Explorer

▶ DRILL

B+ tree index visualizer for dbo.employee. Switch between the clustered index (PK on employee_id) and a non-clustered index on employee_name. Run index seeks and full scans, observe key lookups, and see how INCLUDE eliminates them.

↓ south-park-orders.sql— includes dbo.employee (CREATE TABLE, both indexes, all 30 rows) plus the full shared schema. Run in any SQL Server development database to reproduce the exact same tree against the real engine.

CREATE TABLE dbo.employee (

employee_id INT NOT NULL,

employee_name NVARCHAR(128) NOT NULL,

birth_year INT NOT NULL,

CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (employee_id)

);

no filter (full scan)

SELECT employee_id, employee_name, birth_year

FROM dbo.employee

WHERE employee_id = 1— index seek on PK

slowfast

Select a row and press RUN to seek, or check no filter to scan.

CLUSTERED INDEX on employee_id — leaf pages store all columns (employee_id, employee_name, birth_year)

■ internal — routing only■ CX leaf: id / name / year■ NC leaf: name / row-locator─ ─ NextPage pointerORDER=3 · 30 employees · 4 levels

# index seekTraverses root → internal → leaf. One page read per level. O(log n). Triggered by a WHERE predicate on the index key.

# index scanReads all leaf pages sequentially via NextPage pointers. No predicate — or a predicate on a non-indexed column.

# key lookupWhen a non-clustered index seek finds the row locator (clustered key), it must seek the clustered index again to fetch columns not stored in the NC index.

# covered indexINCLUDE adds columns to NC leaf pages — query can be satisfied entirely from the NC index, eliminating the key lookup.

# noteSimplified model: ORDER=3, 30 rows, birth_year as INT. Real SQL Server uses 8 KB pages, variable row sizes, fill factor, and row-level locking.