Create Index

Table of Contents



Indexes and unique keys can be rebuilt. Since indexes do not become "fragmented", the only reason you would need to rebuild an index is to alter its partition size.

Partition Size

When an index is created, the engine will create a lookup tree containing the values from the fields which are specified in the index. One way to create this tree is to create one single large tree. The problem with this approach is that it causes lock contention, reduces parallelism opportunities, increases read/write size since the minimum index read size is 1 partition and takes a long time to traverse for index read operations.

The solution to these performance problems is partitioning. When an index is partitioned, the lookup trees will be striped across n-number of trees (where n is the partition count) using a quick hash of the lookup values.

Be default, the engine will use the globally configured index partition size but you can set a custom partition size per-index by specifying "Partitions" in the WITH section of the index creation or rebuilt statement.


round-pushpin Statement to create an index.
CREATE INDEX IX_Word_Text(
    LanguageId,
    Text
) ON WordList:Word

round-pushpin Statement to create an index with a user-defined partition size.
For more information, see partitions and Rebuild_Index.
CREATE INDEX IX_Word_Text(
    LanguageId,
    Text
) ON WordList:Word WITH(Partitions = 1000)


SQL :: PageSize
Defines the size of a schema page.
SQL :: Partitions
Partitions are applicable to indexes and define the number of pieces the index will be split into.
SQL :: Rebuild Index
Rebuilds an index with a different set of parameters.
SQL::Keywords
A breakdown of all high-level statements available via the query handler.
SQL::Syntax
Basic KBSQL syntax and some examples.