Comprehend clustered indexes within SQL Server

This article primarily explains clustered indexes in SQL Server, covering their purpose and internal working mechanism. Readers who need a deeper understanding can use this as a reference. When it comes to clustered indexes, I assume every developer has heard of them. But like many “code monkeys” (myself included), some of us resort to rote memorization: “A table can have only one clustered index,” or analogies like “it’s like a book’s table of contents.” But here’s the problem—we’re not studying literature! We don’t need to memorize blindly. What we truly want is to see the real, tangible structure with our own eyes. We love clustered indexes because they transform an unordered heap table into an ordered structure using a B-tree. This reduces search complexity from O(N) to O(logₘN), dramatically lowering both logical reads and physical reads. I. Observations 1. Without Any Index As usual, let’s start with an example. Suppose…

In-depth Understanding of Non-Clustered Index Structure in SQL Server

Is a non-clustered index also a heap structure? Actually, SQL Server uses several types of pages (data is stored page by page, just like how Windows organizes memory in pages). For those interested, this article aims to deepen your understanding of non-clustered index structures. We know that SQL Server stores data rows using two types of data structures: A: Heap B: B-tree (binary tree) Data is sorted and stored according to one of these two structures. Those familiar with data structures will recognize binary trees—why use them? Because they enable efficient binary search for fast data retrieval. In a heap, data is not sorted in any order and lacks inherent structure. Data pages are not linked sequentially, unlike in a B-tree, where data pages are connected via doubly-linked lists. A heap table relies solely on IAM pages (Index Allocation Map pages) to link its data pages together. The IAM records…