Table scan estimates when there are no stats on the table

Question

Suppose I create a table with 1000 rows but don't gather statistics on the table or run a query that should trigger an automatic statistics creation:

create table test( a int )
go
insert into test select 1
go 1000
select * from test
sp_helpstats 'test','all'

When I check the plan for the select, I can see that the estimate is accurate and it's 1000. Where does that estimate comes from? Does it have anything to do with sys.partitions rows column?

Answer 1

Try a different question: should I need to create statistics just to see how much space a table uses?

EXEC sp_spaceused 'test';

That query shouldn't just fail, right? Yet it provides the correct row count of 1000 rows. You can look at the code of sp_spaceused quite easily:

SELECT
            @reservedpages = SUM (reserved_page_count),
            @usedpages = SUM (used_page_count),
            @pages = SUM (
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE 0
                END
                ),
            @rowCount = SUM (
                CASE
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
                END
                )
        FROM sys.dm_db_partition_stats
        WHERE object_id = @id;

This doesn't mean that the estimate "comes from the sys.dm_db_partition_stats DMV". DMVs are created for end users to write application code against. The approximate row count of the table is stored in some internal structure which can be used to provide a cardinality estimate when there's no other option available for the query optimizer. If you need to know the details about this internal structure you could try using a debugger, but it's not like there's any guarantee that it wouldn't change between SQL Server versions.

It's worth mentioning that you should almost never have tables without statistics in production.