Indexes

In SQL, an index is a database object that provides a faster way to look up data in a table. Indexes optimize the retrieval of rows from a table based on the values in one or more columns.

They act like a reference or a pointer to the data, allowing the database engine to locate and retrieve the rows more efficiently.

Types of Indexes

The following are the two main types of indexes.

• Clustered Index:

  The clustered Index determines the physical order of data rows in a table. A table can have only one clustered Index because the rows are stored in the order defined by it.

  Example of creating a clustered index:

  CREATE CLUSTERED INDEX idx_EmployeeID
  ON Employees (EmployeeID);

  In this example, a clustered index named idx_EmployeeID is created on the EmployeeID column of the Employees table. This means that the rows in the Employees table will be physically ordered based on the values in the EmployeeID column.

• Non-Clustered Index:

  The non-Clustered Index does not affect the physical order of data in the table. Instead, it creates a separate structure, including indexed columns and a pointer to the corresponding rows.

  Example of creating a non-clustered index:

  CREATE INDEX idx_LastName
  ON Employees (LastName);

  In this example, a non-clustered index named idx_LastName is created on the LastName column of the Employees table. This Index will help speed up queries that involve filtering, sorting, or searching based on the LastName column.