What is an Index?

An index is a data structure, a special data structure designed to improve the speed of data retrieval. An index on a file speeds up selections on the search key fields for the index. 

 CLUSTERED INDEX Clustered index is a type of index which sorts the data rows in the table on their key values. Clustered index exists as sorted row on disk. Clustered index re-orders the table record. Clustered index contains record in the leaf level of the B-tree. NON-CLUSTERED INDEX A Non-clustered index stores the data at one location and indices at another location. The index contains pointers to the location of that data. Non-clustered index is like the index in the back of a chemistry text book. The chemistry text book has some natural order to it: Chapter 1: Matter Chapter 2: Elements Chapter 3: Compounds DENSE INDEX In a dense index, a record is created for every search key valued in the database. This helps you to search faster but needs more space to store index records. In this Indexing, method records contain search key value and points to the real record on the disk. key columns are stored. SPARSE INDEX It is an index record that appears for only some of the values in the file. Sparse Index helps you to resolve the issues of dense Indexing. Primary and Secondary Indexes An index on a set of fields that includes the primary key is called a primary index. Primary Index requires the rows in data blocks to be ordered on the index key. An index that is not a primary index is called a secondary index. They can be in any order.