What types of indexes exist?

Types of indexes depend on the context of their application, usually in databases.

By storage structure:

• B-tree: The most common type, used for ordered data storage and fast range searches.
• B+ tree: A variant of B-tree, where all data is stored in leaf nodes, optimizing sequential reading.
• Hash Index: Based on hash tables, effective for exact match searches but not suitable for range searches or sorting.
• R-tree: Used for indexing multidimensional data, such as geographic coordinates.

By purpose:

• Primary Key Index: Automatically created for the primary key, ensuring uniqueness and serving as a record identifier.
• Secondary Index: Created on one or more columns to speed up queries that are not primary keys.
• Unique Index: Guarantees the uniqueness of values in the indexed column (or column combination).
• Full-Text Index: Used for searching within text inside columns.
• Spatial Index: Used for indexing spatial data.

By creation method:

• Clustered Index: Defines the physical order of data storage in the table. A table can have only one clustered index.
• Non-clustered Index: Stores pointers to actual data, which may be stored in a different order.

Example of creating indexes in SQL:

-- Creating a regular index
CREATE INDEX idx_lastname ON Employees (LastName);

-- Creating a unique index
CREATE UNIQUE INDEX idx_employeeid ON Employees (EmployeeID);

-- Creating a composite index
CREATE INDEX idx_name_city ON Customers (FirstName, City);