Structure of a Multi-column index in SQL server

Question

Where can I find a good representation of the of how data is stored in pages and how the B tree is constructed for a multi-column index (specifically for SQL server, but not necessarily)?

I'm referring to something like what you see in https://learn.microsoft.com/en-us/sql/relational-databases/reading-pages?view=sql-server-ver15 (for single column) but extended for multi-columns.

Another example for single column index:

• https://www.sqlshack.com/clustered-index-vs-heap/

Thanks.

Answer 1

I found this example very informative, hope you are looking for something like this. It shows include columns as well (SQL-Server specific). If you don't need include-columns, simply take everything without 'age' and 'sex' in the leaf node. There is a good explanation in the original article (which I hope you might not need).

Reference - https://www.malinga.me/index-physical-structure-example-multi-column-non-clustered-index-with-includes/

Answer 2

The index key values are sorted first by the first key column, then by the second key column, and then yt's exactly the same, except with additional columns on the non-leaf nodes. So if the first key column is a number, and the second the name of an animal, the non-leaf pages might have ranges like:

                                     (1,'cat')-(1000,'horse')
                                              ^
                  (1,'cat')-(500,'snake')               (500,'tiger')-(1000,'horse') 
                           ^                                         ^
(1,'cat')-(250,'elephant')   (250,'fox')-(500,'snake')          . . .