Index: clustered vs non-clustered, composite, dan covering (INCLUDE)

Sebelum mulai: setup lab sudah dijalankan (ROADMAP, "Hari 4"). Setup sengaja hanya membuat dua index di dbo.Product: PK_Product (clustered, dari PRIMARY KEY) dan UQ_Product_Name (non-clustered, dari UNIQUE). Tidak ada index pada CategoryId, karena FOREIGN KEY tidak membuat index otomatis (lesson 0019). Padanan Node: ini sama dengan @@index([categoryId]) di schema Prisma atau CREATE INDEX di migration knex; bedanya, di interview .NET Anda diharapkan bisa menjelaskan apa yang terjadi di dalamnya.

1. Clustered vs non-clustered: analogi buku

Microsoft sendiri memakai analogi buku: index adalah "a sorted list of keywords and next to each keyword is a set of page numbers". Index rowstore disimpan sebagai B+ tree. ^[1]

Kutipan tabel: ^[2]

• "If a table has no clustered index, its data rows are stored in an unordered structure called a heap." ^[2]
• PRIMARY KEY membuat unique index yang "by default ... is clustered". Clustered key yang ideal: sempit, unik, selalu naik, tidak berubah, NOT NULL; "a single int or bigint not nullable column ... populated by an IDENTITY clause" memenuhi semuanya. GUID acak tidak: 16 byte dan tidak selalu naik. ^[1]
• Kenapa sempit penting: "The clustered index key is a part of any nonclustered index on the same base table." Clustered key yang lebar membuat semua index ikut gemuk. ^[1]

2. Key lookup, dan kenapa index kadang diabaikan

CREATE INDEX IX_Product_CategoryId ON dbo.Product (CategoryId);

SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 6;   -- 20 baris

Index menemukan 20 entri Category 6 dengan Index Seek, tapi entri itu hanya berisi CategoryId dan Id. Name dan Price ada di clustered index, jadi engine "melompat ke halamannya" sekali per baris: Key Lookup. "The Key Lookup operator is a bookmark lookup on a table with a clustered index ... Key Lookup is always accompanied by a Nested Loops operator." ^[3]

• Untuk sedikit baris, itu murah: "A low number of executions is typically not a performance concern." ^[3]
• Untuk ±2.500 baris (Category 2), ribuan lookup lebih mahal daripada membaca seluruh tabel sekali, jadi optimizer memilih Clustered Index Scan dan index baru diabaikan: "a table scan could be the most efficient method if ... the result set of the query is a high percentage of rows from the table." ^[2] Titik peralihan ini sering disebut tipping point. Obatnya bukan hint, tapi index yang tidak butuh lookup (bagian 3).
• Pada heap, operator yang sama bernama RID Lookup. ^[3]

3. Covering index dengan INCLUDE

CREATE INDEX IX_Product_CategoryId_Covering
    ON dbo.Product (CategoryId)          -- key: kolom predikat, diurutkan, bisa di-seek
    INCLUDE (Name, Price);               -- hanya di leaf: diambil, tidak difilter

• "A covering index is a nonclustered index that satisfies all data access by a query directly without accessing the base table." Pola resminya: kolom SARGable di key, "non-SARGable columns as included columns". ^[1]
• Kapan INCLUDE, kapan key: "If a column must be retrieved by a query, but isn't used in the query predicates, aggregations, and sorts, add it as an included column and not as a key column." Kolom INCLUDE tidak dihitung ke batas key (16 kolom, 900 byte) dan boleh bertipe seperti nvarchar(max). ^[1] Sintaksnya: "a subset of non-key columns can be included using the INCLUDE clause". ^[7]
• Id tidak perlu ditulis: clustered key "automatically added to each nonunique nonclustered index on the table". ^[1]
• Cara membuktikan: plan menjadi Index Seek tanpa Key Lookup, dan logical reads turun. Jangan berlebihan: "Avoid creating a covering index with too many columns", karena storage dan biaya tulis ikut naik. ^[1]

4. Composite index: urutan kolom menentukan segalanya

-- Query: 10 Product termahal di satu Category
-- SELECT TOP (10) Name, Price FROM dbo.Product WHERE CategoryId = @c ORDER BY Price DESC;
CREATE INDEX IX_Product_CategoryId_Price
    ON dbo.Product (CategoryId, Price DESC)
    INCLUDE (Name);

Index composite diurutkan seperti buku telepon: dulu per kolom pertama, lalu per kolom kedua di dalam setiap nilai kolom pertama. Contoh resmi: index (LastName, FirstName) berguna untuk WHERE LastName = 'Smith', tapi "the query optimizer wouldn't use the index for a query that searched only on WHERE FirstName = 'Jane'". ^[1]

Aturan urutan dari Microsoft: kolom yang dipakai di predikat equality, inequality, BETWEEN, atau join "should be placed first", lalu kolom lain "from the most distinct to the least distinct". ^[1] Versi praktis untuk interview: equality dulu, lalu range atau ORDER BY, lalu kolom yang hanya diambil masuk INCLUDE.

