Difference between count (*), Count (1) and Count (Column) in sql server

COUNT (*) and COUNT (1)

It returns the number of items in a group, including NULL values and duplicates. COUNT (ALL expression) evaluates expression for each row in a group and returns the number       

See the example

I have a table

COUNT (Column Name) – Returns the total number of Non-NULL records. It means that, it ignores counting NULL valued records in that particular column.

See the example

I am creating a table

create table Emp (ID int Identity(1,1), Name Varchar(100), Mob varchar(10) )

Now I am inserting some records.

insert into Emp values ('Bagesh kumar','888880XXXX') insert into Emp values ('Rajesh kumar','888880XXXX') insert into Emp values ('Manish kumar',null) insert into Emp values ('Dinesh kumar','888880XXXX') insert into Emp values ('Dileep kumar',null) insert into Emp values ('Mukesh kumar','888880XXXX') insert into Emp values ('Ramesh kumar',null) insert into Emp values ('Mahesh kumar','888880XXXX')

Record inserted successfully.

COUNT (*) and COUNT (1)

Now I am using Count (*) and Count (1) and see the value.

I return number of record in the table.

Count (column)

As we know that it will returns the total number of Non-NULL records. It means that, it ignores counting NULL valued records in that particular column. See the example. In mob column table has null values.

See Mob return 5 because in 3 record having Null value.