Learn Clustered, Non-Clustered & Composite Indexes - Textnotes

Learn Clustered, Non-Clustered & Composite Indexes

Improve your SQL query performance with indexing. Learn about single-column and composite indexes, clustered vs non-clustered indexes, query optimization, and execution plans. Practice creating indexes and analyzing query performance for faster data retrieval.

1. Introduction

Indexes in SQL are special structures that speed up data retrieval from tables without scanning all rows.

  1. Essential for large datasets to optimize query performance.
  2. Proper indexing can drastically reduce query execution time.

Key Points:

  1. Indexes do not store data separately; they point to table rows.
  2. Over-indexing can slow down INSERT, UPDATE, DELETE operations.
  3. Use execution plans to analyze query performance.

2. What is an Index?

  1. An index is like a table of contents in a book.
  2. Helps the database engine quickly locate rows without scanning the entire table.

Syntax (Single Column Index):


CREATE INDEX idx_columnName
ON TableName (ColumnName);

Example:


-- Create an index on Employee Name
CREATE INDEX idx_employee_name
ON Employees(Name);

3. Types of Indexes

3.1 Single Column Index

  1. Index on one column.
  2. Useful when queries filter or sort by a single column.

3.2 Composite Index

  1. Index on multiple columns.
  2. Useful when queries filter or sort using multiple columns together.

Example:


-- Composite index on Department and Salary
CREATE INDEX idx_dept_salary
ON Employees(Department, Salary);

4. Clustered vs Non-Clustered Index

TypeDescriptionNotes
Clustered IndexSorts the actual table rows based on the indexed columnOnly 1 clustered index per table
Non-Clustered IndexStores pointers to table rowsCan have multiple non-clustered indexes

Example (Clustered Index in SQL Server):


CREATE CLUSTERED INDEX idx_employee_id
ON Employees(ID);

Example (Non-Clustered Index):


CREATE NONCLUSTERED INDEX idx_employee_department
ON Employees(Department);

5. Query Optimization and Execution Plan

  1. Query optimization improves SQL performance.
  2. Execution plan shows how SQL Server executes a query.

Example:


-- Check execution plan in SQL Server
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Employees
WHERE Department = 'IT';
GO
SET SHOWPLAN_TEXT OFF;

Tips:

  1. Look for Index Seek (good) vs Table Scan (slower).
  2. Add indexes on columns used in WHERE, JOIN, ORDER BY clauses.

6. Practical Exercises

  1. Create a single-column index on Employee Name.
  2. Create a composite index on Department and Salary.
  3. Analyze query performance for filtering employees by Department.
  4. Compare execution time with and without indexes.
  5. Identify slow queries using execution plan and optimize them.

7. Tips for Beginners

  1. Index frequently queried columns for faster SELECTs.
  2. Avoid indexing columns that rarely appear in queries.
  3. Regularly maintain indexes in large tables.
  4. Use EXPLAIN / Execution Plan to identify performance issues.


Next Step: After mastering indexing and performance, the next module is Views in SQL, where you’ll learn to create, update, and use views to simplify queries and enhance security.