Clustered vs. Non-Clustered Indexes: Organizing Your Data for Speed in SQL Server
Here's a table summarizing the key differences:
Feature | Clustered Index | Non-Clustered Index |
---|---|---|
Number per table | Only one | Multiple |
Data order | Determines physical order | Separate structure |
Storage | Data itself is stored in order | Stores pointers to data |
Use case | Fast for exact value lookups | Faster for searching specific columns, but requires additional step to retrieve data |
This example creates a table Customers
with a clustered index on the CustomerID
column. The data in the table will be physically stored sorted by CustomerID
.
CREATE TABLE Customers (
CustomerID int PRIMARY KEY, -- Primary key enforces uniqueness and automatically creates a clustered index
CustomerName nvarchar(50) NOT NULL,
Email nvarchar(100)
);
This example creates a non-clustered index on the Email
column of the Customers
table. The data will still be physically stored based on the clustered index (CustomerID), but this index will allow for faster searches based on email addresses.
CREATE TABLE Customers ( -- Assuming Customers table already exists
CustomerID int PRIMARY KEY,
CustomerName nvarchar(50) NOT NULL,
Email nvarchar(100) NOT NULL
);
CREATE NONCLUSTERED INDEX IX_CustomerEmail ON Customers(Email);
sql-server indexing clustered-index