CREATE INDEX

Giriş

Not : Clustered vs Non-clustered indexes yazısına bakabilirsiniz.

Açıklaması şöyle. CREATE INDEX ile yaratılan index non-clustered ve içinde veri taşımıyor. Sadece clustered index'e pointer gibi.

Normal İşleyiş

Örnek

Şöyle yaparız

CREATE INDEX idx_name ON employee_tab (name);

Açıklaması şöyle

When querying using a non-clustered index, the database performs an index seek to first locate the index key and the clustered index.

If the requested data is not in the non-clustered index tree, the database performs a key lookup in the clustered index tree to retrieve the entire row and join the data.

This key lookup incurs additional I/O and can slow down the query.

INCLUDE

Açıklaması şöyle

When querying using a non-clustered index, the database performs an index seek to first locate the index key and the clustered index.

If the requested data is not in the non-clustered index tree, the database performs a key lookup in the clustered index tree to retrieve the entire row and join the data.

This key lookup incurs additional I/O and can slow down the query. 

To mitigate this issue, you can use the INCLUDE command to include non-key columns in the non-clustered index tree.

Örnek

Şöyle yaparız

CREATE INDEX idx_example ON employee_tab (name) INCLUDE (age);