Database Reference
In-Depth Information
Figure 8-2.
Clustered index physical statistics after DELETE statement without AFTER DELETE trigger
As you remember, the DELETE operation does not physically remove the row from the page and just marks it as a
ghost row. In our example, the only thing that was changed is amount of free space on the pages.
Now let's truncate the table and populate it with the same data as before with code shown in Listing 8-9.
Listing 8-9.
Triggers and fragmentation: Populating table with the data
truncate table dbo.Data;
;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
,Numbers(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Data(Value)
select Num
from Numbers;
Next, let's create the empty AFTER DELETE trigger on the table, as shown in Listing 8-10.
Listing 8-10.
Triggers and fragmentation: Trigger creation
create trigger trg_Data_AfterDelete
on dbo.data
after delete
as
return;
If you run the same deletion statement as you did previously, you would see the results shown in Figure
8-3
.
