Clustered Index

A clustered index determines the physical order of data rows in a table. Each table can have only one clustered index.

CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate);

Non‑clustered Index

A non‑clustered index is a separate structure that contains a copy of indexed columns and a pointer to the actual data row.

CREATE NONCLUSTERED INDEX IX_Products_CategoryID
ON dbo.Products (CategoryID);

Unique Index

Enforces uniqueness of the indexed column(s). Can be clustered or non‑clustered.

CREATE UNIQUE NONCLUSTERED INDEX UX_Employees_Email
ON dbo.Employees (Email);

Filtered Index

A non‑clustered index with a WHERE clause that includes a subset of rows.

CREATE NONCLUSTERED INDEX IX_Orders_Active
ON dbo.Orders (CustomerID)
WHERE IsActive = 1;

Columnstore Index

Stores data column‑wise, optimizing analytic queries and compression.

CREATE CLUSTERED COLUMNSTORE INDEX CX_InvoiceDetails
ON dbo.InvoiceDetails;

XML Index

Specialized indexes for XML data type columns, improving XQuery performance.

CREATE PRIMARY XML INDEX PXI_Documents
ON dbo.Documents (XmlContent);

Spatial Index

Optimizes queries on spatial data types (geometry, geography).

CREATE SPATIAL INDEX SXI_Locations
ON dbo.Locations (GeoLocation);

Indexed Views

Materializes a view with a unique clustered index, improving performance for frequently accessed aggregations.

CREATE VIEW dbo.vSalesSummary
WITH SCHEMABINDING
AS
SELECT StoreID, SUM(Amount) AS TotalSales
FROM dbo.Sales
GROUP BY StoreID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_vSalesSummary_StoreID
ON dbo.vSalesSummary (StoreID);

Full‑Text Index

Enables advanced word‑based searches on large text columns.

CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Articles(Content)
KEY INDEX PK_Articles;

When to Use Each Type