For query tuning and testing purposes, you can manually assign a rowcount and pagecount to a table's index statistics by running
UPDATE STATISTICS. But how do you recompute/reset the statistics to the table's actual contents?
--- Create a table.. CREATE TABLE dbo.StatTest ( i int NOT NULL, CONSTRAINT PK_StatTest PRIMARY KEY CLUSTERED (i) ); GO --- .. and give it a thousand-or-so rows: DECLARE @i int=1; INSERT INTO dbo.StatTest (i) VALUES (@i); WHILE (@i<1000) BEGIN; INSERT INTO dbo.StatTest (i) SELECT @i+i FROM dbo.StatTest; SET @i=@i*2; END;
A dummy query:
SELECT i%100, COUNT(*) FROM dbo.StatTest GROUP BY i%100;
... will return the following query plan (the row estimate in the Index Scan is 1024 rows).
UPDATE STATISTICS command..
UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;
... and the plan looks like this, now with an estimate of 10 million rows:
How do I reset the rowcount to the actual contents of the table without using
WITH RESAMPLE and
WITH SAMPLE n ROWS, but the statistics rowcount remains 10 million rows. Inserting a row or even deleting all of the rows doesn't update the statistics, because the change is too small.