INDEX TYPES
Clustered Index
An index that imposes a sort order on the pages within the index.
A table can have only one clustered index.
Non clustered Index
An index that does not impose a sort order on the data pages
within the table. You can have up to 1,000 non clustered indexes in a table.
INDEX KEY
You can define an index by using one or more columns in the table, called the index key, with the following restrictions:
- You can define an index with a maximum of 16 columns.
- The maximum size of the index key is 900 bytes.
CLUSTERING KEY
The column(s) defined for clustered index are referred to as clustering key.
CLUSTERED INDEX
A clustered index is special because it causes SQL Server to arrange the data in the table according to the clustering key. Because a table cannot be sorted more than one way, you can define only one clustered index on a table.
Clustered indexes provide a sort order for the storage of data within a table. However, a clustered index does not provide a physical sort order. A clustered index does not physically store the data on disk in a sorted order because doing so creates a large amount of disk input/output (I/O) for page split operations. Instead, a clustered index ensures that the page chain of the index is sorted logically, allowing SQL Server to traverse directly down the page chain to
locate data. As SQL Server traverses the clustered index page chain, each row of data is read in clustering key order.
The general syntax for creating a relational index is as follows:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ][ ;
Nonclustered Indexes
The other type of relational index that you can create is a nonclustered index. Nonclustered indexes do not impose a sort order on the table, so you can create multiple nonclustered indexes on a table. Nonclustered indexes have the same restrictions as a clustered index.
RESTRICTIONS OF NONCLUSTERED INDEXES
They can have a maximum of 900 bytes in the index key and a maximum of 16 columns.
A table is limited to a maximum of 1,000 nonclustered indexes.
Difference Between Clustered And Nonclustered Index
A clustered index imposes a sort order on the data pages in the table.
A nonclustered index does not impose a sort order.
A nonclustered index does not impose a sort order.
Covering Indexes
When
an index is built, every value in the index key is loaded into the
index. In effect, each index is a mini-table containing all the values
corresponding to just the columns in the index key. Therefore, it is
possible for a query to be entirely satisfied by using the data in the
index.
An index that is constructed such that SQL Server can completely satisfy queries by reading only the index is called a covering index.
Included Columns
Indexes
can be created using the optional INCLUDE clause. Included columns
become part of the index at only the leaf level.
Values from included columns do not appear in the root or intermediate levels of an index and do not count against the 900-byte limit for an index.
Values from included columns do not appear in the root or intermediate levels of an index and do not count against the 900-byte limit for an index.
Therefore, you can construct covering indexes that can have more than 16 columns and 900 bytes by using the INCLUDE clause.
Filtered Indexes
To handle the cases where significant skew exists in the data, SQL
Server 2008 allows you to create filtered indexes.
A filtered index is simply an index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table while allowing SQL Server to choose another method for the less selective range.
Filtered indexes have the following restrictions:
A filtered index is simply an index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table while allowing SQL Server to choose another method for the less selective range.
Filtered indexes have the following restrictions:
- They must be a nonclustered index.
- They cannot be created on computed columns.
- Columns cannot undergo implicit or explicit data type conversion.
