Indexing In SQL

An index in SQL is similar to an index in a book. Instead of scanning the entire table (like reading every page), the database uses the index to quickly locate rows based on column values.

What is Indexing

• Indexing is a database technique used to optimize and speed up data retrieval.
• It improves query performance by minimizing disk I/O operations, thus reducing the time it takes to locate and access data.
• It creates a separate data structure (index) that stores key values and pointers to actual rows.
• Indexes are organized data structures that allow quick searching based on key values.
• Indexing has 2 main types: Clustered Index and Non-Clustered Index.

Clustered Indexing

• Clustered indexing is a technique where multiple related records are stored together in the same file.
• A Clustered Index determines the physical order of data in the table. The data rows are sorted and stored based on the indexed column.
• Storing related records together makes this process faster and more efficient.

Key Points

• Only one clustered index is allowed per table.
• It defines the physical order of data.
• By default, Primary Key creates a clustered index.
• Great for range queries and sorted results.

Non-Clustered Indexing

• Non-Clustered indexing is a technique where the index is stored separately from the actual data. Instead of sorting the data itself, it stores a pointer (reference) to the location of the data row.
• A Non-Clustered Index does not affect the physical order of data in the table. It creates a structure that helps the database find data quickly without scanning the entire table.

Key Points

• A table can have multiple non-clustered indexes.
• The data remains unsorted, only the index is sorted.
• It stores a reference to the data row instead of the actual data.
• Best for exact match searches (e.g., Email, Username, PhoneNumber).
• Commonly used on columns involved in JOINs, WHERE filters, or frequent searches.

Conclusion

Clustered and non-clustered indexes help speed up SQL queries by organizing data efficiently. A clustered index stores data in sorted order, making it fast for range and sort queries. A non-clustered index keeps a separate structure with pointers to the data, useful for quick lookups on different columns. Using the right index improves performance and makes your database more responsive.