How is a statistics collection different to normal table stats?

Question

I have a small table with few rows

create table dbo.p(i int);
insert dbo.p(i)
values (1), (2), (3), (4), (4);

I created statistics; no index, just the stats.

create statistics p_c on dbo.p(i) with fullscan;

I'm investigating various things so i tried to set the ROWCOUNT to the upper limit of a bigint.

update statistics dbo.p p_c with rowcount = 9223372036854775807;

This fails with error message

Msg 3739, Level 11, State 3, Line 346
Cannot UPDATE the index 'p_c' because it is not a statistics collection.

It also fails with ROWCOUNT = 1; although update statistics dbo.p p_c succeeds.

This error is not in the documentation. I can find nothing online that seems relevant.

What is a statistics collection as opposed to plain-old table statistics? Why might setting ROWCOUNT be failing here?

SQL Server 2017 (RTM-CU31-GDR) (KB5021126)

Answer 1

When you update statistics and set rowcount or pagecount this updates partition level metadata rather than the statistics themselves.

This is shown when querying sys.dm_db_partition_stats and sys.partitions rather than the Rows returned by DBCC SHOW_STATISTICS.

For statistics not tied to an index there is no corresponding partition metadata to update.

As for "collection" - I assume it may be as the command will distribute the specified row count across the metadata of all the partitions in the table for that index.

i.e. For a table with four partitions setting rowcount = 9223372036854775807 ended up with three partitions of 2305843009213693951 and one with 2305843009213693954 for me.