Database Reference
In-Depth Information
Listing 4-12.
Filtered indexes: Inserting data and updating statistics
;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.Data(RecId, Processed)
select ID, 0
from Ids;
update statistics dbo.Data;
At this point, the
dbo.Data
table has 65,536 rows. Let's update all of the data in the table and set
Processed = 1
.
After that, we will look at the statistics columns modification count. The code to do this is shown in Listing 4-13.
Listing 4-13.
Filtered indexes: Updating data
update dbo.Data set Processed = 1;
select
s.stats_id as [Stat ID]
,sc.name + '.' + t.name as [Table]
,s.name as [Statistics]
,p.last_updated
,p.rows
,p.rows_sampled
,p.modification_counter as [Mod Count]
from
sys.stats s join sys.tables t on
s.object_id = t.object_id
join sys.schemas sc on
t.schema_id = sc.schema_id
outer apply
sys.dm_db_stats_properties(t.object_id,s.stats_id) p
where
sc.name = 'dbo' and t.name = 'Data'
As you can see in Figure
4-7
, the modification count for the filtered index column shows zero. Moreover, the
number of rows in the index is still 65,536, even though all rows in the table are now processed.
Figure 4-7.
Filtered indexes: Statistics information
If you look at the histogram shown in Figure
4-8
, you will see that it contains the old data distribution information.
