Lesson 37: Introduction to Indexing (Speeding up Queries)

Indexes are crucial for database performance. They function much like the index at the back of a book, allowing the DBMS to quickly locate data without scanning every single row.

What is an Index?

An index is a separate data structure that stores the values of one or more columns in a sorted order, along with pointers to the location of the actual data records.

Clustered vs. Non-Clustered Indexes

1. Clustered Index: Defines the physical order in which the data is stored on disk. A table can only have one Clustered Index (often the Primary Key).
2. Non-Clustered Index: Creates a separate, sorted list pointing to the physical location of the data. A table can have many Non-Clustered Indexes.

When to Create an Index

Indexes should be created on columns that are:

1. Frequently used in the WHERE clause (filtering).
2. Used in JOIN conditions (linking tables).
3. Used in ORDER BY clauses (sorting).

DDL Syntax

sql -- Creating a non-clustered index on the 'LastName' column CREATE INDEX IX_Employee_LastName ON Employees (LastName);

The Trade-off

While indexes make reading data (SELECT) faster, they slow down writing data (INSERT, UPDATE, DELETE). This is because every time data changes, the index must also be updated. Indexing should be used judiciously.