What are the differences between various index types in SQL and how do they affect query execution?

In SQL, there are different types of indexes that affect query performance and data storage methods:

• B-Tree index — the most common type. Suitable for fast searches, sorting, and range queries. The index stores data in a balanced tree, providing logarithmic access time.

• Hash index — used for quick exact match searches (e.g., WHERE column = value). It does not support range queries or sorting.

• Bitmap index — effective for columns with low cardinality (few unique values). Uses bitmaps for fast filtering.

• Clustered index — determines the physical order of rows in the table. Usually, a table can have only one clustered index. It speeds up retrievals based on the clustered index key.

• Non-clustered index — a separate structure containing keys and pointers to table rows.

Impact on query execution:

• Indexes speed up SELECT operations, especially with search conditions and sorting.
• They may slow down INSERT, UPDATE, DELETE operations due to the need to update indexes.
• The choice of index type depends on the query patterns and data structure.

Example of creating a B-Tree index in SQL Server:

CREATE INDEX idx_customer_name ON Customers (LastName, FirstName);