SQL Server Index Knowledge-Concepts

Concept article

Indexing concept (index concept)

A data structure for the related b-tree in a table or view. The index key column consists of one or more columns. You can have an inclusion column (sql2005). Used to promote SQL Server to find related data row efficiency.

Clustered indexes (Clustered index)

Table or indexed view data rows are sorted by the clustered index key. Each table can have only one clustered index (the data in the clustered table can only be organized in one way). The table is called a clustered index table, and a nonclustered index is called a heap table.

Nonclustered indexes (nonclustered index)

A data structure that is independent of the rows of a row. Used to quickly retrieve data. Each index key value contains a pointer to the data row that contains its key value. The pointer in the clustered table is the clustered index key. The pointer is the row position pointer in the heap table.

Index inclusion column (index with included columns)

A nonclustered index contains data columns other than the index key. These data columns are not sorted for maintenance. The user overwrites the query.

Aggregation tables (Clustered table)

Table with clustered index, data organized in order

Heap Tables (heap table)

The table does not contain a clustered index, and the data is stacked randomly.

Indexed Views (index view)

The view is organized and contains an index. The first index to be built must be a clustered unique index, and a nonclustered index can then be built. Used to improve the speed of complex queries and simplify query application logic.

Full-text index (Full-text index)

A special notation-based functional index. Maintenance is created in SQL Server by the Full-text engine. After Sql08, the database engine is integrated, replacing the previous version of the file system. 08 after the full-text catalog is a virtual object, you can not belong to any filegroup.

Spatial index

08 The introduction of an index that supports special data types (spatial data such as Geodata).

Filter index (Filtered index)

A nonclustered index that can add predicate conditions. Used to optimize special queries.

XML index (XML index)

A Blob object index for the XML data type.