A database index is a data structure that improves the speed of data retrieval operations on a database. However, it comes with the extra cost of additional writes and storage space to maintain index data structure.
Indexes can be created by using one or more columns of a database table for rapid lookups.
During an insert, read, update, or delete operation a process called index-looking is done. Index-locking is done to maintain index integrity.
Index Locking: A portion of an index is locked during a database transaction.
Types of Database Index
Primary or Clustered Index
Primary Index is the search key that specifies the sequential order of the table (or collection). It defines the order in which data is physically stored in a table. Table/Collection can be sorted in only one way. That is why, there’s only one clustered index for a table (can be composed of multiple columns per table). In SQL server, the primary key constraints automatically creates a clustered index on the primary key column.
Secondary or Non-Clustered Index
Secondary Index is an index whose order (search key/column) specifies an order different from sequential order (primary index order) or the order the way table is sorted. A secondary index doesn’t sort the physical data inside the table. Infact, secondary index and table data are stored at different places. It is similar to index of a book.
This allows for more than one secondary index on a table with table data actually sorted by a primary index.
When a query is run against a column on which a secondary index is created, the database will go to the secondary index (where it is stored) and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values of that row.
Note: Due to the additional table lookups, secondary/non-clustered indexes are slower than primary index.
Image courtesy: Database System Concepts Book