Understanding a Clustered Index

Question

Since PostgreSQL does not support clustered indexes, I am considering a MSSQL server. I read an article comparing clustered and nonclustered indexes. The essence of the article is that (emphasize mine):

Uncompiled indexes store both the value and the pointer to the actual row that contains this value.

AND

Clustered indexes do not need to store a pointer to the actual row due to the fact that the rows in the table are stored on disk in the same exact order as the clustered index

As I was told there and there it was very difficult to maintain the physical ordering of tabular data, especially if the table is divided between several drives. And now I come across the concept of a clustered index, assuming that the data is stored in some order physically. This is what confused me.

Question: What is a cluster index structure? Does it support a tree like structure to go through, for example, PosgtreSQL for btree indexes?

Answer 1

In SQL Server, indexes are organized as B-trees. Each page in a B-tree index is called a node index. The top node of the B-tree is called the root of the node. The bottom level of nodes in an index is called leaf nodes. Any index levels between the root and leaf nodes are collectively known as intermediate levels. In a clustered index, leaf nodes contain data pages of a base table. Root and middle tier nodes contain index pages containing index rows. Each row of the index contains a key value and a pointer to an intermediate level page in the B-tree or a data row at the index sheet level. Pages at each index level are linked in a doubly linked list.

Cluster indexes have one row in sys.partitions, and index_id = 1 for each section used by the index. By default, a clustered index has one partition. When a clustered index has several partitions, each partition has a B-tree structure that contains data for that particular partition. For example, if a clustered index has four sections, there are four B-tree structures; one in each section.

for reference

https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/14/sql-server- part-4-explaining-the-non-clustered-index-structure- /

Answer 2

There are three levels to a clustered index

level 1.Root

2. Beginner level

Level 3.Leaf

The clustered index contains row-level data rows. if you are looking for a value in an indexed column, the query engine will first look at the value at the root level, if the value is available at the root level, then the query engine will not go to the intermediate level or sheet level. if the value is not based on the root level, it will look for the value at the intermediate level or leaf level. if the number of data rows is too small, then there is no intermediate level in the clustered index.

The chart below will help you understand the basic clustered index: