The theme of the indices is one very broad that varies between each manager's data base. This answer is fully focused on SQL Server since that is the handle that question.
There are many types of indexes in SQL Server, but we can speak of 2 main clustered and non-clustered. The first are the primary indexes and basically create a key for logically arranging the table. The clustered index is converted, therefore, into the same table. The general rule is that all tables in a database have a clustered index that will be the main form of access to the tables.
The indexes have a tree-structured search balanced so-called b-tree. Is represented in the following manner for the indexes to clustered.
![introducir la descripción de la imagen aquí]()
The information in the table is found up to the level of the "leaves", while the roots and intermediate levels only have the key and a pointer.
The indexes non-clustered are indexes auxiliaries which serve only to gather information more quickly when you do not query a table for your clustered index. The indexes non-clustered have a similar structure to the index clustered, but instead of having the table information in the nodes of leaves, have a pointer to the clustered index or to the page where the row (when there is not a clustered index).
The relationship between both types of indices is presented as well, with a clustered index on the left side and one non-clustered on the right side which points to the clustered index:
![introducir la descripción de la imagen aquí]()
The indexes non-clustered can have additional columns that are not part of the key to avoid having to read them from the table.
Both types of indices also support to maintain the primary keys or the keys unique.
To finish explain a little, the indexes will help to find a row or set of rows more quickly without having to traverse the entire table. In the same way that we can find a word faster in the dictionary thanks to found in order and don't have to read everything (this is an example of how the indices clustered), or how we can find words in a table of contents of a book that will tell us on which page we can find the theme that we seek to (so make the index non-clustered).
This is just an introduction, since we don't touch the indexes with filter, XML indexes, columnstore, full text, etc
