SQL Indexes

---

In SQL, indexes are used to speed up the retrieval of data from a database table by providing quick access to rows based on the indexed columns.

There are different types of indexes supported by most relational database management systems (RDBMS).

Some common types of indexes along with examples:

1. Single-Column Index: A single-column index is created on a single column of a table. It speeds up queries that involve the indexed column.

   CREATE INDEX idx_lastname ON employees (lastname);
   

2. Composite Index: A composite index is created on multiple columns of a table. It can speed up queries that involve the combination of indexed columns.

   CREATE INDEX idx_firstname_lastname ON employees (firstname, lastname);
   

3. Unique Index: A unique index ensures that the indexed columns do not contain duplicate values.

   CREATE UNIQUE INDEX idx_email ON employees (email);
   

4. Primary Key Index: A primary key index uniquely identifies each record in a table and ensures that the indexed column(s) do not contain NULL values.

   ALTER TABLE employees ADD PRIMARY KEY (employee_id);
   

5. Foreign Key Index: A foreign key index is automatically created when you define a foreign key constraint. It helps to enforce referential integrity between related tables.

   ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);
   

6. Clustered Index: In some databases, like SQL Server, a clustered index determines the physical order of data in the table. It's typically created on the primary key column(s).

   CREATE CLUSTERED INDEX idx_primary_key ON employees (employee_id);
   

7. Non-Clustered Index: A non-clustered index is a separate structure from the data rows and contains pointers to the actual data rows.

   CREATE NONCLUSTERED INDEX idx_lastname ON employees (lastname);

Indexes improve the performance of SELECT queries but may slow down INSERT, UPDATE, and DELETE operations because the database has to maintain the index structure when modifying data.

Therefore, it's essential to create indexes judiciously based on the usage patterns of your database.

Enroll Now

• SQL
• DBMS