SQL examples demonstrating each type of index

Sure! Below are SQL examples demonstrating each type of index.

1. Primary Index (Automatically created on a Primary Key)

A Primary Index is created automatically when you define a Primary Key. It uniquely identifies each row in a table.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(100),

Age INT,

Department VARCHAR(50)

);

1. The Primary Index is automatically created on EmployeeID, ensuring uniqueness and improving lookup speed.
2. If you try to insert duplicate values for EmployeeID, it will fail.

2. Unique Index (Ensures unique values in a column)

A Unique Index prevents duplicate values in a column, but unlike a primary key, it allows NULL values (unless specified otherwise).

CREATE UNIQUE INDEX idx_unique_email ON Employees(Email);

1. Ensures that no two employees have the same email.
2. Allows NULL values if the column is nullable.

INSERT INTO Employees (EmployeeID, Name, Age, Department, Email)

VALUES (1, 'Alice', 30, 'HR', 'alice@example.com'); -- ✅ Success

INSERT INTO Employees (EmployeeID, Name, Age, Department, Email)

VALUES (2, 'Bob', 28, 'IT', 'alice@example.com'); -- ❌ Error: Duplicate email

3. Clustered Index (Determines the physical order of rows)

A Clustered Index sorts and stores the rows physically in the table based on the indexed column. Each table can have only one clustered index.

CREATE CLUSTERED INDEX idx_clustered_age ON Employees(Age);

1. The data in the table is physically reordered based on Age.
2. Improves performance for range-based queries, e.g., WHERE Age BETWEEN 25 AND 35.

SELECT * FROM Employees WHERE Age BETWEEN 25 AND 35;

Note: If a table has a Primary Key, it is the default Clustered Index unless explicitly specified otherwise.

4. Non-Clustered Index (Stores pointers to the actual data)

A Non-Clustered Index does not affect the physical order of rows. Instead, it creates a separate structure that holds pointers to the actual data.

CREATE NONCLUSTERED INDEX idx_nonclustered_department ON Employees(Department);

1. The index stores department names with pointers to their respective rows.
2. Speeds up searches for specific departments.

SELECT * FROM Employees WHERE Department = 'IT';

5. Full-Text Index (Optimized for searching text-based data)

A Full-Text Index is used for searching large text fields efficiently, such as descriptions, resumes, or articles.

First, enable Full-Text Search (for MySQL/MSSQL):

ALTER TABLE Employees ADD FULLTEXT(Description);

Or explicitly create a Full-Text Index:

CREATE FULLTEXT INDEX idx_fulltext_description ON Employees(Description);

Now, search for specific keywords efficiently:

SELECT * FROM Employees WHERE MATCH(Description) AGAINST('experienced software engineer');

1. Faster than LIKE '%text%' queries.

Summary

Index Type Purpose

Primary Index

Uniquely identifies rows (Auto-created on Primary Key).

Unique Index

Ensures column values are unique.

Clustered Index

Determines physical order of rows in the table.

Non-Clustered Index

Stores pointers to actual data, does not affect row order.

Full-Text Index

Optimized for searching large text fields efficiently.

Would you like a performance comparison using EXPLAIN queries? 🚀