Database-Specific Parameters for MS SQL Server

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.

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