1

After reading a few articles and some answers on Stackoverflow my baby mind has started overflowing with information. Yet I am so confused where exactly the data gets stored in SQL Server.

What I assume I know of right now:

  • The page is the basic unit where the data gets stored in SQL Server
  • SQL Server uses a binary tree to store the data
  • There are two types of nodes: a leaf page and a non-leaf page
  • The leaf pages are the one which is at the end of a B-Tree
  • The index is stored in a leaf page

My questions:

  1. Is the data stored in leaf page?
  2. What gets stored in a non-leaf page?
  3. If index are created on the leaf page then how are they the first thing SQL Server engine checks for without going through the entire B-tree non-leaf nodes? Isn't this time-consuming.

What I have read so far:

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ankit Suhail
  • 2,045
  • 21
  • 32
  • 1
    Indexes are not stored in leaf pages. Indexes *use* leaf and non-leaf pages to implement B-trees. Leaf pages contain the actual rows, non-leaf pages contain the intermediate levels needed to find those leaf pages (rows). – Jeroen Mostert May 26 '17 at 14:38

1 Answers1

0

SQL Server uses a balanced tree to store indexes, not data.

There are two types of tables: Heaps and Clustered. Heaps doesn't have a clustered index, in this case the data is stored in pages with no order.

When a table has a clustered index, the index get the data for itself, the data pages becomes the leaf level of the index.

Non-leaf pages stores an indexing using the index keys, directing the search for the key across the tree.

An oversimplified way to understand is to think about an string key and the alphabet: the non-leaf level could register each letter of the alphabet and direct the search for that letter to the correct pages across the tree.

Dennes Torres
  • 396
  • 2
  • 10