How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? – Interview Question of the Week #204

Question: Can an empty table have an execution plan that estimates thousands of rows?

Answer: Yes. The original experiment uses UPDATE STATISTICS ... WITH ROWCOUNT to change the estimate without inserting rows. It is an unsupported statistics-stream option, so keep this as a disposable learning experiment.

I had not heard this particular question in twenty years of working with SQL Server. Where do readers find these questions? I loved it because it makes a distinction that is easy to forget during a performance health check: the rows a plan estimates are not necessarily the rows a table contains.

-- A disposable experiment, not a production tuning recommendation.
IF OBJECT_ID('tempdb..#SqlaEmptyStats184281') IS NOT NULL
    THROW 50001, 'The experiment table already exists in this session.', 1;
CREATE TABLE #SqlaEmptyStats184281 (col1 int);

-- In SSMS, turn on the actual execution plan with Ctrl+M.
SELECT col1 FROM #SqlaEmptyStats184281 OPTION (RECOMPILE);

-- Unsupported statistics-stream option; future behavior is not guaranteed.
UPDATE STATISTICS #SqlaEmptyStats184281 WITH ROWCOUNT = 5000;
SELECT col1 FROM #SqlaEmptyStats184281 OPTION (RECOMPILE);

-- Try documented FULLSCAN; the unsupported estimate can persist on an empty table.
-- DROP ends this disposable experiment without relying on a metadata reset.
UPDATE STATISTICS #SqlaEmptyStats184281 WITH FULLSCAN;
SELECT col1 FROM #SqlaEmptyStats184281 OPTION (RECOMPILE);
DROP TABLE #SqlaEmptyStats184281;

Run the whole example in one SSMS connection. Inspect the Table Scan’s estimated rows and actual rows for each SELECT. There are no INSERT statements, so every SELECT returns an empty result. OPTION (RECOMPILE) gives each SELECT a fresh compilation after the statistics change.

These current captures show the first two estimates, with complete object names. They are not a promise about every optimizer version. In the current SQL Server 2025 test, the estimates were one, then 5,000, then still 5,000 after FULLSCAN. Actual rows remained zero for all three SELECTs. The original advice that FULLSCAN resets this override is therefore not reliable on this empty table. The final DROP removes the temporary experiment; do not depend on a metadata-reset promise. Keep the useful idea, but do not use fabricated row counts to repair a production estimate. Microsoft identifies the statistics-stream options as unsupported and does not guarantee future compatibility.

There is another limit: changing a row count does not create a realistic distribution of values, page layout or workload. It can help explore a plan hypothesis, but real representative data is needed before drawing performance conclusions. Have you used this trick in a test? I would like to hear what you learned.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.