Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Clustered vs Non-Clustered Indexes Explained

- 26.05.26 - ErcanOPAK

📚 The Phone Book vs The Library Catalog

Clustered indexes physically order data (like phone book). Non-clustered indexes point to data (like library catalog). Choose wisely.

📖 Clustered Index

  • Only one per table
  • Physically reorders data
  • Fast for range queries (BETWEEN, >, <)
  • Primary key = clustered by default
  • Slow for inserts (data rearrangement)

🏷️ Non-Clustered Index

  • Up to 999 per table
  • Separate structure pointing to data
  • Fast for exact matches (=, IN)
  • Includes key lookup (extra step)
  • Can INCLUDE additional columns

📝 Creating Indexes

-- Clustered (Primary Key)
CREATE TABLE Users (
    Id INT PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100)
);

-- Non-clustered on Name
CREATE NONCLUSTERED INDEX IX_Users_Name ON Users(Name);

-- Composite index (multiple columns)
CREATE INDEX IX_Orders_UserDate ON Orders(UserId, OrderDate);

-- Included columns (cover query)
CREATE INDEX IX_Orders_UserDate 
ON Orders(UserId, OrderDate) 
INCLUDE (TotalAmount, Status);

-- Unique non-clustered
CREATE UNIQUE INDEX IX_Users_Email ON Users(Email);

✅ When to Use Which

  • Clustered: Primary key, columns with range queries, columns with unique values
  • Non-clustered: Foreign keys, columns in WHERE/JOIN, search columns
  • Composite: When filtering on multiple columns together
  • INCLUDE: Cover queries to avoid key lookups

💡 Best Practices

  • Choose clustered key wisely (narrow, unique, ever-increasing)
  • Don’t over-index (each index slows writes)
  • Drop unused indexes (check usage stats)
  • Consider index order (most selective columns first)
  • Regularly rebuild/reorganize indexes

“Query was taking 45 seconds. Added one non-clustered index on the WHERE column. Query dropped to 0.2 seconds. Indexing is the single biggest performance lever you have.”

— Database Administrator

Related posts:

SQL Server: Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks N...

SQL Server: Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks N...

How to use Replicate function for masking in SQL?

How to use Replicate function for masking in SQL?

SQL Transaction Isolation Levels: Master Data Consistency

SQL Transaction Isolation Levels: Master Data Consistency

Views: 26

Post navigation

.NET Core: Understand Scoped, Transient, and Singleton Services
C#: Async/Await Best Practices to Avoid Deadlocks and Performance Issues

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com