Show TOC

Database-Specific Parameters for MS SQL ServerLocate this document in the navigation structure

Use

Clustered Index

Use

For MS SQL Server, you can define whether an index is clustered or non-clustered.

The physical sorting of data strongly affects query performance. Using a clustered index makes data sorting much faster. On the other hand, unclustering the primary key and clustering another index can make query performance worse. It makes sense if the primary key is a GUID that is never used in a SELECT statement.

Changing this property requires thorough tests. It highly impacts the cost of execution, especially the number of logical page reads. As a result, this affects the query optimizer execution plans.

Note

You should change the clustered index property only when you are explicitly asked to do so by SAP development.

Recommendation

We strongly recommend that you cluster at least one index. By default, this is the primary key.

You should not uncluster all indexes, including the primary key, as this causes side effects on space usage.

Description

All data rows in a table are physically sorted by the columns of the clustered index. Therefore, only one index can be clustered per table. This could either be the primary key or any other index.

By default, the primary key is clustered, and all other indexes are nonclustered.

Syntax

  • Primary key

    • <is-nonclustered>false</is-nonclustered> - enables clustering on the primary key

    • <is-nonclustered>true</is-nonclustered> - disables clustering on the primary key

  • Secondary indexes

    • <is-clustered>false</is-clustered> - disables clustering on secondary indexes

    • <is-clustered>true</is-clustered> - enables clustering on secondary indexes