SQL - Indexes

Indexes are special lookup tables that the database search engine can use to speed up searches and queries. An index in a database is very similar to an index in the back of a book.  It is used to efficiently find all rows matching a column in your query and then walk through that subset of the table for exact matches.  If you don’t have indexes then every row in the table will be checked.

An index will speed up your SELECT queries but will slow down data input, i.e. when you use UPDATE or INSERT statements.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Whatever index you create, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Single-Column

A single-column index is one that is created based on only one table column. 

Unique Index

The index can be unique, whereby you cannot have duplicate values in that column, or a Primary Key which in some storage engines defines where in the database file the value is stored.  Creating a unique index “provides additional information for the query optimizer that can produce more efficient execution plans”, as per SQL Server Index Design Guide.

Composite Index

A composite index is an index on two or more columns of a table.

Implicit Index

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Hash Table Index

Hash tables are another data structure that can be used as indexes - known as hash indexes. Hash indexes are used because hash tables are extremely efficient when it comes to just looking up values. The only thing you must keep in mind is that hash table is only good for looking up key value pairs, i.e. equality rather than a range of values. 

 A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table (although a HEAP table can have a non-clustered index – but that’s for another time).