Index Fragmentation - Pro SQL Server Internals - page 120

update dbo.Positions set Address = N'Position address';
select index_level, page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'Positions'),1,null,'DETAILED')
Even though you update the address information during post-processing, it did not increase the size of the data
rows. As a result, there is no fragmentation in the table, as shown in Figure 5-6 .
Figure 5-6. Fragmentation when address has been pre-populated with 16 space characters during the insert stage
Unfortunately, in some cases you cannot pre-populate some of the columns in the insert stage because of the
business or functional requirements of the system. As a workaround, you can create a variable-length column in the
table and use it as the placeholder to reserve the space. Listing 5-7 shows such an approach.
Listing 5-7. Patterns that lead to fragmentation: Using a placeholder column to reserve the space
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,
Dummy varbinary(32)
);
;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, Dummy)
select
ID % 100 /*DeviceId*/
,dateadd(minute, -(ID % 657), getutcdate()) /*ATime*/
,0 /*Latitude - just dummy value*/
,0 /*Longitude - just dummy value*/
,convert(varbinary(32),replicate('0',32)) /*Dummy column to reserve the space*/
from IDs;