Is COUNT(*) in SQL Server a constant time operation? If not, why not?

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.

why do we need to "load" entire rows

We don't. SQL Server will tend to use the smallest index that it can use that can satisfy the query.

Count(*) (without any filtering) being such a common operation

I think you over-estimate its prevalence. I can't remember the last time that I cared about the total number of rows in a single table versus a more filtered view or a count across a more complex joined operation.

It would be an exceptionally narrow optimization that could only benefit a single style of query, and as I say, I think you've overestimated how often it occurs.