Microsoft
Please try the below and you will see that your reply to me is incorrect. SQL Server has never fully populated the intermediate pages in a B-Tree index even with an identity. You have to rebuild the index for this to happen.
/*
** Create a test DB and then a test table (I'll leave craeting the DB to you
*/
create table dbo.test (
i int identity(1, 1) primary key clustered
, filler char(500) default 'a'
);
/*
** Populate the table
*/
insert into dbo.test default values;
go 1000
insert into dbo.test (filler)
select filler
from dbo.test;
go 10
/*
** Grab the root page
*/
select concat(N'dbcc page(', db_name(), N', ', rp.[file_id], N', ', rp.page_id, N', 3) with tableresults;')
from sys.partitions as p
inner join sys.system_internals_allocation_units as au
on p.[partition_id] = au.container_id
cross apply sys.fn_PhysLocCracker(au.root_page) as rp
where p.index_id = 1
and p.[object_id] = object_id(N'dbo.test', 'U');
/*
** From here go to any page in the middle of the level below
*/
Also Awesome, the comment preview doesn't work for me so I don't know if this will finally accept the formatting this time around. I hope it does, if not then it would be great if this supported the latest Safari browser.
