CREATE INDEX Statement (version 6.5)

Creates an index on a given table. The CREATE INDEX statement either changes the physical ordering of the table or provides the optimizer with a logical ordering of the table to increase efficiency for queries. When you create an index for the primary key, use the table- and column-level Primary Key constraint provided with the CREATE TABLE or ALTER TABLE statements.

For additional syntax information for the CREATE INDEX statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
    ON [[database.]owner.]table_name (column_name [, column_name]...)
[WITH
    [PAD_INDEX, ]
    [[,] FILLFACTOR = fillfactor]
    [[,] IGNORE_DUP_KEY]
    [[,] SORTED_DATA | SORTED_DATA_REORG]
    [[,] IGNORE_DUP_ROW | ALLOW_DUP_ROW]]
[ON segment_name]

where

PAD_INDEX

Specifies that the FILLFACTOR setting should be applied to the index node pages as well as to the data pages in the index.

Note The number of items on an index page is never less than two, regardless of how low the value of FILLFACTOR.

Remarks

Indexes are made up of pages that form a branching structure known as a B-tree. The starting page records ranges of values within the table. Each range on the starting page branches to another page that contains a more detailed range of values in the table. In turn, these index pages branch out to more layers, which increase the number of pages in the layer and narrow the range of data on each page.

The branching index pages are called node pages. The final layer in the branching structure is called the leaf layer. In a unique, non-clustered index, the ranges on each leaf layer page point to a specific row of data in the table. In a clustered index, the leaf layer is the data pages because the data is stored in the physical order specified in the index.

Setting FILLFACTOR to 100 in SQL Server version 6.5 has the same behavior as it does in SQL Server 6.0. Index node pages are filled completely and have no space for additional items.

Example

This example creates an index on the author's identification number in the authors table. Without the PAD_INDEX option, SQL Server version 6.5 creates leaf pages that are 10 percent full, but the node pages are filled almost completely. With PAD_INDEX the node pages are also 10 percent full.

Note At least two entries appear on the index pages of unique clustered indexes when PAD_INDEX is not specified.

CREATE INDEX au_id_ind
    ON authors (au_id)
    WITH PAD_INDEX, FILLFACTOR=10