SQL Index

← Previous Next →

An index is a database structure that improves the speed of SELECT queries by allowing SQL Server to quickly locate rows in a table.

An Index is a database object used to improve the speed of data retrieval from a table.
It works similar to an index in a book—instead of scanning every page, you go directly to the page number listed in the index.
      • Without an index, SQL Server performs a Table Scan, meaning it checks every row in the table.

Types of Indexes in SQL Server
1️. Clustered Index:
      • Sorts and stores the data rows in the table based on the key.
      • A table can have only one clustered index.

2. Non-Clustered Index:
      • Creates a separate structure that points to the data rows.
      • A table can have multiple non-clustered indexes.

Example: Create an index on CustomerID:

CREATE INDEX idx_customerid
ON Orders(CustomerID);

Now SQL Server can directly find the row where CustomerID = 203 instead of scanning the entire table.

Result:
⚡ Faster query performance

Example: Clustered Index 

CREATE CLUSTERED INDEX idx_orderid
ON Orders(OrderID);

Example: Non-Clustered Index 

CREATE NONCLUSTERED INDEX idx_product
ON Orders(ProductName);

Example Orders Table with Indexes 

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Primary key automatically creates a clustered index on OrderID.

Add another index:

CREATE INDEX idx_customer
ON Orders(CustomerID);

Syntax to Drop an Index 

DROP INDEX index_name
ON table_name;

Example: Drop the Index 

DROP INDEX idx_customerid
ON Orders;

-- This command removes the index from the Orders table.


Advantages of Indexes
✔ Faster SELECT queries
✔ Faster searching and filtering
✔ Improves JOIN performance

Disadvantages of Indexes
❌ Uses additional storage space
❌ Slower INSERT, UPDATE, DELETE operations (because indexes must also update)

Interview Definition:
An index in SQL Server is a database object that improves the speed of data retrieval by creating a quick lookup structure for table rows.

← Previous Next →
Topics