Clustered Index

• Defines the physical order of rows.

CREATE CLUSTERED INDEX IX_Users_UserId
ON Users(UserId);

Use Case

• Fast lookups and range queries on primary keys.

---

Non‑Clustered Index

• Logical index pointing to the clustered key.

CREATE INDEX IX_Orders_UserId
ON Orders(UserId);

Use Case

• Speed up WHERE, JOIN, ORDER BY, GROUP BY.

---

Composite Index (Multi‑Column)

• Note: Order matters.

CREATE INDEX IX_Orders_UserId_CreatedAt
ON Orders(UserId, CreatedAt);

Use Case

• Queries filtering by both columns: WHERE UserId = 10 AND CreatedAt >= '2024-01-01'

---

Covering Index (INCLUDE)

• Avoids table lookups. CREATE INDEX IX_Orders_UserId_Cover ON Orders(UserId) INCLUDE (OrderDate, Amount);

Use Case

• Query is fully satisfied by the index:

SELECT OrderDate, Amount
FROM Orders
WHERE UserId = 10;

---

Unique Index

• Enforces uniqueness.

CREATE UNIQUE INDEX IX_Users_Email
ON Users(Email);

Use Case

• Prevent duplicate emails or usernames.

---

Filtered Index

• Index only a subset of rows.

CREATE INDEX IX_Orders_Active
ON Orders(Status)
WHERE Status = 'Active';

Use Case

• Fast queries on sparse data: WHERE Status = 'Active'

---

Full‑Text Index

• Optimized for text search.

CREATE FULLTEXT INDEX ON Articles(Content)
KEY INDEX PK_Articles;

Use Case

Searching long text:

SELECT * FROM Articles
WHERE CONTAINS(Content, '"machine learning"');

---

Columnstore Index

• Column-based storage for analytics.

CREATE CLUSTERED COLUMNSTORE INDEX IX_Sales
ON Sales;

Use Case

• Data warehouse queries:

SELECT Region, SUM(Amount)
FROM Sales
GROUP BY Region;

Hash Index (Memory‑Optimized Tables)

• Used in in-memory OLTP. CREATE HASH INDEX IX_Users_Email ON Users(Email) WITH (BUCKET_COUNT = 1024);

Use Case

• High‑concurrency lookups on memory‑optimized tables.

---

Summary

Index Type	What It Does	Best Use Case
Clustered Index	Defines physical row order	Primary key, range queries, ever-increasing values
Non-Clustered Index	Logical index for fast lookups	WHERE, JOIN, ORDER BY, GROUP BY
Composite Index	Multi-column index in defined order	Multi-column filters (e.g., UserId + CreatedAt)
Covering Index	Includes extra columns to avoid table lookups	SELECT-heavy queries needing extra columns
Unique Index	Enforces uniqueness	Email, username, code fields
Filtered Index	Index on a subset of rows	Sparse data (IsActive = 1, IsDeleted = 0)
Full-Text Index	Optimized for text search	Searching long text or phrases
Columnstore Index	Column-based storage for analytics	Data warehouses, aggregations, large fact tables
Hash Index	In-memory optimized hash lookup	Memory-optimized tables, high-concurrency workloads