We might assume that count(*) and count(column_name) will return same result count. But NO, in case of column holds any null values.
Count (*) – returns all values (including nulls and duplicates)
Count (Column_Name) – returns all Non-NULL values(including duplicates)
In the below script we will see how it works. So that it will be easy for us to understand.
create table #tempTable(Name char(1))
insert into #tempTable values("A")
insert into #tempTable values("B")
insert into #tempTable values(Null)
insert into #tempTable values("C")
insert into #tempTable values(Null)
insert into #tempTable values("C")
select COUNT(*) from #tempTable
select COUNT(Name) from #tempTable
drop table #tempTable
Output: 6 and 4
The table #temptable has total 6 rows. Count(*) returns all the rows including null/duplicates but where as count(name) returns only 4 rows which includes duplicates("C") but not null values.
If you want to remove the duplicates from count(Name) then use distinct keyword in it.
select COUNT(distinct Name) from #tempTable –-returns 3