Understanding SQL Server Indexes
Database indexes are crucial for optimizing SQL Server query performance. Without indexes, SQL Server has to perform a full table scan to retrieve data.
Clustered vs Non-Clustered Indexes
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Physical Order | Reorders the physical rows | Maintains separate index structure |
| Max Count | Only 1 per table | Up to 999 per table |
| Leaf Node | Contains actual data rows | Contains pointers to data |
Creating Indexes in SQL
-- Create a unique clustered index
CREATE UNIQUE CLUSTERED INDEX IX_Post_Slug
ON Posts (Slug);
-- Create a non-clustered index
CREATE NONCLUSTERED INDEX IX_Post_PublishedDate
ON Posts (PublishedDate DESC);