What are the types of indexes in databases?

• Unique Index Ensures the uniqueness of values in the indexed column (or set of columns).

• Primary Key A special unique index used to identify each row in a table. A table can have only one primary key.

• Clustered Index Defines the physical order of data storage in the table. A table can have only one clustered index. Data is stored in the order of the index structure.

• Non-Clustered Index Stores data separately from the table rows. Contains pointers to the actual data rows. A table can have multiple non-clustered indexes.

• Covering Index A non-clustered index that contains all the columns needed to satisfy a query, eliminating the need to access the table for additional data.

• Composite/Compound Index An index built on multiple columns of a table. The order of columns is important for query efficiency.

• Full-Text Index Used for full-text search on character data.

• Spatial Index Used for indexing spatial data (geographical coordinates, geometric objects).

• Bitmap Index Used for indexing columns with a small number of discrete values. Efficient for OLAP systems.

• Function-Based Index An index built on the result of a function or expression applied to one or more columns.

• Hash Index Uses hash values for quick data lookup. Efficient for equality (=) comparisons.

-- Example of creating a unique index
CREATE UNIQUE INDEX idx_email
ON users (email);

-- Example of creating a composite index
CREATE INDEX idx_lastname_firstname
ON employees (last_name, first_name);