Moonglade

Modern Markdown blogging with AI-powered localization and search.

Tag: Clustered Index


[Translated] How Data Is Stored in SQL Databases

This article provides an in-depth analysis of the physical data storage mechanisms in SQL Server. It highlights that while data logically exists in rows and columns, it is physically stored using 8KB data pages as the basic unit. When a table has a primary key, SQL Server creates a clustered index by default, ensuring that data is physically ordered by the primary key. This storage structure takes the form of a B-tree (also known as the clustered index structure), composed of root nodes, intermediate levels, and leaf nodes. The leaf nodes directly store the actual data rows, whereas the root nodes and intermediate levels contain index rows that include key values and pointers to lower-level nodes. Through this hierarchical pointer structure, the database engine can efficiently locate and retrieve data. Understanding this underlying structure is crucial for developers, particularly when performing SQL Server performance tuning, troubleshooting, and optimizing slow queries. Mastering the operational principles of data pages, B-trees, and index rows forms the foundation for enhancing database performance.

Views 132
B Tree Clustered Index DB Data Page Data Storage Root Node

[Translation] How SQL Indexes Work

This article provides an in-depth analysis of how indexes work in SQL Server, with a particular focus on distinguishing between clustered and non-clustered indexes. First, the article explains that clustered indexes are based on a B-tree structure, where data is physically sorted and stored by primary key. By traversing pointers from the root node through intermediate levels to the leaf nodes, the system can quickly locate data, enabling efficient index seeks (Seek) that retrieve results by reading only a minimal number of rows. Next, it contrasts this with a full table scan (Scan) performed in the absence of an index, noting that such scans must traverse millions of data rows, resulting in poor performance. Subsequently, the article introduces the structure of non-clustered indexes, which store only key values and row locators pointing to the clustered index. When querying non-indexed columns, SQL Server first uses the non-clustered index to quickly find the corresponding clustered key, then leverages the clustered index to retrieve the complete data row. This process involves an index seek, a key lookup, and a nested loop join. The article illustrates these mechanisms using execution plans, demonstrating how indexes significantly enhance query efficiency by reducing I/O reads. This helps developers understand the underlying logic of index-accelerated queries and their central role in database optimization.

Views 174
Clustered Index DB Database Index Execution Plan Non Clustered Index Query Optimization