The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at it) this column gives the number of rows in the table.

I'm curious how accurate this column is and if I can use it instead of a SELECT COUNT(1) FROM TableName. I've done some experiments where create a table and add a few thousand rows, delete a few hundred, add a few thousand more etc and the count has always been dead on. However I have one table with about 700 mil rows and several indexes. The row in sys.partitions for the clustered index is again dead on, however the other indexes show some slight variations (+-20k).

Does anyone know how this row is calculated and if it is as accurate as it appears?

  • 4
    I've been using a query based on the rows column for ages now. Haven't observed it being out of date
    – billinkc
    Commented Dec 16, 2013 at 18:56

2 Answers 2


Books Online states that the rows field "indicates the approximate number of rows in this partition." I would therefore expect it to be close, but not 100% accurate, 100% of the time.

Michael Zilberstein reports an example of sys.partitions being wildly incorrect in For want of a nail. Not saying it is a common occurrence, but it is possible.

sys.dm_db_index_physical_stats contains a record_count field that appears to be more accurate, although be aware running the DMV may result in a REDO blocking issue if you run it on an instance hosting an AlwaysOn Readable Secondary Replica.

The explanation for the record_count field shows the following info:

Total number of records.

For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of records in the IN_ROW_DATA allocation unit.

For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

See also Martin Smith's answer to a similar question on Stack Overflow.


BOL indeed says for this column "indicates the approximate number of rows in this partition." And the approximate is very approximate. I already saw that in a certain partition I had 1 record. sy.partitions.rows was indicating 4048 rows. After deleting this record it was still indicating 4048 rows, even after rebuilds of all indexes etc. So one conclusion, it is if you want to rely on it, don't.

  • Please, edit your question providing the source link of that quote. It shall improve your answer ;)
    – Ronaldo
    Commented Mar 12, 2020 at 15:38

Your Answer

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

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