5. Kapan index merugikan

Desain index adalah "a complex balancing act between query speed, index update cost, and storage cost." ^[1]

Semua kutipan tabel: ^[1]. Ingat juga: "Don't always equate index usage with good performance".

6. Hands-on (±15 menit): ukur sebelum dan sesudah

Alat ukurnya SET STATISTICS IO, TIME ON. Bandingkan logical reads ("Number of pages read from the data cache" ^[4]), bukan waktu: logical reads tidak berubah karena cache atau beban laptop, sedangkan milidetik dari STATISTICS TIME ^[5] naik-turun. Setiap page berukuran 8 KiB ^[6], jadi 800 logical reads ≈ 6,4 MB data yang disentuh.

1. Container jalan (docker start sql2022) dan setup lab sudah dijalankan.
2. Buka dotnet/lab/0022-index.sql di Azure Data Studio, VS Code mssql, atau SSMS. Nyalakan actual execution plan.
3. Jalankan per bagian (blok sampai GO). Sebelum menjalankan setiap query, tebak dulu operatornya dan apakah logical reads naik atau turun. Catat angkanya di tabel ini:

Lewat sqlcmd: docker cp dotnet/lab/0022-index.sql sql2022:/tmp/0022.sql lalu docker exec sql2022 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Dev_Passw0rd!' -C -i /tmp/0022.sql (logical reads tampil, plan tidak). Skrip menghapus semua index buatannya di bagian 6, jadi lab kembali ke kondisi setup untuk lesson 0023.

-- Lesson 0022 · Index: clustered vs non-clustered, key lookup, covering (INCLUDE), composite, biaya index.
-- Jalankan setelah dotnet/lab/00-setup-product-catalog-lab.sql. Boleh dijalankan ulang.
-- Jalankan per bagian (blok sampai GO). Nyalakan "Include Actual Execution Plan"
-- (Ctrl+M di SSMS, tombol "Explain"/"Actual plan" di Azure Data Studio / VS Code mssql),
-- lalu bandingkan dua hal untuk setiap query: operator di plan dan "logical reads" di tab Messages.
-- Angka di komentar adalah perkiraan; yang penting arah perubahannya.

USE ProductCatalogLab;
SET NOCOUNT ON;
GO

-- 0. Kondisi awal ---------------------------------------------------------------------------
-- Hapus sisa index lesson ini kalau skrip pernah dijalankan setengah jalan.
DROP INDEX IF EXISTS IX_Product_CategoryId ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CategoryId_Covering ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CategoryId_Price ON dbo.Product;
GO

-- Harus hanya dua: PK_Product (CLUSTERED, dari PRIMARY KEY) dan UQ_Product_Name (NONCLUSTERED, dari UNIQUE).
-- Perhatikan: FK_Product_Category TIDAK membuat index pada CategoryId.
SELECT i.name, i.type_desc, i.is_unique, i.is_primary_key
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.Product') AND i.index_id > 0
ORDER BY i.index_id;
GO

SET STATISTICS IO, TIME ON;
GO

-- 1. SEBELUM index --------------------------------------------------------------------------
-- Category 6 = 20 Product, Category 2 = ±2.500 Product, dari 100.000.
-- Plan: Clustered Index Scan. Logical reads = hampir semua page tabel (ratusan), walau hasilnya 20 baris.
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 6;
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 2;
GO

-- 2. Non-clustered index biasa: Index Seek + Key Lookup -------------------------------------
-- Index ini hanya berisi CategoryId (+ Id, clustered key yang otomatis ikut sebagai penunjuk).
CREATE INDEX IX_Product_CategoryId ON dbo.Product (CategoryId);
GO

-- Category 6: Index Seek + Nested Loops + Key Lookup. Name dan Price tidak ada di index,
-- jadi diambil dari clustered index untuk SETIAP baris. Logical reads turun jauh (puluhan).
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 6;

-- Category 2: kemungkinan besar tetap Clustered Index Scan. ±2.500 key lookup (masing-masing
-- beberapa page) lebih mahal daripada membaca seluruh tabel sekali. Index ada, tapi tidak dipakai.
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 2;

-- Hanya kolom yang ada di index (CategoryId, Id): Index Seek saja, tanpa Key Lookup, juga untuk Category 2.
SELECT Id FROM dbo.Product WHERE CategoryId = 2;
GO

-- 3. Covering index dengan INCLUDE ----------------------------------------------------------
-- Kolom predikat di key, kolom yang hanya diambil di INCLUDE (disimpan di leaf, tidak diurutkan).
-- Id tidak perlu ditulis: clustered key otomatis ada di setiap non-clustered index.
DROP INDEX IF EXISTS IX_Product_CategoryId ON dbo.Product;
CREATE INDEX IX_Product_CategoryId_Covering ON dbo.Product (CategoryId) INCLUDE (Name, Price);
GO

