What is an index in SQL?

An index in SQL is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and overhead during data modification operations (like INSERT, UPDATE, or DELETE).

Purpose of an Index:

• Improves Query Performance: Indexes allow the database to find rows more quickly, which speeds up query performance, especially for large tables.
• Speeds Up Searches: When you perform queries involving WHERE, ORDER BY, GROUP BY, or JOIN, indexes can help speed up data retrieval by reducing the number of rows the database needs to scan.

How It Works:

An index is like a data structure (commonly a B-tree or a hash table) that stores the values of one or more columns from a table in a way that makes searching more efficient. Instead of scanning the entire table row by row, the database can quickly find the location of the data using the index.

Types of Indexes:

1. Unique Index: Ensures that all values in the indexed column are unique (like the primary key).
2. Composite Index: Indexes multiple columns in a table to improve query performance when filtering or sorting based on multiple columns.
3. Full-Text Index: Used for text-based searching on large textual data (e.g., for searching within articles, blogs, etc.).
4. Clustered Index: Determines the physical order of data in a table. There can only be one clustered index per table.
5. Non-clustered Index: A separate structure from the table data that points to the physical data. You can have multiple non-clustered indexes on a table.

Example:

-- Creating a simple index on a column
CREATE INDEX idx_employee_name
ON employees (name);

-- Creating a composite index on multiple columns
CREATE INDEX idx_employee_name_age
ON employees (name, age);

Pros and Cons:

Pros:

• Faster data retrieval for queries that search on indexed columns.
• Can speed up joins and sorting operations.

Cons:

• Additional storage space is required for the index.
• Slower INSERT, UPDATE, and DELETE operations because the index needs to be updated whenever data changes.