February 2, 2015 at 11:44 pm
interesting question. thanks for sharing
February 5, 2015 at 6:03 am
Thomas Abraham (2/2/2015)
Nice question to highlight the differences in ways that NULLs are handled by different operations. Thanks!
+1
April 14, 2016 at 6:53 am
psingla (2/2/2015)
Good Question...That's why I prefer using count(1)
I use COUNT(0). Any difference?
April 14, 2016 at 12:23 pm
Iwas Bornready (4/14/2016)
psingla (2/2/2015)
Good Question...That's why I prefer using count(1)
I use COUNT(0). Any difference?
No.
The COUNT function has officially two forms. The first is COUNT(*) which self-documents that it counts rows rather any specific constant of the row,
The other form is officially COUNT(expression), which logically states that the expression has to be evaluated for every row and the non-NULL results have to be counted. COUNT(0) and COUNT(1) are examples of this where the expression is very simple. In SQL Server, the optimizer realizes that the expressions 0 and/or 1 can never become NULL so it short-circuits the expression evaluation and simply uses the same logic as for COUNT(*).
Another common form of COUNT(expression) is to use a simple column name as the expression. For a nullable column, this means that the RDBMS has to actually count the number of rows where this column is not NULL. However, if the column is nullable then SQL Server again realizes that it can simply pretend you used COUNT(*).
COUNT with an expression that is more complex than any of these examples is permitted, but rare (and also very rarely useful). The more complex the expression is, the lower the chance that SQL Server can adequately determine if a NULL result is even possible. If it cannot determine this, then no short-circuiting is possible and the expression will always be evaluated for every row.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply