DBCC SHOWCONTIG returns 2 rows against certain tables

Question

When I am running DBCC SHOWCONTIG ('schema_name.table_name') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS in SQL Server 2016 it is returning 2 rows against certain tables

Example below:

ObjectName  ObjectId    IndexName   IndexId Level   Pages   Rows    MinimumRecordSize   MaximumRecordSize   AverageRecordSize   ForwardedRecords    Extents ExtentSwitches  AverageFreeBytes    AveragePageDensity  ScanDensity BestCount   ActualCount LogicalFragmentation    ExtentFragmentation

table1_ 1891549497  col_st_idx  1   0   0   0   0   0   0   0   0   0   0   0   100 0   0   0   0
table_  1891549497  col_st_idx  1   0   1   10  553 641 593.6   0   1   0   2140    73.5606622189276    100 1   1   0   0 

I am unable to understand the reason for this behavior as ideally it should return only 1 row,can someone provide anything on that?

Answer 1

drop table if exists testtblZ
go

--clustered
create table testtblZ(col char(900) default('aaaa') not null);
create clustered index idxtesttblZ on testtblZ(col);
go
insert into testtblZ(col)
values(newid());
go 10
DBCC SHOWCONTIG ('testtblZ') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS;
go


--columnstore
create clustered columnstore index idxtesttblz on testtblz with (drop_existing = on);
go
insert into testtblZ(col)
values(newid());
go 10
DBCC SHOWCONTIG ('testtblZ') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS;
go