Search Engine Algorithm Research Topic III: Introduction to Clustered and nonclustered indexes

Search Engine Algorithm Research Topic III: Introduction to Clustered and nonclustered indexes

Introduction to Clustered Indexes

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index.

If it is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes have faster data access speeds than nonclustered indexes.

Clustered indexes often speed up UPDATE and DELETE operations because they require a large amount of data to be read. It can take a long time to create or modify a clustered index because the rows of the table are reorganized on disk when you perform both operations.

Consider using a clustered index for:

1. A column that contains a limited number of unique values, such as the State column contains only 50 unique states codes.

2. Use the following operators to return a query for a range value: Between, >, >=, <, and <=.

3. Returns a query for a large result set.

Create a clustered index

In the database diagram, select the table that you want to create an index on, right-click the table, and then choose Indexes/Keys from the shortcut menu.

Or

Open the Table Designer for the table you want to index, right-click in the Table Designer, and choose Indexes/Keys from the shortcut menu.

Creates a new index. For more information, see Creating Indexes.

To modify an existing index, select the index from the selected indexes list.

Select the Create as CLUSTERED check box.

When you save a table or diagram, the index is created in the database.

Non-clustered index introduction

Nonclustered indexes have a B-tree structure like a clustered index, but there are two significant differences:

Data rows are not sorted and stored in the order of the nonclustered index keys.

The leaf layer of a nonclustered index does not contain a data page.

Instead, leaf nodes contain index rows. Each index row contains a nonclustered key value and one or more row locators that point to the data row that has the key value (or multiple rows if the index is not unique).

Nonclustered indexes can be defined on a table, heap, or indexed view that has a clustered index. At Microsoft? SQL Server? In 2000, the row locators in a nonclustered index have two forms:

If the table is a heap (no clustered index), the row locator is a pointer to the row. The pointer is generated with a file identifier (ID), a page number, and a row count on the page. The entire pointer is called the row ID.

If the table does not have a clustered index, or if the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2000 adds an internally generated value to make the duplicate key unique. The user does not see this value, which is used to make the keys within the nonclustered index unique. SQL Server retrieves the data rows by using the clustered index key to search the clustered index, while the clustered index key is stored in the leaf row of the nonclustered index.

Because a nonclustered index stores a clustered index key as its row pointer, it is important to keep the clustered index key as small as possible. If the table also has a nonclustered index, do not select a large column as the key for the clustered index.

Search Engine Algorithm Research Topic III: Introduction to Clustered and nonclustered indexes