Index structure and Types -- Part 1

I have collected this from msdn and other searches.

Index

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table and identify a particular column to be the primary key, the SQL Server 2005 Database Engine automatically creates a PRIMARY KEY constraint and index on that column

“An index is an on-disk structure associated with a table or views that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.”

Index Types

A table or view can contain the following types of indexes:

·         Clustered / Clustered Table

·         Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition.

·         There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

·         If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

·         No clustered

·         No clustered indexes have a structure separate from the data rows. A no clustered index contains the non clustered index key values and each key value entry has a pointer to the data row that contains the key value.

·         The pointer from an index row in a non clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

·         In SQL Server 2005, you can add non key columns to the leaf level of the non clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

·         Both clustered and non clustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.

Indexes are automatically maintained for a table or view whenever the table data is modified.

Index type	Description	Additional information
Clustered	A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.	Clustered Index Design Guidelines Clustered Index Structures
Nonclustered	A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.	Nonclustered Index Design Guidelines Nonclustered Index Structures
Unique	A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Both clustered and nonclustered indexes can be unique.	Unique Index Design Guidelines
Index with included columns	A nonclustered index that is extended to include nonkey columns in addition to the key columns.	Index with Included Columns
Indexed views	An index on a view materializes (executes), the view and the result set is permanently stored in a unique clustered index in the same way a table with a clustered index is stored. Nonclustered indexes on the view can be added after the clustered index is created.	Designing Indexed Views
Full-text	A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service. It provides efficient support for sophisticated word searches in character string data.	Full-Text Indexes
XML	A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.	Indexes on xml Data Type Columns

 

Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

How Indexes are used by the Query Optimizer

Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, or DELETE statements. Consider the query SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 in the AdventureWorks database. When this query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method. The method may be a table scan, or may be scanning one or more indexes if they exist.

When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.

When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.

The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select. SQL Server 2005 provides the Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes.

Read more from this link Index structure and Types -- Part 2 : Index Considerations