nonclustered

Note that this value does not include the leaf level of the nonclustered index.

If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.

When a nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index.

When you create a clustered or nonclustered index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

This allows the storage engine to retrieve data rows from the underlying table before it has completed the scan of the nonclustered index.

A nonclustered index can be converted to a clustered index type by specifying CLUSTERED in the index definition.

Nonclustered indexes do not have to be scanned to validate the base table.

Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns.

ON option so the DROP INDEX transaction does not block queries and modifications to the underlying data and associated nonclustered indexes.

Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index.

An administrator wants to determine the performance impact of adding a nonclustered index to a table.

Therefore, even if the nonclustered indexes were previously aligned with the clustered index, they may no longer be aligned with the heap.

A nonclustered index can be defined on a table or view with a clustered index or on a heap.

The bottom level of a clustered or nonclustered index.

These physical performance structures include clustered indexes, nonclustered indexes, indexed views, and partitioning.

Existing nonclustered indexes and XML indexes associated with the table are automatically disabled and cannot be accessed.

Create nonclustered indexes on all columns that are frequently used in predicates and join conditions in queries.

The process of determining disk space requirements for unique indexes is the same as that of clustered and nonclustered indexes.

Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.

All indexing options that apply to a nonclustered index, except IGNORE_DUP_KEY and ONLINE, are permitted on secondary XML indexes.

Physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning.

Added unique nonclustered indexes to the list of indexes that cannot be created online.

The following example creates a unique nonclustered index on the Name column of the Production. UnitMeasure table.

Rebuilding the clustered index on a view does not automatically enable nonclustered indexes on the view.

CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.

The leaf rows of a nonclustered index contain pointers to the data rows that contain each specific key value.

Columns that are not part of the index key can be included in nonclustered indexes.

That is, all page locks are disallowed on the nonclustered indexes.

Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index.

Conversion from clustered to nonclustered is not supported regardless of the ONLINE setting.

Online processing allows concurrent user access to the underlying data and to nonclustered indexes during the index operation.

Unique nonclustered indexes cannot be created online.

In this case, one table is a heap with no indexes, and the other table is a heap with a nonclustered index.

A unique clustered index must be created on a view before any nonclustered index is created.

Calculate the space used to store index information in the nonleaf levels of the nonclustered index.

Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.

When a nonclustered index is disabled, the index data rows are deleted, but the index definition remains in metadata.

Calculate the space used to store index information in the leaf level of the nonclustered index.

Specifies that nonclustered indexes for user tables should not be checked.

Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.

Nonclustered or clustered index on the Name column.

If the nonclustered index is over a clustered index, the data row locator is the clustering key.

Nonclustered indexes on an indexed view are aligned with the indexed view.

The default storage size does not include nonclustered indexes and indexed views.

Provides guidance on improving the performance of bulk copy operations when the target table contains clustered and nonclustered indexes.

Multiple unique nonclustered indexes can be defined on a table.

Each index row in the nonclustered index contains the nonclustered key value and a row locator .

After a unique clustered index has been created on a view, nonclustered indexes can be created.

The base table structure is not dependent on the structure of a nonclustered index.

Nonclustered indexes are automatically disabled when the associated clustered index is disabled.

Disabling a nonclustered index or clustered index on a view physically deletes the index data.

The difference is that nonclustered indexes do not affect the order of the data rows.

This also applies to nonclustered index leaf rows that have included columns.

ON is not valid for indexed views, nonclustered indexes or indexes on local temp tables.

When AllowRowLocks or DisAllowRowLocks are set to TRUE or FALSE, the setting is applied to the heap and any associated nonclustered indexes.

If the nonclustered index is on the primary key, any active, referencing FOREIGN KEY constraints will automatically be disabled.

Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups.

Added a note in the definition of the ONLINE option about unique nonclustered indexes.

If the nonclustered index is over a heap, the data row locator is the heap RID.

By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

By default, a nonclustered index is created if clustered is not specified.

Create the clustered index before creating any nonclustered indexes.

When ALL is specified, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized.

If CLUSTERED is not specified, a nonclustered index is created.

The index data rows for the clustered and nonclustered indexes are deleted.

Disabling a nonclustered index physically deletes the index data.

These additional columns are only stored at the leaf level of the nonclustered index.

Clustered or nonclustered index on the column or columns defined in the constraint.

Index type (CLUSTERED or NONCLUSTERED) not specified.

After the unique clustered index has been created, you can create additional nonclustered indexes.

You can create multiple nonclustered indexes on a table or indexed view.

By default, a nonclustered, nonunique index is created if the clustered or unique options are not specified.

If a clustered constraint or index already exists on a table, PRIMARY KEY constraints default to NONCLUSTERED.

MOVE TO is not valid for indexed views or nonclustered indexes.

Disabling a clustered index on a view also disables any nonclustered indexes on that view.

Nonclustered or clustered index on the sorted or aggregated column.

Nonclustered indexes that are disabled are exempt from this requirement.

Includes nonclustered indexes created by PRIMARY KEY or UNIQUE constraints, but not XML indexes.

The storage engine uses prefetching to speed base table lookups from nonclustered indexes.

Specifies that a clustered or nonclustered index is created for the PRIMARY KEY or UNIQUE constraint.

Unique nonclustered indexes can now be created online.

An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table.

Nonclustered indexes not resident on the specified filegroup are not checked.

If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation.

All clustered and nonclustered indexes on views that reference the table are disabled.

Clustered or nonclustered index on the ProductModelID column.

You can also consider partitioning clustered and nonclustered indexes across multiple filegroups.

The following example creates a nonclustered index on the VendorID column of the Purchasing. ProductVendor table.

In this case, the table in Node 1 is a heap with no indexes, and the table in Node 2 is a heap with a nonclustered index.

Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups.

The nonclustered index if the pages are nonclustered index pages.

In earlier versions of SQL Server, you could sometimes rebuild a nonclustered index to correct inconsistencies caused by hardware failures.

Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint.

The maximum number of indexes in the table hint is 250 nonclustered indexes.

Nonclustered indexes have a structure separate from the data rows.

Nonclustered indexes have a B-tree index structure similar to the one in clustered indexes.

You can rebuild a disabled nonclustered index online when the table does not have a disabled clustered index.

SQL Server supports clustered and nonclustered indexes.

Corrected the Remarks section for nonclustered indexes on separate filegroups to reflect SQL Server 2005 behavior.

In earlier versions of SQL Server, the nonclustered index and the base table in the other filegroup are checked.

The following methods can be used to rebuild clustered and nonclustered indexes

When you design nonclustered indexes with included columns consider the following guidelines

Nonclustered indexes that are defined with LOB data type columns

Nonclustered index on an indexed view

Clustered and two nonclustered indexes

all clustered and nonclustered indexes on tables

Each nonclustered index created for a table or a view has a row in

A heap with LOB data and no nonclustered indexes, and;

You can use the following steps to estimate the amount of space that is required to store a nonclustered index