SQL Server: One Inequality, Two B-Trees
Add a <> and key order suddenly matters
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
5
7
Root
Root page 1:150
Austin, chris → 1:250
Portland, alex → 1:251
Spokane, dana → 1:252
← range 1: < 'Seattle, WA'
← range 2: > 'Seattle, WA'
Intermediate
Intermediate 1:250
Austin, brent → 1:400
Boise, alexa → 1:401
⋯
Intermediate 1:251
Portland, alex → 1:402
Seattle, aaron → 1:403
⋯
Intermediate 1:252
Spokane, dana → 1:404
Walla Walla, pat → 1:405
⋯
Leaf · keys
Page 1:400
Austin, TX · brent · 12,040
Austin, TX · chris · 55,491
Austin, TX · dana · 60,332
⋯
Page 1:401
Boise, ID · alexa · 9,201
Chicago, IL · brent · 33,177
Houston, TX · aaron · 8,114
⋯
Page 1:402
Portland, OR · alex · 77,351
Portland, OR · sam · 24,615
Renton, WA · sam · 52,077
⋯
Page 1:403
Seattle, WA · aaron · 19,406
Seattle, WA · alex · 41,209
Seattle, WA · alex · 88,554
⋯
skipped
the only page <> rules out
Page 1:404
Spokane, WA · dana · 7,036
Tampa, FL · alex · 2,290
Tulsa, OK · jamie · 91,882
⋯
equality leads: 3 reads · inequality leads: almost every page