3

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)

0

1 Answer 1

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.