Types of Indexes in SQL

11/22/2025
All Articles

Types of Indexes in SQL

Types of Indexes in SQL

Types of Indexes in SQL – Complete Tutorial

Introduction

Indexes in SQL are special data structures that improve the speed of data retrieval operations on database tables. They work similarly to an index in a book—making it faster to find information without scanning the entire table.

This tutorial explains the types of SQL indexes, their use cases, advantages, and when to apply them.

๐Ÿ”น 1. Primary Index

A primary index is created automatically when a PRIMARY KEY constraint is defined.

Features:

  • Ensures uniqueness

  • Cannot contain NULL values

  • Automatically indexed by the database

Example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100)
);

๐Ÿ”น 2. Unique Index

A unique index enforces that all values in the indexed column must be unique.

Features:

  • Prevents duplicate values

  • Allows one NULL (depends on DB engine)

Example:

CREATE UNIQUE INDEX idx_email ON users(email);

๐Ÿ”น 3. Non-Unique (Regular) Index

Created on columns to improve query speed but doesn’t enforce uniqueness.

Example:

CREATE INDEX idx_name ON customers(name);

๐Ÿ”น 4. Composite Index

An index on two or more columns.

Use Case:

Useful when queries use multiple columns in WHERE or ORDER BY.

Example:

CREATE INDEX idx_order ON orders(customer_id, order_date);

๐Ÿ”น 5. Full-Text Index

Used for full-text searching in large text fields.

Example:

CREATE FULLTEXT INDEX idx_description ON products(description);

๐Ÿ”น 6. Clustered Index

Stores table data physically in the order of the index.

Key Features:

  • One clustered index per table

  • Faster for range queries

Example (SQL Server):

CREATE CLUSTERED INDEX idx_emp_id ON employees(emp_id);

๐Ÿ”น 7. Non-Clustered Index

Stores index separately from the table, pointing to the actual data.

Features:

  • Multiple non-clustered indexes per table

  • Good for frequent read operations

Example:

CREATE NONCLUSTERED INDEX idx_city ON customers(city);

๐Ÿ”น 8. Bitmap Index

Used in data warehousing for columns with fewer distinct values.

Features:

  • Very efficient for low-cardinality columns (e.g., gender, status)

  • Common in Oracle

Example (Oracle):

CREATE BITMAP INDEX idx_gender ON users(gender);

๐Ÿ”น 9. Hash Index

Uses a hash table internally; best for equality comparisons.

Use Case:

  • Perfect for queries like WHERE id = 10

  • Used in MySQL MEMORY and PostgreSQL HASH indexes

Example:

CREATE INDEX idx_hash ON table_name USING HASH(column_name);

๐Ÿ”น 10. Spatial Index

Used for geographical and geometric data types.

Example:

CREATE SPATIAL INDEX idx_location ON places(location);

๐Ÿ“Œ When NOT to Use Indexes

  • On very small tables

  • On columns that are frequently updated

  • On columns with very low selectivity (many duplicate values)

  • When storage overhead is a concern

Conclusion

Understanding the types of SQL indexes helps optimize query performance and design efficient databases. Choosing the right index depends on your data type, query patterns, and database engine.

If you want, I can also create:

  • SQL performance optimization guide

  • SQL indexing diagrams

  • SQL interview questions related to indexing

  • A full PDF version of this tutorial

Article