Index Fragmentation - Pro SQL Server Internals - page 119

Another option is that you can reserve the space in the row during the insert stage by populating the address with
a default value, preallocating the space. Let's find out how much space is used by the address information with the
code shown in Listing 5-5. Figure 5-5 shows the result.
Listing 5-5. Patterns that lead to fragmentation: Calculating average address size
select avg(datalength(Address)) as [Avg Address Size] from dbo.Positions
Figure 5-5. Fragmentation after post-processing
Average address size is 32 bytes, which is 16 Unicode characters. You can populate it with a string of 16 space
characters during the insert stage, which would reserve the required space in the row. The code in Listing 5-6
demonstrates this approach.
Listing 5-6. Patterns that lead to fragmentation: Populating address with 16 space characters during insert stage
drop table dbo.Positions
go
create table dbo.Positions
(
DeviceId int not null,
ATime datetime2(0) not null,
Latitude decimal(9,6) not null,
Longitude decimal(9,6) not null,
Address nvarchar(200) null,
Placeholder char(100) null,
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.Positions(DeviceId, ATime, Latitude, Longitude, Address)
select
ID % 100 /*DeviceId*/
,dateadd(minute, -(ID % 657), getutcdate()) /*ATime*/
,0 /*Latitude - just dummy value*/
,0 /*Longitude - just dummy value*/
,replicate(N' ',16) /*Address - adding string of 16 space characters*/
from IDs;
create unique clustered index IDX_Postitions_DeviceId_ATime
on dbo.Positions(DeviceId, ATime);