V78xgg4Q

1--create table and fill it
2DROP TABLE IF EXISTS bunchesofints
3CREATE TABLE bunchesofints (
4thisisanint INT PRIMARY KEY CLUSTERED,
5junkrow CHAR(1000) NOT NULL
6)
7
8INSERT dbo.bunchesofints
9SELECT TOP 5000
10ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS thisisanint,
11REPLICATE('a',1000) AS junkrow
12FROM sys.all_objects a1
13CROSS JOIN sys.all_objects a2
14
15
16--with this query we can see all the non-leaf pages of the b-tree, plus the IAM
17SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
18FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
19WHERE page_type != 1
20GO
21
22--Ok, let's delete most of the rows
23;WITH CTE AS (
24    SELECT TOP (4500) *
25    FROM dbo.bunchesofints
26    ORDER BY thisisanint DESC
27)
28
29DELETE 
30FROM CTE
31GO
32
33--Hmm, still have 3 non-leaf index pages
34SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
35FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
36WHERE page_type != 1
37
38
39
40--So, where are the rows?
41--please note the assumption that your test database has a single file.
42DECLARE @firstindexpage INT, @lastindexpage INT, @db INT = DB_ID()
43SELECT @firstindexpage = MIN(previous_page_page_id), @lastindexpage = MAX(next_page_page_id)
44FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
45WHERE page_type = 2 AND page_level = 1
46
47DBCC PAGE(@db,1,@firstindexpage,3) WITH TABLERESULTS
48DBCC PAGE(@db,1,@lastindexpage,3) WITH TABLERESULTS