Count (*) and count (1) Problems

Bytes ------------------------------------------------------------------------------------------------------------------

1. I have 10 fields.
Both count (1) and count (*) return the same result!

2. Who is faster.

I have asked a lot of people, and I have searched the internet for a long time. I still don't have the exact answer. They all stick to each other! Each has its own logic!

10 million data, the test is count (*) faster, but I did not know where to read the information, said count (1) Faster! How can this problem be solved !? Let us give you a clear explanation!
Bytes ------------------------------------------------------------------------------------------------------------------

 

 

 

 

 

This is because Aggregate functions such as Count and sum are added with group by statistics and null is automatically skipped. Therefore, the results of Count (1) and count (*) are likely to be different.
I have a table named a for testing.
Select * from
/*
A_nam a_add
---------------------
1 aa
1 bb
Null kk
2 kk
Null UU
4 dd
6 YY
6 YY

(8 row (s) affected)
*/

Select a_add, count (a_nam) from
Group by a_add
/*
A_add
---------------------
Aa 1
BB 1
DD 1
Kk 1
UU 0
YY 2

(6 row (s) affected)

Warning: null value is eliminated by an aggregate or other set operation.
*/

Select a_add, count (1) from
Group by a_add

/*
A_add
---------------------
Aa 1
BB 1
DD 1
Kk 2 -- there are two pens, null thinks there is
UU 1 -- null, but I think there is
YY 2

(6 row (s) affected)
*/

Let's take a look at the two below. It is very interesting. One is empty, and the other is empty.
Select sum (a_nam) from
Where a_add = 'xx'
Group by a_add
/*

-----------

(0 row (s) affected)
*/

Select sum (a_nam) from
Where a_add = 'xx'
/*
-----------
Null

(1 row (s) affected)
*/
The above two differences are caused by no use of group by. Some programs think that null is informative, so pay attention to them.
Therefore, to use aggregate functions, we generally need to add group by. If there are null data, add isnull () for conversion.

FYI

 

 

 

Original post address