What are SQL Indexes

Indexes in SQL are a data structure that helps us retrieve data from the database more quickly. We could compare them to a book's table of contents. For example, if you have a book about C programming and want to learn about pointers, you don't need to read the entire book from the beginning to reach the relevant chapter. You check the table of contents, see that the chapter on pointers is on page 110, and go directly there. Indexes work similarly.

Indexes are created on a column of a table in the database. So, when you perform a query based on that column, the SQL engine can return results more quickly.

How SQL Indexes Work

The most common data structure for SQL indexes is the B-Tree, also known as a Balanced Tree. In this data structure, each leaf node of the tree can hold more than one key. For instance, if each leaf has two keys, it will have three branches. The left branch will contain numbers smaller than the left key, the middle branch will have numbers that fall between the left and right keys, and the right branch will have numbers larger than the right key.

A more detailed analysis of balanced trees can be found here:
Understanding B-Trees: The Data Structure Behind Modern Databases

Types of SQL Indexes

Clustered Indexes:

A clustered index defines the physical order of data in a table. This means that rows are stored on disk in the same order as the index. A table can have only one clustered index, as rows can only be sorted in one way. Clustered indexes are typically created based on the primary key of the table.

Non-Clustered Indexes:

A non-clustered index is independent of the table's data. It contains a sorted list of values from the columns that have been indexed, along with pointers to the actual rows in the table. Unlike clustered indexes, a table can have multiple non-clustered indexes.

A more detailed comparison between clustered and non-clustered indexes for SQL Server can be found here:
Clustered vs. Nonclustered Index Structures in SQL Server

Unique Indexes:

A unique index ensures that the values in the column or columns that have been indexed are unique. This type of index is often used to enforce data uniqueness, similar to a unique constraint.

More on unique Indexes here:
Unique and Non Unique Indexes in sql server

Full-Text Indexes:

A full-text index is used to perform full-text searches, which involve searching for words or phrases within large text columns. This type of index is ideal for applications such as search engines or document management systems.

More on Full-text Indexes here:
SQL Server Creating a Full-Text Index

When to Use Indexes

Right Column Rule: Create an index on a column that is frequently used in WHERE, JOIN, or ORDER BY clauses in queries.

Index Limitation Rule: Excessive use of indexes can have the opposite effect by slowing down performance. This is because more indexes increase the time needed for INSERT operations and also enlarge the database size.

Type of Data: The type of data being indexed is crucial. For instance, integers perform better when indexed due to their simpler structure and faster comparison.

Avoid Indexes on Small Tables: For small tables, a full scan might be faster than creating and using indexes to locate a record.

Using indexes in a database is an important tool that can help speed up data retrieval. However, we need to be selective about which data to index to avoid creating a database where every column in every table is indexed, which could lead to results opposite to those we want.