How NonClustered Index works in SQL Server

Question

I have a question, related to DB theory:

Let's assume that we have table with 3 columns: [PersonID], [PersonName], [PersonAge].

We know that when we have a nonclustered index by one column, SQL Server orders table data in accordance with specified column and build B+ tree from it. And when we need to find the row using such an index, SQL Server scans the B++ tree by comparing one atomic data object (int or string, for example). That is clear, how non-clustered index works and find data when we build it by one column (suppose [PersonName]), but what if we create non-clustered index by 2 columns: [PersonName] and [PersonAge]?

I understand, that during sorting criterion with main importance will be [PersonName] and if several records have it same, than they would be sorted by [PersonAge]. But how physically SQL Server would process the B++ tree, based on this index?

How it will use such tree when it should execute query like

SELECT * 
FROM dbo.Person 
WHERE [PersonName] = 'Bob' AND [PersonAge] = 45

Thanks for explaining.

Answer 1

The nonclustered index will be pretty much the same as with just one column - but each index entry in the B++ navigation tree will have two column values (PersonName, PersonAge).

Since both columns are comparable, a clearly defined ordering will be applied - first by PersonName, then by PersonAge - so your navigation tree items would look something like this (if looked at as a flat list):

Alice,42
Alice,57
Andrew,31
Anthony,23
...
...
Bertrand,48
Bob,34
Bob,39
Bob,44 
Bob,45
Bob,58
......
Zachary,19

When you run your query

SELECT * 
FROM dbo.Person 
WHERE [PersonName] = 'Bob' AND [PersonAge] = 45

then SQL Server will navigate the B++ navigation tree - first looking at the PersonName, until it finds all occurrences of Bob, and then it scans all Bob's to find the one you're looking for (or maybe it doesn't find it).