Database Reference
In-Depth Information
create /*unique*/ clustered index IDX_NonUniqueCIDups_KeyValue
on dbo.NonUniqueCIDups(KeyValue);
-- Populating 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
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.UniqueCI(KeyValue, ID)
select ID, ID
from IDs;
insert into dbo.NonUniqueCINoDups(KeyValue, ID)
select KeyValue, ID
from dbo.UniqueCI;
insert into dbo.NonUniqueCIDups(KeyValue, ID)
select KeyValue % 10, ID
from dbo.UniqueCI;
Now let's look at the clustered indexes physical statistics for each table. The code for this is shown in Listing 6-2,
and the results are shown in Figure
6-1
.
Listing 6-2.
Nonunique clustered index: Checking clustered indexes row size
select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from sys.dm_db_index_physical_stats
(db_id(), object_id(N'dbo.UniqueCI'), 1, null , 'DETAILED')
select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from sys.dm_db_index_physical_stats
(db_id(), object_id(N'dbo.NonUniqueCINoDups'), 1, null , 'DETAILED')
select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from sys.dm_db_index_physical_stats
(db_id(), object_id(N'dbo.NonUniqueCIDups'), 1, null , 'DETAILED')
