Can you give examples of index structures in SQL and their features?

In SQL, there are different types of index structures that help speed up data search and sorting. Here are the main ones:

1. B-Tree (balanced tree)

   • The most common type of index.
   • Suitable for equality, range queries, and sorting.
   • Provides logarithmic search time.

2. Hash indexes

   • Use hash functions for quick exact match searches.
   • Not suitable for range queries.
   • Supported in some DBMSs (e.g., PostgreSQL) but with limitations.

3. Bitmap indexes

   • Effective for columns with a small number of unique values (e.g., gender, status).
   • Use bitmaps to represent values.
   • Good for analytical queries.

4. Full-text indexes

   • Designed for fast text search.
   • Support word, phrase searches, considering morphology.

5. Spatial indexes

   • Used for geographic data.
   • Allow efficient queries on spatial data.

Example of creating a B-Tree index in SQL:

CREATE INDEX idx_users_lastname ON users (lastname);

The choice of index type depends on the nature of the data and the types of queries to optimize.