Indexes are created on columns in tables or views.
The index provides a fast way to look up data based on the values within those columns.
For example - if you create an index on the Primary Key and then search for a row of data, based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.
You can create indexes on most columns in a table or a view. Except -
You can create indexes on most columns in a table or a view. Except -
LOB data types - such as image, text, and varchar(max).
You can also create indexes on XML columns, but those indexes are slightly different from the basic index.
You can also create indexes on XML columns, but those indexes are slightly different from the basic index.
An index is made up of a set of Index nodes, that are organized
in a B-tree structure. This structure is hierarchical in nature, with
the Root node at the top of the hierarchy and the Leaf nodes at the
bottom.
Clustered Indexes
A clustered index stores the actual data rows at the leaf level of the index.
For example - The entire row of data associated with a specific primary key value, would be stored in a specific leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view.
In addition, data in a table is sorted only if a clustered index has been defined on a table.
A table that has a clustered index is referred to as a Clustered Table. A table that has no clustered index is referred to as a Heap.
A table that has a clustered index is referred to as a Clustered Table. A table that has no clustered index is referred to as a Heap.
Non-clustered Indexes
Unlike a clustered indexed, here the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators, that points to the actual data rows, rather than contain the data rows themselves.
Unlike a clustered indexed, here the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators, that points to the actual data rows, rather than contain the data rows themselves.
This means that the query engine must take an additional step in order to locate the actual data.
Similar as the Btree Index In Oracle.
A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.
Non-clustered indexes cannot be sorted like clustered indexes; however, you can create more than one non-clustered index per table or view.
SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999. This certainly doesn’t mean you should create that many indexes. Indexes can both help and hinder performance.
In addition to being able to create multiple non-clustered indexes on a table or view, you can also add included columns to your index. This means that you can store at the leaf level not only the values from the indexed column, but also the values from non-indexed columns. This strategy allows you to get around some of the limitations on indexes.
In addition to being able to create multiple non-clustered indexes on a table or view, you can also add included columns to your index. This means that you can store at the leaf level not only the values from the indexed column, but also the values from non-indexed columns. This strategy allows you to get around some of the limitations on indexes.
For example, you can include non-indexed columns in order to exceed the size limit of indexed columns (900 bytes in most cases).
Index Types(Concepts) in SQL server
In addition to an index being clustered or nonclustered, it can be configured in other ways:
Index Types(Concepts) in SQL server
In addition to an index being clustered or nonclustered, it can be configured in other ways:
- Composite index
- Unique Index
- Primary key
- Unique
- Covering index
Composite index
An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.
Unique Index
An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.
A unique index is automatically created when you define a primary key or unique constraint.
Primary key Constraint
A unique index is automatically created when you define a primary key or unique constraint.
Primary key Constraint
What happens when you create a PK?
When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behaviour and define a unique, non-clustered index on the primary key.
Unique key
What happens when you create a UK?
When you define a unique constraint, SQL Server automatically creates a unique, non-clustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.
Covering index
A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.
