SQL Server: Two Equality Searches, Two B-Trees
The compound key steers every level — in either order
SELECT
*
FROM
dbo.Users
WHERE
DisplayName = 'alex'
AND
Location = 'Seattle, WA';
IX_DisplayName_Location
page reads:
—
1
2
3
Root
Root page 1:100
aaron, Houston → 1:200
brent, Chicago → 1:201
maria, Austin → 1:202
aaron, Houston → 1:200
Intermediate
Intermediate page 1:200
aaron, Houston → 1:301
alex, Portland → 1:302
amy, Boston → 1:303
⋯
alex, Portland → 1:302
Page 1:201
brent, Chicago → …
…
Page 1:202
maria, Austin → …
…
Leaf · keys
Page 1:301
aaron · Houston, TX · 8,114
aaron · Seattle, WA · 19,406
⋯
Page 1:302
alex · Portland, OR · Id 77,351
alex · Seattle, WA · Id 41,209
alex · Seattle, WA · Id 88,554
alex · Tampa, FL · Id 2,290
alexa · Boise, ID · Id 9,201
Page 1:303
amy · Boston, MA · 44,120
amy · Denver, CO · 61,003
⋯
IX_Location_DisplayName
page reads:
—
1
2
3
Root
Root page 1:150
Austin, chris → 1:250
Portland, alex → 1:251
Spokane, dana → 1:252
Portland, alex → 1:251
Intermediate
Intermediate page 1:251
Portland, alex → 1:401
Seattle, aaron → 1:402
Seattle, chris → 1:403
⋯
Seattle, aaron → 1:402
Page 1:250
Austin, chris → …
…
Page 1:252
Spokane, dana → …
…
Leaf · keys
Page 1:401
Portland, OR · alex · 77,351
Renton, WA · sam · 52,077
⋯
Page 1:402
Seattle, WA · aaron · Id 19,406
Seattle, WA · alex · Id 41,209
Seattle, WA · alex · Id 88,554
Seattle, WA · brent · Id 60,113
Seattle, WA · casey · Id 30,881
Page 1:403
Seattle, WA · chris · 90,125
Seattle, WA · dana · 7,036
⋯
either key order: 3 reads → the same 2 rows