Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I was reading this discussion in another post where this question was raised by someone else. Before reading the discussion, I always thought SQL Server (and other DBMS) keep a global count of rows for each table somewhere in the metadata, but the discussion seems to say it isn't so. Why? Count(*) (without any filtering) being such a common operation would get huge boost if it is O(1). Even not considering COUNT(*), the total number of rows in a table is such a fundamental piece of information. Why don't they keep a note of it?

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?

like image 995
dotNET Avatar asked May 24 '17 12:05

dotNET


People also ask

Are count (*) and count () the same function Why Why not?

The simple answer is no – there is no difference at all. The COUNT(*) function counts the total rows in the table, including the NULL values. The semantics for COUNT(1) differ slightly; we'll discuss them later.

What does count (*) mean in SQL?

COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.

How does count * Work SQL?

The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.

Will count (*) ever return NULL?

COUNT(expression) does not count NULL values.


2 Answers

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.

like image 132
Remus Rusanu Avatar answered Oct 11 '22 18:10

Remus Rusanu


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.

like image 37
Damien_The_Unbeliever Avatar answered Oct 11 '22 18:10

Damien_The_Unbeliever