No, COUNT(*) is not a constant time operation. A COUNT(*) must return a count of rows that conform to the current scan predicate (ie. WHERE clause), so that alone would make the return of a metadata property invalid. But even if you have no predicates, the COUNT still has to satisfy the current transaction isolation semantics, ie. return the count of rows visible (eg. committed). So COUNT must, and will, in SQL Server, actually scan and count the rows. Some systems allow return of faster ‘estimate’ counts.
Also, as a side comment, relying on rows in sys.partitions is unreliable. After all, if this count would be guaranteed accurate then we would not need DBCC UPDATEUSAGE(...) WITH COUNT_ROWS. There are several scenarios that historically would cause this counter to drift apart from reality (mostly minimally logged insert rollbacks), all I know of are fixed, but that still leaves the problems of 1) upgraded tables from earlier versions that had the bugs and 2) other, not yet discovered, bugs.
In addition, why do we need to “load” entire rows (as indicated in the post I linked) just to count them? Shouldn’t indexes or PKs etc. be sufficient to count them?
This is not 100% true. There are at least 2 scenarios that do no ‘load entire rows’:
- narrow rowstore indexes load just the ‘index’ row, which may be much smaller
- columnstore data loads just the relevant column segments
And most of what I say above do not apply for Hekaton tables.