or
daniel hutmacher imported from SE
sql-server
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).

[![10 000 rows][1]][1]

Run the `UPDATE STATISTICS` command..

    UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;

... and the plan looks like this, now with an estimate of 10 million rows:

[![10 million rows][2]][2]

How do I reset the rowcount to the actual contents of the table without using `WITH ROWCOUNT`?

I've tried `WITH FULLSCAN`, `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.


  [1]: http://i.stack.imgur.com/e8Mtl.png
  [2]: http://i.stack.imgur.com/Dh3HI.png
Top Answer
Paul White
Use `DBCC UPDATEUSAGE` with the `COUNT_ROWS` option.

    DBCC UPDATEUSAGE 
    (   { database_name | database_id | 0 } 
        [ , { table_name | table_id | view_name | view_id } 
        [ , { index_name | index_id } ] ] 
    ) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ] 

[Documentation][1]


  [1]: https://msdn.microsoft.com/en-us/library/ms188414.aspx
How to reset statistics after UPDATE STATISTICS … WITH ROWCOUNT

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.