-- Keduanya sekarang Index Seek saja, tanpa Key Lookup. Category 2 akhirnya memakai index,
-- dengan logical reads jauh di bawah scan di bagian 1.
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 6;
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 2;

-- Masih covering, tapi hasilnya belum urut menurut Price: perhatikan operator Sort (atau Top N Sort) di plan.
SELECT TOP (10) Name, Price FROM dbo.Product WHERE CategoryId = 2 ORDER BY Price DESC;
GO

-- 4. Composite index dan urutan kolom -------------------------------------------------------
-- CategoryId di depan (equality), Price kedua supaya baris di dalam satu Category sudah urut harga.
DROP INDEX IF EXISTS IX_Product_CategoryId_Covering ON dbo.Product;
CREATE INDEX IX_Product_CategoryId_Price ON dbo.Product (CategoryId, Price DESC) INCLUDE (Name);
GO

-- Index Seek + Top, TANPA Sort: engine membaca 10 entri pertama Category 2 dan berhenti.
SELECT TOP (10) Name, Price FROM dbo.Product WHERE CategoryId = 2 ORDER BY Price DESC;

-- Kolom pertama + rentang di kolom kedua: tetap Index Seek.
SELECT Name, Price FROM dbo.Product WHERE CategoryId = 2 AND Price >= 4900;

-- Hanya kolom kedua: TIDAK bisa seek (leftmost prefix). Plan menjadi scan
-- (Index Scan atas index ini, atau Clustered Index Scan), logical reads naik lagi.
SELECT Name, Price FROM dbo.Product WHERE Price >= 4900;
GO

SET STATISTICS IO, TIME OFF;
GO

-- 5. Biaya index ----------------------------------------------------------------------------
-- Storage: setiap index adalah salinan kolomnya sendiri. Bandingkan page index buatan dengan PK_Product.
SELECT i.name, i.type_desc, ps.used_page_count, ps.row_count
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID(N'dbo.Product')
ORDER BY i.index_id;

-- Biaya tulis: UPDATE Price harus mengubah clustered index DAN IX_Product_CategoryId_Price.
-- Dibungkus transaction + ROLLBACK supaya data lab tidak berubah (transaction: lesson 0021).
BEGIN TRANSACTION;
UPDATE dbo.Product SET Price = Price + 1 WHERE CategoryId = 2;
ROLLBACK TRANSACTION;

-- Pemakaian index sejak server terakhir start: seek/scan/lookup (dibaca) vs user_updates (ditulis).
-- Index dengan user_updates tinggi tapi seek/scan nol hanya menambah biaya: kandidat dihapus.
SELECT i.name, us.user_seeks, us.user_scans, us.user_lookups, us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID(N'dbo.Product') AND i.index_id > 0
ORDER BY i.index_id;
GO

-- 6. Kembalikan lab ke kondisi setup --------------------------------------------------------
DROP INDEX IF EXISTS IX_Product_CategoryId ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CategoryId_Covering ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CategoryId_Price ON dbo.Product;
GO

-- Harus kembali hanya PK_Product dan UQ_Product_Name.
SELECT i.name, i.type_desc
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.Product') AND i.index_id > 0
ORDER BY i.index_id;
GO

7. Latihan tulis query

Tulis sendiri di editor SQL, jalankan di lab, baru buka jawaban. Tidak dinilai otomatis; bandingkan plan, logical reads, dan bentuk query-nya.

SET STATISTICS IO ON;
SELECT Name, Stock FROM dbo.Product WHERE CategoryId = 5 AND Stock = 0 ORDER BY Name;
-- Clustered Index Scan, logical reads = seluruh tabel

CREATE INDEX IX_Product_CategoryId_Stock
    ON dbo.Product (CategoryId, Stock)
    INCLUDE (Name);

SELECT Name, Stock FROM dbo.Product WHERE CategoryId = 5 AND Stock = 0 ORDER BY Name;
-- Index Seek (+ Sort kecil), tanpa Key Lookup, hanya beberapa logical reads
SET STATISTICS IO OFF;

DROP INDEX IF EXISTS IX_Product_CategoryId_Stock ON dbo.Product;

SELECT i.name AS IndexName, i.type_desc, c.name AS ColumnName,
       ic.key_ordinal, ic.is_descending_key, ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.Product')
ORDER BY i.index_id, ic.is_included_column, ic.key_ordinal;

Ringkasan untuk dicetak: Reference: Index · Istilah: GLOSSARY.md (bagian "Database (SQL Server)")

Ada yang belum jelas? Tanyakan ke saya (agent) di terminal, misalnya: "kenapa GUID buruk sebagai clustered key?", "apa itu filtered index dan kapan dipakai?", atau "bagaimana EF Core membuat index lewat HasIndex dan IncludeProperties?"

Sumber: [1] Index architecture and design guide · [2] Clustered and nonclustered indexes described · [3] Showplan logical and physical operators reference · [4] SET STATISTICS IO · [5] SET STATISTICS TIME · [6] Pages and extents architecture guide · [7] CREATE INDEX