Database Indexing, Types of Index, Indexes

A database index is a copy of part of a table that is used to speed up data retrieval in a database.Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Types of Index:
Clustered Index
Non-Clustered Index

Clustered Index:
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A clustered index stores data similar to a phone directory where all people with the same last name are grouped together. SQL Server will quickly search a table with a clustered index while the index itself determines the sequence in which rows are stored in a table. Clustered indexes are useful for columns searched frequently for ranges of values, or are accessed in sorted order.

CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndexON DummyTable2 (EmpID)GO

Non-Clustered Index:
A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
A non-clustered index stores data comparable to the index of a text book. The index is created in a different location than the actual data. The structure creates an index with a pointer that points to the actual location of the data. Non-clustered indexes should be created on columns where the selectivity of query ranges from highly selective to unique. These indexes are useful when providing multiple ways to search data is desired.

CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empidON DummyTable1 (empid)GO

Tags: index, indexes, indexing, Database Indexing, Types of Index, Clustured Index, Non-Clustered Index, Syntax of Index, Clustured index syntax, Non clustured index syntax