13

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 Dec 16 '13 at 18:56
13

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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